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

[Ian Barwick]

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 | 2014-08-22 08:34:00.883268
> 
> Second column is now() in single apostrophes.
> 
> Now, I tried similar function, clock_timestamp() and get:
> 
>> select clock_timestamp()::timestamp, 'clock_timestamp()'::timestamp;
> ERROR:  invalid input syntax for type timestamp: "clock_timestamp()"
> LINE 1: select clock_timestamp()::timestamp, 'clock_timestamp()'::ti...
>^
> 
> Why is NOW() so special? Where is it documented? 

Here:
  
http://www.postgresql.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

  "All the date/time data types also accept the special literal value 'now' to
   specify the current date and time"

and also here:

  http://www.postgresql.org/docs/9.3/static/datatype-datetime.html#AEN5861

Regards


Ian Barwick


-- 
 Ian Barwick   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] SELECT 'NOW()' - OK, SELECT 'CLOCK_TIMESTAMP()' - ERROR

[David G Johnston]

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
> 
> Second column is now() in single apostrophes.
> 
> Now, I tried similar function, clock_timestamp() and get:
> 
>> select clock_timestamp()::timestamp, 'clock_timestamp()'::timestamp;
> ERROR:  invalid input syntax for type timestamp: "clock_timestamp()"
> LINE 1: select clock_timestamp()::timestamp, 'clock_timestamp()'::ti...
>^
> 
> Why is NOW() so special? Where is it documented? And why not working with
> other timestamp returning internal functions?
> 
>> select version();
>version
> 
> --
>  PostgreSQL 9.3.4 on amd64-portbld-freebsd10.0, compiled by FreeBSD clang
> version 3.3 (tags/RELEASE_33/final 183502) 20130610, 64-bit
> (1 wiersz)
> 
> 
> -- 
> Piotr Gasidło

SELECT ' now** '::timestamp --works

Pretty much any symbol before or after the word now is allowed and you still
get a valid result.  Putting a letter or number anywhere in the string
causes an input syntax error.

Tested on 9.0

As for documentation:

http://www.postgresql.org/docs/9.2/interactive/datetime-input-rules.html

2.b

'now' is a "special string" as referenced in this rule

The tokenizer must be constructed to throw away whitespace and any symbols
except those used in normal timestamps (~ [:/-])



Yep, ^ gives me an error.

That appendix section is missing considerable detail that I've inferred from
the observed behavior - though some of the gaps are filled in once you've
read the following:

http://www.postgresql.org/docs/9.2/interactive/datatype-datetime.html

The above also explains that the special SQL keywords cannot be used as
string literals though as is often the case it omits any discussion as to
why.  The fact that they are functions obviously does not preclude them from
also being keywords...

Most likely its this way for SQL standards compatibility reasons.

Do you have a use-case you'd like to share or is this curiosity after
accidentally finding out that 'now'::timestamp actually works?

David J.







--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/SELECT-NOW-OK-SELECT-CLOCK-TIMESTAMP-ERROR-tp5815823p5815826.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] SELECT 'NOW()' - OK, SELECT 'CLOCK_TIMESTAMP()' - ERROR

[Pavel Stehule]

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
> > +
> >  2014-08-22 08:34:00.883268 | 2014-08-22 08:34:00.883268
> >
> > Second column is now() in single apostrophes.
> >
> > Now, I tried similar function, clock_timestamp() and get:
> >
> >> select clock_timestamp()::timestamp, 'clock_timestamp()'::timestamp;
> > ERROR:  invalid input syntax for type timestamp: "clock_timestamp()"
> > LINE 1: select clock_timestamp()::timestamp, 'clock_timestamp()'::ti...
> >^
> >
> > Why is NOW() so special? Where is it documented? And why not working with
> > other timestamp returning internal functions?
> >
> >> select version();
> >version
> >
> >
> --
> >  PostgreSQL 9.3.4 on amd64-portbld-freebsd10.0, compiled by FreeBSD clang
> > version 3.3 (tags/RELEASE_33/final 183502) 20130610, 64-bit
> > (1 wiersz)
> >
> >
> > --
> > Piotr Gasidło
>
> SELECT ' now** '::timestamp --works
>
> Pretty much any symbol before or after the word now is allowed and you
> still
> get a valid result.  Putting a letter or number anywhere in the string
> causes an input syntax error.
>
> Tested on 9.0
>
> As for documentation:
>
> http://www.postgresql.org/docs/9.2/interactive/datetime-input-rules.html
>
> 2.b
>
> 'now' is a "special string" as referenced in this rule
>
> The tokenizer must be constructed to throw away whitespace and any symbols
> except those used in normal timestamps (~ [:/-])
>
> 
>
> Yep, ^ gives me an error.
>
> That appendix section is missing considerable detail that I've inferred
> from
> the observed behavior - though some of the gaps are filled in once you've
> read the following:
>
> http://www.postgresql.org/docs/9.2/interactive/datatype-datetime.html
>
> The above also explains that the special SQL keywords cannot be used as
> string literals though as is often the case it omits any discussion as to
> why.  The fact that they are functions obviously does not preclude them
> from
> also being keywords...
>
> Most likely its this way for SQL standards compatibility reasons.
>
> Do you have a use-case you'd like to share or is this curiosity after
> accidentally finding out that 'now'::timestamp actually works?
>
> David J.
>
>
>
there are more than "now"

postgres=# select 'now'::timestamp;
 timestamp

 2014-08-22 09:08:26.956702
(1 row)

postgres=# select 'tomorrow'::timestamp;
  timestamp
-
 2014-08-23 00:00:00
(1 row)

postgres=# select 'today'::timestamp;
  timestamp
-
 2014-08-22 00:00:00
(1 row)

postgres=# select 'yesterday'::timestamp;
  timestamp
-
 2014-08-21 00:00:00
(1 row)


Regards

Pavel



>
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/SELECT-NOW-OK-SELECT-CLOCK-TIMESTAMP-ERROR-tp5815823p5815826.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] Use of 'now' constant datatype in view to take advantage of partitioned table

[Alban Hertroys]

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 which partition to look.

In a similar vein, the function you defined to return your timestamp you
marked 'immutable', which it should most definitely not be; time moves on,
after all.

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

Re: [GENERAL] POWA tool

[Birta Levente]

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_statements;
>
>
>   ERROR:  relation "pg_stat_statements" does not exist
>   LINE 1: SELECT * FROM pg_stat_statements;
>
>
> i need to install POWA..i got powa.zip

No, you need to install the extension pg_stat_statements:

http://www.postgresql.org/docs/current/static/pgstatstatements.html
http://www.postgresql.org/docs/current/static/sql-createextension.html



You also need the extensions plpgsql (which should already be 
installed) and btree_gist.


When installing the powa extension (CREATE EXTENSION powa;), postgres 
will warn you of missing dependancy if any.


You can also refer to the installation documentation 
(https://github.com/dalibo/powa/blob/master/README.md).



I just installed POWA and don't understand what mean in readme the 
configure guc ?


Added extension and I get working the GUI, I can login, but have no 
databases. How to add?


Thanks

Levi

Re: [GENERAL] POWA tool

[Birta Levente]

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,
>   postgres=# SELECT * FROM pg_stat_statements;
>
>
>   ERROR:  relation "pg_stat_statements" does not exist
>   LINE 1: SELECT * FROM pg_stat_statements;
>
>
> i need to install POWA..i got powa.zip

No, you need to install the extension pg_stat_statements:

http://www.postgresql.org/docs/current/static/pgstatstatements.html
http://www.postgresql.org/docs/current/static/sql-createextension.html



You also need the extensions plpgsql (which should already be 
installed) and btree_gist.


When installing the powa extension (CREATE EXTENSION powa;), postgres 
will warn you of missing dependancy if any.


You can also refer to the installation documentation 
(https://github.com/dalibo/powa/blob/master/README.md).



I just installed POWA and don't understand what mean in readme the 
configure guc ?


Added extension and I get working the GUI, I can login, but have no 
databases. How to add?


Never mind. ... forget to restart the server with powa in 
shared_preload_libraries


Levi

Re: [GENERAL] Query planner question

[Soni M]

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
> > and t.ticket_number = tb.ticket_number
> > and tb.parse_date > ('2014-07-31');
> >
> > Execution plan: http://explain.depesz.com/s/YAak
> >
> > Indexes on ticket :
> > "ticket_pkey" PRIMARY KEY, btree (ticket_id) CLUSTER
> > "ticket_by_latest_transmission" btree (latest_transmission_id)
> > "ticket_by_ticket_number" btree (ticket_number)
> >
> > This query only returns some portions of rows from ticket table.
> > The question is, Why does postgres need to get all the rows from ticket
> > table in order to complete this query?
> > Can't postgres use indexes to get only needed rows on ticket table?
> >
> > I try set seqscan to off, but still index scan try to get all rows on
> > ticket table.
> > Here's the execution plan : http://explain.depesz.com/s/abH2
>
> Short answer: you haven't defined "(latest_transmission_id, ticket_number)"
> as being a foreign key onto the transmission_base table yet you seem to
> want
> it to act like one.


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 the same:
explain select t.ticket_id ,
tb.transmission_id
from ticket t,
transmission_base tb
where t.latest_transmission_id = tb.transmission_id
and tb.parse_date > ('2014-07-31');
QUERY PLAN
--
 Hash Join  (cost=113928.06..2583606.96 rows=200338 width=8)
   Hash Cond: (t.latest_transmission_id = tb.transmission_id)
   ->  Seq Scan on ticket t  (cost=0.00..1767767.26 rows=69990826 width=8)
   ->  Hash  (cost=108923.38..108923.38 rows=400374 width=4)
 ->  Index Scan using transmission_base_by_parse_date on
transmission_base tb  (cost=0.00..108923.38 rows=400374 width=4)
   Index Cond: (parse_date > '2014-07-31 00:00:00'::timestamp
without time zone)
(6 rows)

I've googling this one, it seems that's how hash join works. For hash join
operation, the join predicate cannot be used for the index scan, only
independent predicate can be used in index scan.
http://use-the-index-luke.com/sql/join/hash-join-partial-objects


>
Because of this failure the planner considers the following:
>
> Nested Looping over 380,000 records is going to suck so it tries some
> advanced "merge/join" techniques to try and speed things up.  In any such
> alternative the entire ticket table needs to be considered since there is
> no
> constraint provided for that table - the only constraint in on
> transmission_base and it rightly is using an index to find records matching
> the where clause.
>
> Since ticket_number and latest_transmission_id are found in separate
> indexes
> I do not believe the planner can make use of an Index Only scan to fulfill
> the join so each index lookup would require a corresponding heap lookup
> which means extra work compared to just sequentially scanning the heap in
> the first place.  Since it is going to hit the entire thing in either case
> the sequential scan is the logical choice for it to make.
>
> Others will correct any factual mistakes I may have made - I am theorizing
> here and do not understand the planner sufficient well to be 100% certain
> that an FK definition will solve the problem.
>
> David J.
>
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Query-planner-question-tp5815659p5815661.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
>



-- 
Regards,

Soni Maula Harriz

[GENERAL] WAL log level compatibility

[Andy Lau]

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
uploading WAL logs to the same location. Would PostgreSQL be able to do
PITR over this entire span of time?

Thanks!
-Andy

Re: [GENERAL] POWA tool

[Ramesh T]

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:
>
>> On Wed, Aug 20, 2014 at 10:08 PM, Raymond O'Donnell  wrote:
>>
>>>  On 20/08/2014 16:41, Ramesh T wrote:
>>> > Hello,
>>> >
>>> >  when i ran  following query,
>>> >   postgres=# SELECT * FROM pg_stat_statements;
>>> >
>>> >
>>> >   ERROR:  relation "pg_stat_statements" does not exist
>>> >   LINE 1: SELECT * FROM pg_stat_statements;
>>> >
>>> >
>>> > i need to install POWA..i got powa.zip
>>> >  please let me know how to install POWA.ZIP for my postgres using putty
>>> > tool ..
>>>
>>
>> are you referring below Tool ?
>>
>> PoWA is PostgreSQL Workload Analyzer that gathers performance stats and
>> provides real-time charts and graph to help monitor and tune your
>> PostgreSQL servers. It is similar to Oracle AWR or SQL Server MDW.
>>
>> http://www.postgresql.org/about/news/1537/
>>
>> Thanks & Regards
>> Raghu Ram
>>
>
>

Re: [GENERAL] Query planner question

[Alban Hertroys]

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 the same:
> explain select t.ticket_id ,
> tb.transmission_id
> from ticket t,
> transmission_base tb
> where t.latest_transmission_id = tb.transmission_id
> and tb.parse_date > ('2014-07-31');
> QUERY PLAN
> --
>  Hash Join  (cost=113928.06..2583606.96 rows=200338 width=8)
>Hash Cond: (t.latest_transmission_id = tb.transmission_id)
>->  Seq Scan on ticket t  (cost=0.00..1767767.26 rows=69990826 width=8)
>->  Hash  (cost=108923.38..108923.38 rows=400374 width=4)
>  ->  Index Scan using transmission_base_by_parse_date on
> transmission_base tb  (cost=0.00..108923.38 rows=400374 width=4)
>Index Cond: (parse_date > '2014-07-31 00:00:00'::timestamp
> without time zone)
> (6 rows)

Do you have an index on ticket (latest_transmission_id)?

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see 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

[GENERAL] postgres_fdw: Running default expressions on foreign server

[Tommy Duek]

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 with IDs that have already been 
taken in the original table on the remote server.

After seeing this post: 
http://www.postgresql.org/message-id/26654.1380145...@sss.pgh.pa.us, I’m 
hopeful honoring these default expressions in the foreign server will be 
supported at some point.

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. 

Thanks,
Tommy Duek

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

[Michael Paquier]

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 foreign table doesn't have access to this and tries to
> INSERT with IDs that have already been taken in the original table on the
> remote server.
>

> After seeing this post:
> http://www.postgresql.org/message-id/26654.1380145...@sss.pgh.pa.us, I'm
> hopeful honoring these default expressions in the foreign server will be
> supported at some point.
>
> 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.
>

Don't count on that for 9.4, that's too late for it (and that's not a
straight-forward problem). But, you can actually use a trick here to
support global sequence IDs:
1) define a view wrapping nextval for this sequence on the foreign server:
create sequence seq;
create view seq_view as select nextval('seq') as a;
2) On the local server, create a foreign table that scans the view already
defined in foreign server:
create foreign server foreign_seq_table (a bigint) server postgres_server
options (table_name 'seq_view');
3) Create on local server a function querying foreign_seq_table:
create function foreign_seq_nextval() returns bigint as 'select a from
foreign_seq_table;' language sql;

And now use each functions in local and foreign servers and you are fine
for the ID uniqueness. Note that you could also use an approach with
uuid-based methods to limit network delay across nodes as well.
-- 
Michael

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

[Tom Lane]

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 area in 9.4.

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] Restart replicated slave procedure

[Joseph Kregloh]

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
issue a new pg_base_backup() or set the master to pg_start_backup() do an
rsync and stop using pg_stop_backup(). If there is a way to avoid any of
that, for example pause replication to hold all the wal files until the
replicated slave comes back and then release them once the replicated slave
is up.

I apologize if this question has already been asked. I did some searching
beforehand.

Thanks,
-Joseph Kregloh

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

[hubert depesz lubaczewski]

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 checked for duplicated rows with the same pkey value - none are there.
And frankly - I'm out of ideas.

What could be wrong in such case?

Detailed logs, with just some obfuscation:
https://depesz.privatepaste.com/0594a93459

depesz

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

[Adrian Klaver]

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 two backends running the same transaction deadlock.

I checked for duplicated rows with the same pkey value - none are there.
And frankly - I'm out of ideas.

What could be wrong in such case?


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.


So what exactly is 'importer' and what does it do?

Also what is this (59303)?



Detailed logs, with just some obfuscation:
https://depesz.privatepaste.com/0594a93459

depesz



--
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

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

[Jeff Janes]

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 pkey = ;
> commit;
>
> and two backends running the same transaction deadlock.
>
> I checked for duplicated rows with the same pkey value - none are there.
> And frankly - I'm out of ideas.
>

What transaction isolation level is being used?

Cheers,

Jeff

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

[hubert depesz lubaczewski]

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 deadlock? There is just single row, and it
shouldn't be able to deadlock on it?!


> So what exactly is 'importer' and what does it do?
>

Some software written by some guy. Runs lots of queries, but the only
problem we have is with these transactions.


> Also what is this (59303)?
>

log_line_prefix is  '%m %r %p %u %d ' so it's port number.

depesz

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

[Adrian Klaver]

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 lower pid is starting later then the none with the higher pid.


Locking is obvious. But why deadlock? There is just single row, and it
shouldn't be able to deadlock on it?!


Well both queries are doing SELECT .. FOR UPDATE as well as UPDATE. From 
what I see there are four queries contending for the same row.




So what exactly is 'importer' and what does it do?


Some software written by some guy. Runs lots of queries, but the only
problem we have is with these transactions.

Also what is this (59303)?


log_line_prefix is  '%m %r %p %u %d ' so it's port number.


So why are different processes running the exact same queries coming in 
on different ports?





depesz



--
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

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

[John R Pierce]

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.


--
john r pierce  37N 122W
somewhere on the middle of the left coast



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

[GENERAL] Constraint exclusion on tables partitioned over range types

[Daniele Varrazzo]

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-01Z)'::tstzrange)
) inherits (offer);

create table offer_201409 (
check (during <@ '[2014-09-01Z,2014-10-01Z)'::tstzrange)
) inherits (offer);

I haven't found a way to make the planner constraint exclusion kicking in:

=# explain select * from offer where during @> '2014-08-03'::timestamptz;

Append  (cost=0.00..27.25 rows=3 width=248)
   ->  Seq Scan on offer  (cost=0.00..0.00 rows=1 width=248)
 Filter: (during @> '2014-08-03 00:00:00+01'::timestamp
with time zone)
  ->  Seq Scan on offer_201408  (cost=0.00..13.62 rows=1 width=248)
Filter: (during @> '2014-08-03 00:00:00+01'::timestamp
with time zone)
  ->  Seq Scan on offer_201409  (cost=0.00..13.62 rows=1 width=248)
Filter: (during @> '2014-08-03 00:00:00+01'::timestamp
with time zone)

Similar results using tztzrange OP tstzrange operators with OP in &&, @>, <@.

Seqscans aside, as these tables are empty so they are expected, I
wonder if there is a way to organize the operators used in the
constraints and the ones used in the query so that the query planner
would be able to exclude some of the tables before querying them, as
is easy to do implementing range constraints on the base tstz type and
its ordering operators.

It would be also nice if the always failing constraint on the base
table could suggest the planner that there is no record to be found
there: I think this would be easier to implement but not as useful as
for the ranges.

Thank you very much,

-- Daniele


-- 
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] deadlock in single-row select-for-update + update scenario? How could it happen?

[hubert depesz lubaczewski]

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, you don't have an ORDER BY,
> so the limit 1 would be indeterminate.
>

leftover from some other thing.

depesz

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

[hubert depesz lubaczewski]

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 deadlocking problem is happening only on one machine, though very
repeatably.

depesz

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

[Adrian Klaver]

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 processes handle batches
of data that happen to update the same "top level row".

but the deadlocking problem is happening only on one machine, though
very repeatably.


Which begs the question, what is different about that machine?



depesz



--
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

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

[Tom Lane]

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 pid.

> Locking is obvious. But why deadlock? There is just single row, and it
> shouldn't be able to deadlock on it?!

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 tuple lock involved (ie, locks on different
versions of the row), which would create some scope for a deadlock
failure.

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] deadlock in single-row select-for-update + update scenario? How could it happen?

[hubert depesz lubaczewski]

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]

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 tuple lock involved (ie, locks on different
> versions of the row), which would create some scope for a deadlock
> failure.
>

Well, showing all events is difficult due to parallelization of importer,
but shouldn't "select for update" solve the problem of other locks?

The transactions are exactly as shown - select for update and then update.

depesz

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

[Adrian Klaver]

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 from any machine running this code.


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?



depesz



--
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

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

[hubert depesz lubaczewski]

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? Transaction
gets the lock on row, and then it updates. the same row. in the same
transaction. with nothing else in the transaction. where is here place for
deadlock for another, identical transaction?

depesz

Re: [GENERAL] Restart replicated slave procedure

[Jerry Sievers]

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 slave fell
> out of sync I would either issue a new pg_base_backup() or set the
> master to pg_start_backup() do an rsync and stop using
> pg_stop_backup(). If there is a way to avoid any of that, for example
> pause replication to hold all the wal files until the replicated slave
> comes back and then release them once the replicated slave is up.
>
> I apologize if this question has already been asked. I did some searching 
> beforehand.

See the manual and read up on the 2 GUCs; archive_command and wal_keep_segments.

wal_keep_segments lets you hold a configurable number of WAL segments
back and buy some more time till you have to resync the stand bys.

Setting archive_command to '' or something like '/bin/false' lets you
delay archiving forever till you change them back again and/or fill
whatever file system pg_xlog writes to :-)

>
> Thanks,
> -Joseph Kregloh
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
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] deadlock in single-row select-for-update + update scenario? How could it happen?

[Adrian Klaver]

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 network latency might be an issue?


All locally, but hey - how could network latency be a problem?
Transaction gets the lock on row, and then it updates. the same row. in
the same transaction. with nothing else in the transaction. where is
here place for deadlock for another, identical transaction?


Not sure, just the combination of parallel operations and remote 
connections seemed to be an avenue to explore. Given that everything is 
local, turns out it was dead end.


Looking at the pastebin log again, am I reading it right that the first 
process actually COMMITs properly?


Also is there a trigger in the mix that might be fouling things up?



depesz



--
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

Re: [GENERAL] Restart replicated slave procedure

[Joseph Kregloh]

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 this? I
> > am looking for best practices or suggestions. Whenever my slave fell
> > out of sync I would either issue a new pg_base_backup() or set the
> > master to pg_start_backup() do an rsync and stop using
> > pg_stop_backup(). If there is a way to avoid any of that, for example
> > pause replication to hold all the wal files until the replicated slave
> > comes back and then release them once the replicated slave is up.
> >
> > I apologize if this question has already been asked. I did some
> searching beforehand.
>
> See the manual and read up on the 2 GUCs; archive_command and
> wal_keep_segments.
>
>
Thanks, i'll read into this some more.


> wal_keep_segments lets you hold a configurable number of WAL segments
> back and buy some more time till you have to resync the stand bys.
>
> Setting archive_command to '' or something like '/bin/false' lets you
> delay archiving forever till you change them back again and/or fill
> whatever file system pg_xlog writes to :-)
>
>
So disabling the archive_command by setting it to and empty string or
/bin/false will effectively pause log shipping? When I re-enable the
archive command will it continue where it left of when the archive_command
was "disabled"?



> >
> > Thanks,
> > -Joseph Kregloh
> >
>
> --
> Jerry Sievers
> Postgres DBA/Development Consulting
> e: postgres.consult...@comcast.net
> p: 312.241.7800
>

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

[Alvaro Herrera]

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 backends running the same transaction deadlock.

FWIW this problem was reported also by Andrew Sackville-West at
http://www.postgresql.org/message-id/20140731233051.GN17765@andrew-ThinkPad-X230

I strongly suspect now that the problem is related to the locking of
updated versions as heap_lock_tuple_updated, and perhaps the internal
locking done by EvalPlanQual.  Haven't traced through it.

-- 
Álvaro Herrerahttp://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] WAL log level compatibility

[Bruce Momjian]

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 support a hot
> standby, then go back to the archive level, all while uploading WAL logs to 
> the
> same location. Would PostgreSQL be able to do PITR over this entire span of
> time?

Yes, PITR needs any wal_level other than "minimal".

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
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] ERROR: Problem running post install step

[Brodie S]

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 having
>> an issue. Here is the section of the log file that displays the error:
>>
>> fixing permissions on existing directory /Volumes/Poker/Databases
>> ... ok
>> creating subdirectories ... ok
>> selecting default max_connections ... 100
>> selecting default shared_buffers ... 32MB
>> creating configuration files ... ok
>> creating template1 database in /Volumes/Poker/Databases/base/1 ...
>> Failed to initialise the database cluster with initdb
>>
>
> So the above is the initial error.
>
>
>
>> I've confirmed that the user "postgres" is the owner of the Databases
>> folder and has full permissions.
>>
>
> Have you tried creating something in /Volumes/Poker/Databases/ as postgres?
>
> Is the installer actually running as postgres?
>
>
>  Not sure what else could be causing this issue.
>>
>> Any help would be greatly appreciated.
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Restart replicated slave procedure

[Joseph Kregloh]

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 way.
>
>
Yep, I run version 9.3 on all the environments.


>
> Joseph Kregloh  writes:
>
> > 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
> this? I
> > > am looking for best practices or suggestions. Whenever my slave
> fell
> > > out of sync I would either issue a new pg_base_backup() or set the
> > > master to pg_start_backup() do an rsync and stop using
> > > pg_stop_backup(). If there is a way to avoid any of that, for
> example
> > > pause replication to hold all the wal files until the replicated
> slave
> > > comes back and then release them once the replicated slave is up.
> > >
> > > I apologize if this question has already been asked. I did some
> searching beforehand.
> >
> > See the manual and read up on the 2 GUCs; archive_command and
> wal_keep_segments.
> >
> > Thanks, i'll read into this some more.
> >
> >
> > wal_keep_segments lets you hold a configurable number of WAL segments
> > back and buy some more time till you have to resync the stand bys.
> >
> > Setting archive_command to '' or something like '/bin/false' lets you
> > delay archiving forever till you change them back again and/or fill
> > whatever file system pg_xlog writes to :-)
> >
> > So disabling the archive_command by setting it to and empty string or
> /bin/false will effectively pause log shipping? When I re-enable the
> archive command will it
> > continue where it left of when the archive_command was "disabled"?
> >
> >
> >
> > >
> > > Thanks,
> > > -Joseph Kregloh
> > >
> >
> > --
> > Jerry Sievers
> > Postgres DBA/Development Consulting
> > e: postgres.consult...@comcast.net
> > p: 312.241.7800
> >
>
> --
> Jerry Sievers
> e: jerry.siev...@comcast.net
> p: 312.241.7800
>

[GENERAL] Appended '+' in Column Value

[Rich Shepard]

  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 | Nematoda | |  |
 |  | Omnivore  |50 |  | StarvationCrk+| Owyhee
| ||  | |  |
 |  |   |   |  |   |
   2701 | STV-10  | 2013-07-10 | Nematoda | |  |
 |  | Omnivore  |36 |  | StarvationCrk | Owyhee

  I'd appreciate learning where that '+' originates and how to get rid of
it. A query to count the rows with the appendage returns zero:

select count(*) from benthos where stream = 'StarvationCrk';
 count 
---

   204

select count(*) from benthos where stream = 'StarvationCrk+';
 count 
---

 0

TIA,

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] ERROR: Problem running post install step

[Adrian Klaver]

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://www.pokertracker.com/

So could we get a specific definition of what installer you are using, 
as well as the installations steps taken?





--
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

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

[Karsten Hilbert]

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 I've not seen this before.
> 
>   Example:
> 
>2220 | STV | 2012-07-12 | Nematoda | |  |
>  |  | Omnivore  |50 |  | StarvationCrk+| 
> Owyhee
> | ||  | |  |
>  |  |   |   |  |   |
>2701 | STV-10  | 2013-07-10 | Nematoda | |  |
>  |  | Omnivore  |36 |  | StarvationCrk | 
> Owyhee
> 
>   I'd appreciate learning where that '+' originates

It's probably an indication of a wrapped column value inside
the display column bounded by |'s.

Try fiddling with \x and \pset.

Karsten
-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
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

[Ian Barwick]

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.
> 
>   Example:
> 
>2220 | STV | 2012-07-12 | Nematoda | |  |
>  |  | Omnivore  |50 |  | StarvationCrk+| 
> Owyhee
> | ||  | |  |
>  |  |   |   |  |   |
>2701 | STV-10  | 2013-07-10 | Nematoda | |  |
>  |  | Omnivore  |36 |  | StarvationCrk | 
> Owyhee
> 
>   I'd appreciate learning where that '+' originates and how to get rid of
> it. A query to count the rows with the appendage returns zero:
> 
> select count(*) from benthos where stream = 'StarvationCrk';
>  count ---
>204
> 
> select count(*) from benthos where stream = 'StarvationCrk+';
>  count ---
>  0

You have a newline character. Try:

  select count(*) from benthos where stream = E'StarvationCrk\n';

Regards

Ian Barwick

-- 
 Ian Barwick   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] Appended '+' in Column Value

[Adrian Klaver]

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 before.


Is this only in psql?

If so I would suspect the table formatting code.

What happens if you do \x and then look at the records?



   Example:

2220 | STV | 2012-07-12 | Nematoda | |  |
  |  | Omnivore  |50 |  |
StarvationCrk+| Owyhee
 | ||  | |  |
  |  |   |   |  |   |
2701 | STV-10  | 2013-07-10 | Nematoda | |  |
  |  | Omnivore  |36 |  | StarvationCrk
| Owyhee

   I'd appreciate learning where that '+' originates and how to get rid of
it. A query to count the rows with the appendage returns zero:

select count(*) from benthos where stream = 'StarvationCrk';
  count ---
204

select count(*) from benthos where stream = 'StarvationCrk+';
  count ---
  0

TIA,

Rich







--
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

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

[Rich Shepard]

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 changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Rich Shepard]

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

[Adrian Klaver]

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 kept hitting Enter after 
each string(most of the time) or whatever program input the data added \n.




Thanks,

Rich





--
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

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

[Tom Lane]

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 string(most of the time) or whatever program input the data added \n.

BTW, see \pset (particularly the linestyle option) in the psql man page
for documentation of this behavior and the options for changing it.

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] ERROR: Problem running post install step

[Brodie S]

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 installed on the 
client machines. Not the server.

On Fri, Aug 22, 2014 at 3:53 PM, Adrian Klaver 
wrote:

> 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://www.pokertracker.com/
> So could we get a specific definition of what installer you are using, 
> as well as the installations steps taken?
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com

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

[Adrian Klaver]

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 postgres user?

If not does the install user have rights on the $DATA directory?



The databases will be used by poker tracker but they will be installed
on the client machines. Not the server.






--
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

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

[John R Pierce]

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 
relaxed ideas about data integrity and the proper ordering of writes.


--
john r pierce  37N 122W
somewhere on the middle of the left coast

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

[Brodie S]

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 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 postgres user?
> If not does the install user have rights on the $DATA directory?
>>
>> The databases will be used by poker tracker but they will be installed
>> on the client machines. Not the server.
>>
>>
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com

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

[Adrian Klaver]

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 Windows Postgres install help:(

Might want to take this up on the EDB forums:

http://forums.enterprisedb.com/forums/list.page



I did a chmod 777 on the data folder





--
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

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

[Brodie S]

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 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 Windows Postgres install help:(
>
> Might want to take this up on the EDB forums:
>
> http://forums.enterprisedb.com/forums/list.page
>
>
>
>> I did a chmod 777 on the data folder
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

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

[Adrian Klaver]

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.enterprisedb.com/posts/list/4000.page



Some searching found references to --enable_acledit 1 when installing to 
non-default paths. So maybe something like this:


32bit
postgresql-9.0.18-1.windows.exe --enable_acledit 1

64bit
postgresql-9.0.18-1.windows-x64.exe --enable_acledit 1

You may need to change the *.exe to match your exact situation.

--
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

Re: [GENERAL] Query planner question

[Soni M]

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(transmission_id)
> >
> > Change the query to include only FK still result the same:
> > explain select t.ticket_id ,
> > tb.transmission_id
> > from ticket t,
> > transmission_base tb
> > where t.latest_transmission_id = tb.transmission_id
> > and tb.parse_date > ('2014-07-31');
> > QUERY PLAN
> >
> --
> >  Hash Join  (cost=113928.06..2583606.96 rows=200338 width=8)
> >Hash Cond: (t.latest_transmission_id = tb.transmission_id)
> >->  Seq Scan on ticket t  (cost=0.00..1767767.26 rows=69990826
> width=8)
> >->  Hash  (cost=108923.38..108923.38 rows=400374 width=4)
> >  ->  Index Scan using transmission_base_by_parse_date on
> > transmission_base tb  (cost=0.00..108923.38 rows=400374 width=4)
> >Index Cond: (parse_date > '2014-07-31 00:00:00'::timestamp
> > without time zone)
> > (6 rows)
>
> Do you have an index on ticket (latest_transmission_id)?
>
> Yes, both t.latest_transmission_id and tb.transmission_id is indexed.

Indexes:
"transmission_base_pkey" PRIMARY KEY, btree (transmission_id) CLUSTER
Indexes:
"ticket_by_latest_transmission" btree (latest_transmission_id)



> --
> If you can't see the forest for the trees,
> Cut the trees and you'll see there is no forest.
>



-- 
Regards,

Soni Maula Harriz

Re: [GENERAL] Query planner question

[David G Johnston]

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
>> > (latest_transmission_id) REFERENCES transmission_base(transmission_id)
>> >
>> > Change the query to include only FK still result the same:
>> > explain select t.ticket_id ,
>> > tb.transmission_id
>> > from ticket t,
>> > transmission_base tb
>> > where t.latest_transmission_id = tb.transmission_id
>> > and tb.parse_date > ('2014-07-31');
>> > QUERY PLAN
>> >
>> --
>> >  Hash Join  (cost=113928.06..2583606.96 rows=200338 width=8)
>> >Hash Cond: (t.latest_transmission_id = tb.transmission_id)
>> >->  Seq Scan on ticket t  (cost=0.00..1767767.26 rows=69990826
>> width=8)
>> >->  Hash  (cost=108923.38..108923.38 rows=400374 width=4)
>> >  ->  Index Scan using transmission_base_by_parse_date on
>> > transmission_base tb  (cost=0.00..108923.38 rows=400374 width=4)
>> >Index Cond: (parse_date > '2014-07-31
>> 00:00:00'::timestamp
>> > without time zone)
>> > (6 rows)
>>
>> Do you have an index on ticket (latest_transmission_id)?
>>
>> Yes, both t.latest_transmission_id and tb.transmission_id is indexed.
> 
> Indexes:
> "transmission_base_pkey" PRIMARY KEY, btree (transmission_id) CLUSTER
> Indexes:
> "ticket_by_latest_transmission" btree (latest_transmission_id)

Can you provide EXPLAIN ANALYZE for all three queries?

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Query-planner-question-tp5815659p5815981.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] Query planner question

[Jeff Janes]

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-31');
>
> Execution plan: http://explain.depesz.com/s/YAak
>
> Indexes on ticket :
> "ticket_pkey" PRIMARY KEY, btree (ticket_id) CLUSTER
> "ticket_by_latest_transmission" btree (latest_transmission_id)
> "ticket_by_ticket_number" btree (ticket_number)
>

> This query only returns some portions of rows from ticket table.
> The question is, Why does postgres need to get all the rows from ticket
> table in order to complete this query?
> Can't postgres use indexes to get only needed rows on ticket table?
>


It can, but having separate indexes on latest_transmission_id and
ticket_number is not going to work.

You need a joint index on both columns.

 Cheers,

Jeff

>