Re: [GENERAL] Query planner question

On Wednesday, August 20, 2014, Soni M wrote: > Hi Everyone, > > I have this query : > > select t.ticket_id , > tb.transmission_id > from ticket t, > transmission_base tb > where t.latest_transmission_id = tb.transmission_id > and t.ticket_number = tb.ticket_number > and tb.parse_date > ('2014-07-

Re: [GENERAL] Query planner question

Soni M wrote > On Fri, Aug 22, 2014 at 9:10 PM, Alban Hertroys < > haramrae@ > > wrote: > >> On 22 August 2014 14:26, Soni M < > diptatapa@ > > wrote: >> > Currently we have only latest_transmission_id as FK, described here : >> > TABLE "ticket" CONSTRAINT "fkcbe86b0c6ddac9e" FOREIGN KEY >> >

Re: [GENERAL] Query planner question

On Fri, Aug 22, 2014 at 9:10 PM, Alban Hertroys wrote: > On 22 August 2014 14:26, Soni M wrote: > > Currently we have only latest_transmission_id as FK, described here : > > TABLE "ticket" CONSTRAINT "fkcbe86b0c6ddac9e" FOREIGN KEY > > (latest_transmission_id) REFERENCES transmission_base(transm

Re: [GENERAL] ERROR: Problem running post install step

On 08/22/2014 04:34 PM, Brodie S wrote: I have installed with the default data directory with success. Sadly, thats not an option for me Hmmm, so the installer is basically working, just not to that directory. I made this post 4 days ago and sadly, have yet to get a response: http://forums.e

Re: [GENERAL] ERROR: Problem running post install step

I have installed with the default data directory with success. Sadly, thats not an option for me I made this post 4 days ago and sadly, have yet to get a response: http://forums.enterprisedb.com/posts/list/4000.page On Fri, Aug 22, 2014 at 5:25 PM, Adrian Klaver wrote: > On 08/22/2014 04:14

Re: [GENERAL] ERROR: Problem running post install step

On 08/22/2014 04:14 PM, Brodie S wrote: I've been installing as the Postgres user. I've also ensured that the permission on the data folder is RW for every user Just for reference have you tried installing without changing the $DATA directory just to see if it works? Well that exhausted my W

Re: [GENERAL] ERROR: Problem running post install step

I've been installing as the Postgres user.  I've also ensured that the permission on the data folder is RW for every user I did a chmod 777 on the data folder On Fri, Aug 22, 2014 at 5:00 PM, Adrian Klaver wrote: > On 08/22/2014 03:47 PM, Brodie S wrote: >> I am using the 9.0.18 version of th

Re: [GENERAL] ERROR: Problem running post install step

On 8/19/2014 11:03 AM, Brodie S wrote: I am installing the Data directory on a NAS server. what NAS file sharing protocol? (choices include SMB/CIFS, AFP, NFS, and probably others).Network file shares are generally NOT considered 'safe' for relational database storage as many have very

Re: [GENERAL] ERROR: Problem running post install step

On 08/22/2014 03:47 PM, Brodie S wrote: I am using the 9.0.18 version of the installer found here: http://www.enterprisedb.com/products-services-training/pgdownload#osx During the installation, everything stays as the default except the path to the data folder Are you installing as the postgre

Re: [GENERAL] ERROR: Problem running post install step

I am using the 9.0.18 version of the installer found here:http://www.enterprisedb.com/products-services-training/pgdownload#osx During the installation, everything stays as the default except the path to the data folder The databases will be used by poker tracker but they will be installe

Re: [GENERAL] Appended '+' in Column Value

Adrian Klaver writes: > On 08/22/2014 03:03 PM, Rich Shepard wrote: >> On Sat, 23 Aug 2014, Ian Barwick wrote: >>> You have a newline character. Try: >>> select count(*) from benthos where stream = E'StarvationCrk\n'; > Yeah, means either whoever inputted the data kept hitting Enter after > each

Re: [GENERAL] Appended '+' in Column Value

On 08/22/2014 03:03 PM, Rich Shepard wrote: On Sat, 23 Aug 2014, Ian Barwick wrote: You have a newline character. Try: select count(*) from benthos where stream = E'StarvationCrk\n'; Ian, Interesting; that query returned 202 of 204 rows. Yeah, means either whoever inputted the data kep

Re: [GENERAL] Appended '+' in Column Value

On Fri, 22 Aug 2014, Adrian Klaver wrote: Is this only in psql? Adrian, Yes. Thanks, Rich -- 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] Appended '+' in Column Value

On Sat, 23 Aug 2014, Ian Barwick wrote: You have a newline character. Try: select count(*) from benthos where stream = E'StarvationCrk\n'; Ian, Interesting; that query returned 202 of 204 rows. Thanks, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make c

Re: [GENERAL] Appended '+' in Column Value

On 14/08/23 6:46, Rich Shepard wrote: > One column in a table has values for the attribute 'stream'. Some queries > return some rows where a stream name (only identified one so far) has an > appended '+'. I cannot update the table to remove that appended character, > and I've not seen this before

Re: [GENERAL] Appended '+' in Column Value

On 08/22/2014 02:46 PM, Rich Shepard wrote: One column in a table has values for the attribute 'stream'. Some queries return some rows where a stream name (only identified one so far) has an appended '+'. I cannot update the table to remove that appended character, and I've not seen this befor

Re: [GENERAL] Appended '+' in Column Value

On Fri, Aug 22, 2014 at 02:46:46PM -0700, Rich Shepard wrote: > One column in a table has values for the attribute 'stream'. Some queries > return some rows where a stream name (only identified one so far) has an > appended '+'. I cannot update the table to remove that appended character, > and

Re: [GENERAL] ERROR: Problem running post install step

On 08/22/2014 12:31 PM, Brodie S wrote: I'm using the installer Well this page: http://www.postgresql.org/download/macosx/ lists quite a few things that could be construed as installers. Also given that there is Poker in the path I would not be surprised if you where talking about: https:

[GENERAL] Appended '+' in Column Value

One column in a table has values for the attribute 'stream'. Some queries return some rows where a stream name (only identified one so far) has an appended '+'. I cannot update the table to remove that appended character, and I've not seen this before. Example: 2220 | STV | 2012-07-12

Re: [GENERAL] Restart replicated slave procedure

On Fri, Aug 22, 2014 at 3:47 PM, Jerry Sievers wrote: > Yes, changing archive_command to '' or something that returns false will > let you queue the WALs until reverting the change. > > I am assuming you run a version where the archive_mode setting exists > which will be set to 'on' and left that

Re: [GENERAL] ERROR: Problem running post install step

I'm using the installer On Tue, Aug 19, 2014 at 3:53 PM, Adrian Klaver wrote: > On 08/19/2014 11:03 AM, Brodie S wrote: > >> I am trying to install PostgreSQL on my OS X Mavericks Server. >> > > Installing with what? > > > >> I am installing the Data directory on a NAS server. However, I'm havi

Re: [GENERAL] WAL log level compatibility

On Thu, Aug 21, 2014 at 11:26:39PM +, Andy Lau wrote: > Hi everyone, > > Are 'wal_level = archive' vs 'wal_level = hot_standby' ok to mix? For example, > let's say I had a PostgreSQL database running and creating WAL logs in the > archive level. Then we switch to the hot standby level to suppo

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

hubert depesz lubaczewski wrote: > I have developer with pg 9.3.5, which is reporing something really strange. > > He runs importer, which does, in single transaction: > > begin; > select * from table where pkey = limit 1 for update; > update table set ... where pkey = ; > commit; > > and two b

Re: [GENERAL] Restart replicated slave procedure

On Fri, Aug 22, 2014 at 2:21 PM, Jerry Sievers wrote: > Joseph Kregloh writes: > > > Hi, > > > > Currently I am doing asynchronous replication from master to > > slave. Now if I restart the slave it will fall out of sync with the > > master. Is there a correct procedure or set of steps to avoid

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On 08/22/2014 11:14 AM, hubert depesz lubaczewski wrote: On Fri, Aug 22, 2014 at 7:54 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: Which in itself might be a clue. Is all the code/data running on/coming from that machine or is some coming in remotely? Where netwo

Re: [GENERAL] Restart replicated slave procedure

Joseph Kregloh writes: > Hi, > > Currently I am doing asynchronous replication from master to > slave. Now if I restart the slave it will fall out of sync with the > master. Is there a correct procedure or set of steps to avoid this? I > am looking for best practices or suggestions. Whenever my s

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On Fri, Aug 22, 2014 at 7:54 PM, Adrian Klaver wrote: > Which in itself might be a clue. > > Is all the code/data running on/coming from that machine or is some coming > in remotely? > > Where network latency might be an issue? > All locally, but hey - how could network latency be a problem? Tra

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On 08/22/2014 10:50 AM, hubert depesz lubaczewski wrote: On Fri, Aug 22, 2014 at 7:43 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: Which begs the question, what is different about that machine? No idea. I can pass all the question you might have, but I'm ~ 6000 miles away fr

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On Fri, Aug 22, 2014 at 7:49 PM, Tom Lane wrote: > You have not shown us the full sequence of events leading up to the > deadlock failure, but I hypothesize that there were yet other transactions > that updated that same row in the very recent past. That might allow > there to be more than one t

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On Fri, Aug 22, 2014 at 7:43 PM, Adrian Klaver wrote: > Which begs the question, what is different about that machine? > No idea. I can pass all the question you might have, but I'm ~ 6000 miles away from any machine running this code. depesz

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

hubert depesz lubaczewski writes: > On Fri, Aug 22, 2014 at 6:45 PM, Adrian Klaver > wrote: >> So process 66017 and 66014 are blocking each because they are running the >> exact same queries. The interesting part is the process with the lower pid >> is starting later then the none with the higher

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On 08/22/2014 10:36 AM, hubert depesz lubaczewski wrote: On Fri, Aug 22, 2014 at 7:20 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: So why are different processes running the exact same queries coming in on different ports? the importer is parallelized, and sometimes two

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On Fri, Aug 22, 2014 at 7:20 PM, Adrian Klaver wrote: > So why are different processes running the exact same queries coming in on > different ports? > the importer is parallelized, and sometimes two processes handle batches of data that happen to update the same "top level row". but the deadlo

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On Fri, Aug 22, 2014 at 7:29 PM, John R Pierce wrote: > On 8/22/2014 9:29 AM, hubert depesz lubaczewski wrote: > >> select * from table where pkey = limit 1 for update; >> > why is there a limit 1 in there?pkey=somevalue should only return a > single row. if it DID return multiple rows, y

[GENERAL] Constraint exclusion on tables partitioned over range types

Hello, we are setting up a partitioned table based on tstzrange in PG 9.3, something like: create table offer ( during tstzrange not null, ... constraint virtual check (false) no inherit ); create table offer_201408 ( check (during <@ '[2014-08-01Z,2014-09

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On 8/22/2014 9:29 AM, hubert depesz lubaczewski wrote: select * from table where pkey = limit 1 for update; why is there a limit 1 in there?pkey=somevalue should only return a single row. if it DID return multiple rows, you don't have an ORDER BY, so the limit 1 would be indeterminate.

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On 08/22/2014 10:15 AM, hubert depesz lubaczewski wrote: On Fri, Aug 22, 2014 at 6:45 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: So process 66017 and 66014 are blocking each because they are running the exact same queries. The interesting part is the process with the

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On Fri, Aug 22, 2014 at 6:45 PM, Adrian Klaver wrote: > So process 66017 and 66014 are blocking each because they are running the > exact same queries. The interesting part is the process with the lower pid > is starting later then the none with the higher pid. > Locking is obvious. But why dead

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On Fri, Aug 22, 2014 at 9:29 AM, hubert depesz lubaczewski wrote: > I have developer with pg 9.3.5, which is reporing something really strange. > > He runs importer, which does, in single transaction: > > begin; > select * from table where pkey = limit 1 for update; > update table set ... where

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On 08/22/2014 09:29 AM, hubert depesz lubaczewski wrote: I have developer with pg 9.3.5, which is reporing something really strange. He runs importer, which does, in single transaction: begin; select * from table where pkey = limit 1 for update; update table set ... where pkey = ; commit; and

[GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

I have developer with pg 9.3.5, which is reporing something really strange. He runs importer, which does, in single transaction: begin; select * from table where pkey = limit 1 for update; update table set ... where pkey = ; commit; and two backends running the same transaction deadlock. I che

[GENERAL] Restart replicated slave procedure

Hi, Currently I am doing asynchronous replication from master to slave. Now if I restart the slave it will fall out of sync with the master. Is there a correct procedure or set of steps to avoid this? I am looking for best practices or suggestions. Whenever my slave fell out of sync I would either

Re: [GENERAL] postgres_fdw: Running default expressions on foreign server

Tommy Duek writes: > I’m working on a project now that uses the postgres_fdw extensively. Do you > know if this will be fixed in 9.4? I figure it’s worth checking since 9.4 is > scheduled to be released any day now, before I start rewriting the whole > project. No, there's no change in this a

Re: [GENERAL] postgres_fdw: Running default expressions on foreign server

On Fri, Aug 22, 2014 at 6:15 AM, Tommy Duek wrote: > Hi Tom, > > I realize that postgres_fdw on 9.3 doesn't support default expressions > that run on the foreign server. In my case, I have a unique, > auto-incrementing ID column that the remote server keeps track of in a > sequence. The local for

[GENERAL] postgres_fdw: Running default expressions on foreign server

Hi Tom, I realize that postgres_fdw on 9.3 doesn’t support default expressions that run on the foreign server. In my case, I have a unique, auto-incrementing ID column that the remote server keeps track of in a sequence. The local foreign table doesn’t have access to this and tries to INSERT wi

Re: [GENERAL] Query planner question

On 22 August 2014 14:26, Soni M wrote: > Currently we have only latest_transmission_id as FK, described here : > TABLE "ticket" CONSTRAINT "fkcbe86b0c6ddac9e" FOREIGN KEY > (latest_transmission_id) REFERENCES transmission_base(transmission_id) > > Change the query to include only FK still result t

Re: [GENERAL] POWA tool

How to include pg_stat_statements in postgres.conf.powa is need it. any help.. thanks, On Wed, Aug 20, 2014 at 11:51 PM, Ramesh T wrote: > yes, > > in my postgres.conf pg_stat_statements is not their needs powa is > released 19 aug. > > > On Wed, Aug 20, 2014 at 10:17 PM, Raghu Ram > wrote:

[GENERAL] WAL log level compatibility

Hi everyone, Are 'wal_level = archive' vs 'wal_level = hot_standby' ok to mix? For example, let's say I had a PostgreSQL database running and creating WAL logs in the archive level. Then we switch to the hot standby level to support a hot standby, then go back to the archive level, all while uploa

Re: [GENERAL] Query planner question

On Thu, Aug 21, 2014 at 9:26 AM, David G Johnston < david.g.johns...@gmail.com> wrote: > Soni M wrote > > Hi Everyone, > > > > I have this query : > > > > select t.ticket_id , > > tb.transmission_id > > from ticket t, > > transmission_base tb > > where t.latest_transmission_id = tb.transmission_id

Re: [GENERAL] POWA tool

On 22/08/2014 13:08, Birta Levente wrote: On 20/08/2014 23:27, Julien Rouhaud wrote: On Wed, Aug 20, 2014 at 6:46 PM, Thomas Kellerer > wrote: Ramesh T schrieb am 20.08.2014 um 17:41: > Hello, > > when i ran following query, >

Re: [GENERAL] POWA tool

On 20/08/2014 23:27, Julien Rouhaud wrote: On Wed, Aug 20, 2014 at 6:46 PM, Thomas Kellerer > wrote: Ramesh T schrieb am 20.08.2014 um 17:41: > Hello, > > when i ran following query, > postgres=# SELECT * FROM pg_stat_s

Re: [GENERAL] Use of 'now' constant datatype in view to take advantage of partitioned table

On 21 August 2014 11:36, Patrick Dung wrote: > 2. > now() is dynamic but it scan all the partitioned tables. > Most likely you partitioned on a timestamp without time zone, while now() returns a timestamp with time zone. The possible time zone difference causes that the database doesn't know in

Re: [GENERAL] SELECT 'NOW()' - OK, SELECT 'CLOCK_TIMESTAMP()' - ERROR

Hi 2014-08-22 9:05 GMT+02:00 David G Johnston : > Piotr Gasidło wrote > > Hello, > > > > I found strange PostgreSQL 9.3 behavior: > > > >> select now()::timestamp, 'now()'::timestamp; > > now | timestamp > > +---

Re: [GENERAL] SELECT 'NOW()' - OK, SELECT 'CLOCK_TIMESTAMP()' - ERROR

Piotr Gasidło wrote > Hello, > > I found strange PostgreSQL 9.3 behavior: > >> select now()::timestamp, 'now()'::timestamp; > now | timestamp > + > 2014-08-22 08:34:00.883268 | 2014-08-22 08:34:00.883268 > >

Re: [GENERAL] SELECT 'NOW()' - OK, SELECT 'CLOCK_TIMESTAMP()' - ERROR

On 14/08/22 15:40, Piotr Gasidło wrote: > Hello, > > I found strange PostgreSQL 9.3 behavior: > >> select now()::timestamp, 'now()'::timestamp; > now | timestamp > + > 2014-08-22 08:34:00.883268 | 2