[GENERAL] SELECT 'NOW()' - OK, SELECT 'CLOCK_TIMESTAMP()' - ERROR
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
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 | 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
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 (~ [:/-]) tests 'now-' 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
Hi 2014-08-22 9:05 GMT+02:00 David G Johnston david.g.johns...@gmail.com: 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 (~ [:/-]) tests 'now-' 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
On 21 August 2014 11:36, Patrick Dung patrick_...@yahoo.com.hk 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
On 20/08/2014 23:27, Julien Rouhaud wrote: On Wed, Aug 20, 2014 at 6:46 PM, Thomas Kellerer spam_ea...@gmx.net mailto:spam_ea...@gmx.net 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
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 spam_ea...@gmx.net mailto:spam_ea...@gmx.net 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
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
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
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 rameshparnandit...@gmail.com 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 raghuchenn...@gmail.com wrote: On Wed, Aug 20, 2014 at 10:08 PM, Raymond O'Donnell r...@iol.ie 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
On 22 August 2014 14:26, Soni M diptat...@gmail.com 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
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
On Fri, Aug 22, 2014 at 6:15 AM, Tommy Duek tad...@gmail.com 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
Tommy Duek tad...@gmail.com writes: Im working on a project now that uses the postgres_fdw extensively. Do you know if this will be fixed in 9.4? I figure its 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
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?
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 = value limit 1 for update; update table set ... where pkey = the same_value; 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?
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 = value limit 1 for update; update table set ... where pkey = the same_value; 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?
On Fri, Aug 22, 2014 at 9:29 AM, hubert depesz lubaczewski dep...@gmail.com 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 = value limit 1 for update; update table set ... where pkey = the same_value; 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?
On Fri, Aug 22, 2014 at 6:45 PM, Adrian Klaver 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?! 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?
On 08/22/2014 10:15 AM, hubert depesz lubaczewski wrote: On Fri, Aug 22, 2014 at 6:45 PM, Adrian Klaver adrian.kla...@aklaver.com 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?
On 8/22/2014 9:29 AM, hubert depesz lubaczewski wrote: select * from table where pkey = value 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
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?
On Fri, Aug 22, 2014 at 7:29 PM, John R Pierce pie...@hogranch.com wrote: On 8/22/2014 9:29 AM, hubert depesz lubaczewski wrote: select * from table where pkey = value 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?
On Fri, Aug 22, 2014 at 7:20 PM, Adrian Klaver 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. depesz
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 adrian.kla...@aklaver.com 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?
hubert depesz lubaczewski dep...@gmail.com writes: On Fri, Aug 22, 2014 at 6:45 PM, Adrian Klaver 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?! 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?
On Fri, Aug 22, 2014 at 7:43 PM, Adrian Klaver 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. depesz
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 t...@sss.pgh.pa.us 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?
On 08/22/2014 10:50 AM, hubert depesz lubaczewski wrote: On Fri, Aug 22, 2014 at 7:43 PM, Adrian Klaver adrian.kla...@aklaver.com 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?
On Fri, Aug 22, 2014 at 7:54 PM, Adrian Klaver 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? depesz
Re: [GENERAL] Restart replicated slave procedure
Joseph Kregloh jkreg...@sproutloud.com 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?
On 08/22/2014 11:14 AM, hubert depesz lubaczewski wrote: On Fri, Aug 22, 2014 at 7:54 PM, Adrian Klaver adrian.kla...@aklaver.com 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
On Fri, Aug 22, 2014 at 2:21 PM, Jerry Sievers gsiever...@comcast.net wrote: Joseph Kregloh jkreg...@sproutloud.com 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?
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 = value limit 1 for update; update table set ... where pkey = the same_value; 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
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 br...@momjian.ushttp://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
I'm using the installer On Tue, Aug 19, 2014 at 3:53 PM, Adrian Klaver adrian.kla...@aklaver.com 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
On Fri, Aug 22, 2014 at 3:47 PM, Jerry Sievers jerry.siev...@comcast.net 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 jkreg...@sproutloud.com writes: On Fri, Aug 22, 2014 at 2:21 PM, Jerry Sievers gsiever...@comcast.net wrote: Joseph Kregloh jkreg...@sproutloud.com 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
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
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
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
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
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
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
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 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
Adrian Klaver adrian.kla...@aklaver.com 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
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 adrian.kla...@aklaver.com 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
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
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
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 adrian.kla...@aklaver.com 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
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
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 adrian.kla...@aklaver.com 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
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
On Fri, Aug 22, 2014 at 9:10 PM, Alban Hertroys haram...@gmail.com wrote:
On 22 August 2014 14:26, Soni M diptat...@gmail.com 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
Soni M wrote
On Fri, Aug 22, 2014 at 9:10 PM, Alban Hertroys lt;
haramrae@
gt; wrote:
On 22 August 2014 14:26, Soni M lt;
diptatapa@
gt; 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
