Re: [GENERAL] Vacuuming strategy

On Tue, Apr 29, 2014 at 4:59 PM, Elanchezhiyan Elango wrote: [...] > With the above query pattern with intensive updates and deletes, I need to > do some aggressive vacuuming. > > Current strategy:I am running with default autovacuum settings (postgres > 9.1.9) and I tried doing a 'vacuum full' fo

[GENERAL] Vacuuming strategy

Hi, I need help on deciding my vacuuming strategy. I need to know if I ever need to do 'vacuum full' for my tables. Tables1: Following is the query patterns on 4 high traffic table in my database: 1. Every 5 minutes about 5 rows in the table are updated. And for a given clock hour the same 50

Re: [GENERAL] Escape double-quotes in text[]?

Raymond O'Donnell wrote > '{"abc", "de"f"}' > > Or would I need to do this? - > > E'{"abc", "de"f"}' Do you realize that both of the above expressions are effectively identical? David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Escape-double-quotes-in-te

Re: [GENERAL] Escape double-quotes in text[]?

Raymond O'Donnell wrote > Hi all, > > Probably a silly question, but I'm having trouble figuring out the > answer... if I'm constructing an string representation of a value to go > into a text[] column, and one of the text literals includes > double-quotes, do I need to escape the literal? > > Fo

Re: [GENERAL] Escape double-quotes in text[]?

On 29/04/2014 22:10, Raymond O'Donnell wrote: > Hi all, > > Probably a silly question, but I'm having trouble figuring out the > answer... if I'm constructing an string representation of a value to go > into a text[] column, and one of the text literals includes > double-quotes, do I need to escap

[GENERAL] Escape double-quotes in text[]?

Hi all, Probably a silly question, but I'm having trouble figuring out the answer... if I'm constructing an string representation of a value to go into a text[] column, and one of the text literals includes double-quotes, do I need to escape the literal? For example, can I insert something like t

Re: [GENERAL] Planned downtime @ Rackspace - 2014-04-29 2100-2200 UTC

All, We have confirmation from Rackspace that the maintenance will begin in ~5 minutes. Thanks! Stephen * Stephen Frost (sfr...@snowman.net) wrote: > Greetings, > > This is take-2 on this. Apologies for the short notice. > > As some may be aware, we are curren

Re: [GENERAL] Altering array(composite-types) without breaking code when inserting them and similar questions

So : 1. drop function 2. alter type: add column 3. create again function with new default argument in a transaction ? On Tue, Apr 29, 2014 at 4:22 PM, Merlin Moncure wrote: > On Sun, Apr 27, 2014 at 4:57 PM, Dorian Hoxha > wrote: > > Since my alternative is using json, that is heavi

Re: [GENERAL] reindexdb

On Tue, Apr 29, 2014 at 10:22 AM, Steve Clark wrote: > We have a small (2GB) 8.4.20 database. The prior maintainer ran a reindexdb > twice a day (originally started with 7.3.x). Can anybody see a reason why we > need to > continue to do this? I used to re-index regularly with earlier 6.x and 7.x

[GENERAL] Planned downtime @ Rackspace - 2014-04-29 2100-2200 UTC

Greetings, This is take-2 on this. Apologies for the short notice. As some may be aware, we are currently working with Rackspace to upgrade the PostgreSQL infrastructure systems which they graciously host for us. As part of these upgrades there will be downtime for systems hosted ther

Re: [GENERAL] pg_ctl start error and lock file

On Tue, Apr 29, 2014 at 6:26 PM, Albe Laurenz wrote: > Set "unix_socket_directory" (in versions before 9.3) or > "unix_socket_directories" (from 9.3 on) in postgresql.conf. > > Yours, > Laurenz Albe > Fantastic, thanks for this setting. regards...

Re: [GENERAL] Passing dynamic parameters to a table-returning function

This should be the example used in the docs for LATERAL JOIN rather than the contrived, rather useless example that is there today. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 "Quality is not an a

Re: [GENERAL] reindexdb

On 04/29/2014 07:22 AM, Steve Clark wrote: Hello, We have a small (2GB) 8.4.20 database. The prior maintainer ran a reindexdb twice a day (originally started with 7.3.x). Can anybody see a reason why we need to continue to do this? I don't. It certainly appears to be a piece of duct-tape pu

[GENERAL] reindexdb

Hello, We have a small (2GB) 8.4.20 database. The prior maintainer ran a reindexdb twice a day (originally started with 7.3.x). Can anybody see a reason why we need to continue to do this? Thanks, -- Stephen Clark *NetWolves Managed Services, LLC.* Director of Technology Phone: 813-579-3200 F

Re: [GENERAL] Altering array(composite-types) without breaking code when inserting them and similar questions

On Sun, Apr 27, 2014 at 4:57 PM, Dorian Hoxha wrote: > Since my alternative is using json, that is heavier (need to store keys in > every row) than composite-types. > Updating an element on a specific composite_type inside an array of them is > done by UPDATE table SET composite[2].x = 24; > > So

Re: [GENERAL] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)

David Noel writes: > Both queries are run from a Java project using the latest JDBC driver. > The PostgreSQL Server versions it's being run on are 9.2 and 9.3. The > query executes and returns just fine when run on a FreeBSD-based > platform, but executes forever when run under Windows. Um .. whi

Re: [GENERAL] pg_ctl start error and lock file

Dev Kumkar wrote: > Faced following issue when trying to start database using pg_ctl: > > FATAL: could not create lock file "/tmp/.s.PGSQL.5432.lock": Permission > denied > > Accidentally someone cleaned up /tmp and recreated it but without any write > permissions to non-root > user, if the no

Re: [GENERAL] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)

> Is both server/client running on FreeBSD or Windows, or are you switching > only part of the stack? When I run it it's all FreeBSD. When the other developer working on it runs it it's all Windows. > It shouldn't get stuck. It might be slower on some platforms, but it > shouldn't really get stuc

Re: [GENERAL] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)

> But there are two of them : ' and ' makes ''. If you use only one > psql/parser will complain. Ha! Wow. That one totally flew by me. It's not a double quotation mark (one character), it's a double _single_ quotation mark (two characters). Yeah, that makes complete sense. Wow. Can't believe I mis

[GENERAL] pg_ctl start error and lock file

Faced following issue when trying to start database using pg_ctl: FATAL: could not create lock file "/tmp/.s.PGSQL.5432.lock": Permission denied Accidentally someone cleaned up /tmp and recreated it but without any write permissions to non-root user, if the non-root user write permissions are gi

Re: [GENERAL] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)

On 29 Duben 2014, 8:59, David Noel wrote: > The query I'm running is: > > "select page.*, coalesce((select COUNT(*) from sentence where > sentence."PageURL" = page."URL" group by page."URL"), 0) as > NoOfSentences from page WHERE "Classification" LIKE CASE WHEN "<>" > THEN " ELSE '%' END ORDER BY "

Re: [GENERAL] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)

On 29/04/2014 12:54, David Noel wrote: 'health'<>'' (if that is what you have) means a boolean expression that compares the literal 'health' with the empty literal '' which is of course always false. Ah. Gotcha. Thanks. I didn't know you could use a single double quotation mark in a query -- I t

Re: [GENERAL] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)

> 'health'<>'' (if that is what you have) means a boolean expression that > compares the > literal 'health' with the empty literal '' which is of course always false. Ah. Gotcha. Thanks. I didn't know you could use a single double quotation mark in a query -- I thought like in most languages that

Re: [GENERAL] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)

On 29/04/2014 12:39, David Noel wrote: Ehh, to clarify I'm referring to the lone _double_ quotation mark at the end of the condition 'health'<>''. I called it a "single quotation mark" because it was a quotation mark all by itself, but realize that could be misread. Single quotation marks are tec

Re: [GENERAL] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)

Ehh, to clarify I'm referring to the lone _double_ quotation mark at the end of the condition 'health'<>''. I called it a "single quotation mark" because it was a quotation mark all by itself, but realize that could be misread. Single quotation marks are technically this: ' Sorry for the newbie sp

Re: [GENERAL] WAL Replication + PITR

basti wrote: > is it possible to have WAL Replication and Point-in-Time Recovery like > follows: > > DB-Master -- (WAL to Slave) --> DB-Slave > | > |---> (PITR to an other Server) > > Thanks for any help! Sure. You can use something like the UNIX command "tee" in "archive_command" t

Re: [GENERAL] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)

> select p.*, s.NoOfSentences > from page p, > lateral (select count(*) as NoOfSentences > from sentence s > where s."PageURL" = p."URL") s > where "Classification" like case ... end > order by "PublishDate" desc > limit 100; Great. Thanks so much! Could I make it

[GENERAL] WAL Replication + PITR

Hello, is it possible to have WAL Replication and Point-in-Time Recovery like follows: DB-Master -- (WAL to Slave) --> DB-Slave | |---> (PITR to an other Server) Thanks for any help! Basti p.s. is there a tutorial somewhere how describe the steps todo when the master is crashed?

Re: [GENERAL] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)

On 04/29/2014 09:44 AM, David Noel wrote: > Ahh, sorry, copied the query over incorrectly. It should read as follows: > > select page.*, coalesce((select COUNT(*) from sentence where > sentence."PageURL" = page."URL" group by page."URL"), 0) as > NoOfSentences from page WHERE "Classification" LIKE

Re: [GENERAL] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)

On 4/29/2014 12:42 AM, David Noel wrote: Ok, thanks for the heads up. It confused me, too. It's code I'm just picking up from another developer, so I don't know why it was done the way it was done. I'm not super proficient with SQL but I'll take a stab at rewriting it. wild guess says it was ba

Re: [GENERAL] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)

Ahh, sorry, copied the query over incorrectly. It should read as follows: select page.*, coalesce((select COUNT(*) from sentence where sentence."PageURL" = page."URL" group by page."URL"), 0) as NoOfSentences from page WHERE "Classification" LIKE CASE WHEN 'health'<>'' THEN 'health' ELSE '%' END O

Re: [GENERAL] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)

On 4/29/14, Achilleas Mantzios wrote: > On 29/04/2014 09:59, David Noel wrote: >> "select page.*, coalesce((select COUNT(*) from sentence where >> sentence."PageURL" = page."URL" group by page."URL"), 0) as >> NoOfSentences from page WHERE "Classification" LIKE CASE WHEN "<>" >> THEN " ELSE '%' EN

[GENERAL] Re: SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)

On 4/29/14, David Noel wrote: > The query I'm running is: > > "select page.*, coalesce((select COUNT(*) from sentence where > sentence."PageURL" = page."URL" group by page."URL"), 0) as > NoOfSentences from page WHERE "Classification" LIKE CASE WHEN "<>" > THEN " ELSE '%' END ORDER BY "PublishDate

Re: [GENERAL] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)

On 29/04/2014 09:59, David Noel wrote: The query I'm running is: "select page.*, coalesce((select COUNT(*) from sentence where sentence."PageURL" = page."URL" group by page."URL"), 0) as NoOfSentences from page WHERE "Classification" LIKE CASE WHEN "<>" THEN " ELSE '%' END ORDER BY "PublishDate"

[GENERAL] SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)

The query I'm running is: "select page.*, coalesce((select COUNT(*) from sentence where sentence."PageURL" = page."URL" group by page."URL"), 0) as NoOfSentences from page WHERE "Classification" LIKE CASE WHEN "<>" THEN " ELSE '%' END ORDER BY "PublishDate" DESC Offset 0 LIMIT 100" I can post the