Re: [GENERAL] Query with error - DOW FROM timestamp
Yes, thank you.
-- Original Message --
From: Albe Laurenz laurenz.a...@wien.gv.at
To: Victor Sterpu vic...@caido.ro; PostgreSQL General
pgsql-general@postgresql.org
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] postgreSQL : duplicate DB names
Hi Something wrong happened in our server, for some strange and unknown reason (s) the server created another DB, there is two same DB names in one server. How I can I fix this. Please help Name| Owner | Encoding +--+-- a9004| postgres | UNICODE a9004| postgres | UNICODE CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
Re: [GENERAL] Shared memory changes in 9.4?
Hi, On 2014-05-25 12:26:20 -0700, Maciek Sakrejda wrote: 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 chance you're using a 9.3 configuration file instead of the one generated by initdb? dynamic_shared_memory_type defaults to 'posix' if not specified in the config file (on platforms supporting it). If initdb detects that 'posix' can't be used it'll emit a different value. If you're copying the config from 9.3 and your environment doesn't support posix shm that'll cause the above error. I still think dynamic_shared_memory_type should default to 'none' because of such problems. 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. It sounds like you're not allowing posix shared memory. Possibly just by not mounting /dev/shm. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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 : duplicate DB names
Khangelani Gama, 26.05.2014 09:20: Hi Something wrong happened in our server, for some strange and unknown reason (s) the server created another DB, there is two same DB names in one server. Name| Owner | Encoding -+--+-- a9004| postgres | UNICODE a9004| postgres | UNICODE Maybe there are some trailing spaces at one of the names. What do you see when you run this: select ''||datname||'' as name from pg_database By adding the quotes, you can see if there are any trailing (or leading) in the name. Regards Thomas -- 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 : duplicate DB names
Hi, I get: name -- a9004 template1 template0 a9004 (4 rows) -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Thomas Kellerer Sent: Monday, May 26, 2014 9:42 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] postgreSQL : duplicate DB names Khangelani Gama, 26.05.2014 09:20: Hi Something wrong happened in our server, for some strange and unknown reason (s) the server created another DB, there is two same DB names in one server. Name| Owner | Encoding -+--+-- a9004| postgres | UNICODE a9004| postgres | UNICODE Maybe there are some trailing spaces at one of the names. What do you see when you run this: select ''||datname||'' as name from pg_database By adding the quotes, you can see if there are any trailing (or leading) in the name. Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. -- 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 : duplicate DB names
On 26 May 2014, at 10:09, Khangelani Gama kg...@argility.com wrote: Khangelani Gama, 26.05.2014 09:20: Hi Something wrong happened in our server, for some strange and unknown reason (s) the server created another DB, there is two same DB names in one server. Name| Owner | Encoding -+--+-- a9004| postgres | UNICODE a9004| postgres | UNICODE Maybe there are some trailing spaces at one of the names. What do you see when you run this: select ''||datname||'' as name from pg_database By adding the quotes, you can see if there are any trailing (or leading) in the name. Regards Thomas Hi, I get: name -- a9004 template1 template0 a9004 (4 rows) Looks like you might have some index corruption, do those databases have the same oid?: select datname, oid from pg_database What version of the database are you running? What OS? Did anything of notice happen on that server before this showed up? P.S. Please don’t top-post on this list. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- 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 : duplicate DB names
-Original Message- From: Alban Hertroys [mailto:haram...@gmail.com] Sent: Monday, May 26, 2014 10:21 AM To: Khangelani Gama Cc: Thomas Kellerer; pgsql-general@postgresql.org Subject: Re: [GENERAL] postgreSQL : duplicate DB names On 26 May 2014, at 10:09, Khangelani Gama kg...@argility.com wrote: Khangelani Gama, 26.05.2014 09:20: Hi Something wrong happened in our server, for some strange and unknown reason (s) the server created another DB, there is two same DB names in one server. Name| Owner | Encoding -+--+-- a9004| postgres | UNICODE a9004| postgres | UNICODE Maybe there are some trailing spaces at one of the names. What do you see when you run this: select ''||datname||'' as name from pg_database By adding the quotes, you can see if there are any trailing (or leading) in the name. Regards Thomas Hi, I get: name -- a9004 template1 template0 a9004 (4 rows) Looks like you might have some index corruption, do those databases have the same oid?: select datname, oid from pg_database What version of the database are you running? What OS? Did anything of notice happen on that server before this showed up? P.S. Please don't top-post on this list. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. It's psql 8.0.4, OS is Red Hat Linux release 9 (Shrike datname |oid + a9004| 4215680815 template1 | 1 template0 | 17229 a9004| 1335509510 (4 rows) CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. -- 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 : duplicate DB names
On 26 May 2014, at 10:28, Khangelani Gama kg...@argility.com wrote: On 26 May 2014, at 10:09, Khangelani Gama kg...@argility.com wrote: Khangelani Gama, 26.05.2014 09:20: Hi Something wrong happened in our server, for some strange and unknown reason (s) the server created another DB, there is two same DB names in one server. Name| Owner | Encoding -+--+-- a9004| postgres | UNICODE a9004| postgres | UNICODE Maybe there are some trailing spaces at one of the names. What do you see when you run this: select ''||datname||'' as name from pg_database By adding the quotes, you can see if there are any trailing (or leading) in the name. Regards Thomas Hi, I get: name -- a9004 template1 template0 a9004 (4 rows) Looks like you might have some index corruption, do those databases have the same oid?: select datname, oid from pg_database What version of the database are you running? What OS? Did anything of notice happen on that server before this showed up? P.S. Please don't top-post on this list. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. It's psql 8.0.4, OS is Red Hat Linux release 9 (Shrike datname |oid + a9004| 4215680815 template1 | 1 template0 | 17229 a9004| 1335509510 (4 rows) 8.0.4? That went EOL almost 4 years ago and even then you should be running a version around 8.0.26. You’re 22 bugfix releases behind, which likely includes fixes for several data-corruption issues. See: http://www.postgresql.org/support/versioning/ If that database contains anything of value, plan some time for an upgrade to a more recent - supported - version. Take your time to read the release notes though, as there have been some changes that probably affect you; the change that disabled implicit type-casting between several types of dubious compatibility, for example. That said, from the look of things your duplicate databases do have different oid’s, meaning they are different objects in your database. Are you sure they don’t just _look_ identical due to case-folding or something? Before you start medling with it, make sure you have a recent database dump. Aside from giving you something to restore if things go sideways, the contents of that dump could be illuminating as to what is actually going on here. Can you find both instances of the CREATE DATABASE statements for those databases? The first thing to try with that dump is whether the situation can be restored on a test-system. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- 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 : duplicate DB names
-Original Message- From: Alban Hertroys [mailto:haram...@gmail.com] Sent: Monday, May 26, 2014 11:11 AM To: Khangelani Gama Cc: Thomas Kellerer; pgsql-general@postgresql.org Subject: Re: [GENERAL] postgreSQL : duplicate DB names On 26 May 2014, at 10:28, Khangelani Gama kg...@argility.com wrote: On 26 May 2014, at 10:09, Khangelani Gama kg...@argility.com wrote: Khangelani Gama, 26.05.2014 09:20: Hi Something wrong happened in our server, for some strange and unknown reason (s) the server created another DB, there is two same DB names in one server. Name| Owner | Encoding -+--+-- a9004| postgres | UNICODE a9004| postgres | UNICODE Maybe there are some trailing spaces at one of the names. What do you see when you run this: select ''||datname||'' as name from pg_database By adding the quotes, you can see if there are any trailing (or leading) in the name. Regards Thomas Hi, I get: name -- a9004 template1 template0 a9004 (4 rows) Looks like you might have some index corruption, do those databases have the same oid?: select datname, oid from pg_database What version of the database are you running? What OS? Did anything of notice happen on that server before this showed up? P.S. Please don't top-post on this list. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. It's psql 8.0.4, OS is Red Hat Linux release 9 (Shrike datname |oid + a9004| 4215680815 template1 | 1 template0 | 17229 a9004| 1335509510 (4 rows) 8.0.4? That went EOL almost 4 years ago and even then you should be running a version around 8.0.26. You're 22 bugfix releases behind, which likely includes fixes for several data-corruption issues. See: http://www.postgresql.org/support/versioning/ If that database contains anything of value, plan some time for an upgrade to a more recent - supported - version. Take your time to read the release notes though, as there have been some changes that probably affect you; the change that disabled implicit type-casting between several types of dubious compatibility, for example. That said, from the look of things your duplicate databases do have different oid's, meaning they are different objects in your database. Are you sure they don't just _look_ identical due to case-folding or something? Before you start medling with it, make sure you have a recent database dump. Aside from giving you something to restore if things go sideways, the contents of that dump could be illuminating as to what is actually going on here. Can you find both instances of the CREATE DATABASE statements for those databases? The first thing to try with that dump is whether the situation can be restored on a test-system. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. I try different things, Many Thanks CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. -- 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 : duplicate DB names
Alban Hertroys haram...@gmail.com writes: On 26 May 2014, at 10:28, Khangelani Gama kg...@argility.com wrote: It's psql 8.0.4, OS is Red Hat Linux release 9 (Shrike 8.0.4? That went EOL almost 4 years ago and even then you should be running a version around 8.0.26. Youre 22 bugfix releases behind, which likely includes fixes for several data-corruption issues. See: http://www.postgresql.org/support/versioning/ 8.0.x did not have any real defenses against transaction ID wraparound (the behavior of forcing a shutdown when wraparound gets too close was added in 8.1, and there was no built-in autovacuum back then either). So my money is on this being a wraparound problem, ie, some dead but never-vacuumed pg_database row has returned to visibility because its deleting transaction is now in the future. If you're very lucky, vacuuming pg_database will fix it. But take a backup *first*, in case you're not lucky and vacuuming makes things worse. I'd shut down the postmaster and then make a physical copy (tarball) of the whole data directory tree, just to be sure you can get back to where you are. As a former Red Hatter, I cannot resist making the point that your OS version is even hoarier than your PG version, and is certainly full of since-fixed bugs. You are *years* overdue for some serious attention to software updates. 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] Trouble running nested select - Join query
Rushi wrote 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' Some thoughts: The O sub-query could probably be top-level A correlated sub-query in the where clause should generally use EXISTS, not IN ... ... Though if you can get rid of the whole thing that would be better You might want to use the statement_timeout GUC to kill the query if it takes too long You could separate the the select and the copy into two statements via use of a temporary table - depending on how large the output is that would split the action into two statements that could be independently monitored. Though, to be honest now that I re-read I have no clue how this query inter-plays with PostgresXC... Can you run a smaller input size and use that estimate how long a 100M^2 query should take to complete? Can you calculate in smaller block sizes on the input? David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Trouble-running-nested-select-Join-query-tp5804938p5804995.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] new index type with clustering in mind.
The discussions at PGCon pointed out that with the posting-list
compression logic added in 9.4, GIN indexes are pretty close to this
already. Multiple items on the same heap page will typically only take one
byte of index space per item; but there is an identifiable entry, so you
don't get into these questions of when VACUUM should remove entries, and
it's not lossy so you're not forced to pay the overhead of rechecking every
entry on the linked-to page.
Not to say that 9.4 GIN is necessarily the last word on the subject, but
it would be worth testing it out before deciding that we need something
better. (beta1 is out. It needs testing. Hint hint.)
Hint taken, and first impressions are positive: the compression is very
efficient for the kind of scenario I'm imagining where the key is
deliberately chosen so that the average page has one distinct key. I have a
25Mb gin 'cluster' index on a table where an equivalent regular btree index
is 10 times as large.
So the questions are, a) is this kind of clustering broadly useful (ie not
just to me), b) how much effort will it be to implement a 'vacuum-like'
operation that scans a designated index and performs the relevant
delete/inserts to achieve this kind of clustering? And c) if it is broadly
useful and not a major implementation mountain to climb, is it something
that might be added to the todo list?
If someone can tell me how to decode a `ctid` into a page number (discarding
the row number portion - is there a better way than `
(replace(replace(ctid::text,'(','{'),')','}')::integer[])[1]`), I should be
able to show some analysis demonstrating this working, albeit inefficiently
as I'll have to scan the table itself for the page/key statistics. Would
that sort of analysis be helpful?
Kindest regards
Jack
PS It occurs to me that the btree_gin documentation page for 9.4,
http://www.postgresql.org/docs/9.4/static/btree-gin.html, might benefit from
including some mention of index compression when discussing the relative
performance of regular and gin btree indexes.
--
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 : duplicate DB names
-Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Monday, May 26, 2014 5:35 PM To: Alban Hertroys Cc: Khangelani Gama; Thomas Kellerer; pgsql-general@postgresql.org Subject: Re: [GENERAL] postgreSQL : duplicate DB names Alban Hertroys haram...@gmail.com writes: On 26 May 2014, at 10:28, Khangelani Gama kg...@argility.com wrote: It's psql 8.0.4, OS is Red Hat Linux release 9 (Shrike 8.0.4? That went EOL almost 4 years ago and even then you should be running a version around 8.0.26. You're 22 bugfix releases behind, which likely includes fixes for several data-corruption issues. See: http://www.postgresql.org/support/versioning/ 8.0.x did not have any real defenses against transaction ID wraparound (the behavior of forcing a shutdown when wraparound gets too close was added in 8.1, and there was no built-in autovacuum back then either). So my money is on this being a wraparound problem, ie, some dead but never-vacuumed pg_database row has returned to visibility because its deleting transaction is now in the future. If you're very lucky, vacuuming pg_database will fix it. But take a backup *first*, in case you're not lucky and vacuuming makes things worse. I'd shut down the postmaster and then make a physical copy (tarball) of the whole data directory tree, just to be sure you can get back to where you are. As a former Red Hatter, I cannot resist making the point that your OS version is even hoarier than your PG version, and is certainly full of since-fixed bugs. You are *years* overdue for some serious attention to software updates. regards, tom lane Thanks a lot Tom, And Thanks to everyone who responded. It looks like the best option we have is to restore from the latest backup. And then plan to do the upgrade in the near future. CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
