Re: [GENERAL] Need some basic information
On Wed, Aug 14, 2013 at 11:10 AM, M Tarkeshwar Rao < m.tarkeshwar@ericsson.com> wrote: > Hi all, > > I am new in this group and need some help from your side. > > We have a mediation product which is initially using Oracle as database. > > Some of our customer interested to move Postgres 9.1. > > Our mediation product storing some configuration related information in > data base and some type of logging data. > > We are using Hibernate in Java to interact with Postgres 9.1. > > Can you please suggest some test cases or some issues which may hamper us? > > You can get more information with below Link: http://zetcode.com/db/postgresqljavatutorial/ Thanks & Regards Raghu Ram
[GENERAL] Need some basic information
Hi all, I am new in this group and need some help from your side. We have a mediation product which is initially using Oracle as database. Some of our customer interested to move Postgres 9.1. Our mediation product storing some configuration related information in data base and some type of logging data. We are using Hibernate in Java to interact with Postgres 9.1. Can you please suggest some test cases or some issues which may hamper us? Regards Tarkeshwar -- 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] Need some basic information
Hi all, I am new in this group and need some help from your side. We have a mediation product which is initially using Oracle as database. Some of our customer interested to move Postgres 9.1. Our mediation product storing some configuration related information in data base and some type of logging data. We are using Hibernate in Java to interact with Postgres 9.1. Can you please suggest some test cases or some issues which may hamper us? Regards Tarkeshwar -- 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] Question about using AggCheckCallContext in a C function
On Aug 12, 2013, at 12:47 PM, Tom Lane wrote: > Matt Solnit writes: >> 2. The function seems to work consistently when I do a SELECT >> SUM(mycol) without any GROUP BY. It's only when I add grouping that >> the failures happen. I'm not sure if this is a real clue or a red >> herring. > > That isn't enormously surprising, since the memory management for > the transition values is different in the two cases. > >> Finally, can you tell me what precisely happens when you call >> datumCopy() with ArrayType? If it's only returning a copy of >> the TOAST reference, then how is it safe for the transition function >> to modify the content? I'm probably *completely* misunderstanding >> how this works, so I would love to be enlightened :-). > > You're right, datumCopy() won't expand a TOAST reference. What does > expand it is PG_GETARG_ARRAYTYPE_P(). So if you have a case where the > system picks up a copy of an array input that happens to be toasted, > it's the GETARG step in the next invocation of the aggregate transition > function that expands the TOAST reference, and then after that you have an > in-memory copy that's safe to modify. Maybe you're missing that somehow? > The code fragment you showed looked okay but ... > > regards, tom lane I think I figured it out. The problem is this line: Datum *arrayData1, *arrayData2; Datum* was correct when I first started this journey, using deconstruct_array(), but is incorrect when accessing the array's content directly using ARR_DATA_PTR(). Changing these to int* fixes the problem, at least on all the systems I've tried so far. I've been wondering why the broken code worked without a GROUP BY, and I think it was just dumb luck. With no GROUP BY, I was only overrunning a single buffer, and maybe the effects were not immediately apparent. With GROUP BY, however, there's a buffer overrun for each group, and each one increases the chance of doing something catastrophic. Sincerely, Matt Solnit -- 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] What type of index do I need for this JOIN?
On 8/13/13, Craig Ringer wrote: > On 08/14/2013 06:05 AM, Robert James wrote: >> I'm doing a JOIN which is very slow: >> >> JOIN t ON t.f1 LIKE (q.f1 || '%') >> >> t1 has an INDEX on (f1, f2) which I thought would help for this. But >> Postgres seems to still use a (very slow) Nested Loop. What type of >> index would be appropriate for this? > > You'll need a text_pattern_ops index. > > "The optimizer can also use a B-tree index for queries involving the > pattern matching operators LIKE and ~ if the pattern is a constant and > is anchored to the beginning of the string — for example, col LIKE > 'foo%' or col ~ '^foo', but not col LIKE '%bar'. However, if your > database does not use the C locale you will need to create the index > with a special operator class to support indexing of pattern-matching > queries; see Section 11.9 below." I'm confused: What's the difference between col LIKE 'foo%' and col LIKE f1 || '%' ? Either way, it's anchored to the beginning of the string. And, being that there is a difference (ie the pattern needs to be constant): How will text_pattern_ops help? The only difference I see is that text_patterns_ops is locale sensitive, needed for locale other than C. -- 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] What type of index do I need for this JOIN?
On 08/14/2013 06:05 AM, Robert James wrote: > I'm doing a JOIN which is very slow: > > JOIN t ON t.f1 LIKE (q.f1 || '%') > > t1 has an INDEX on (f1, f2) which I thought would help for this. But > Postgres seems to still use a (very slow) Nested Loop. What type of > index would be appropriate for this? You'll need a text_pattern_ops index. http://www.postgresql.org/docs/current/static/indexes-opclass.html http://www.postgresql.org/docs/current/static/indexes-types.html "The optimizer can also use a B-tree index for queries involving the pattern matching operators LIKE and ~ if the pattern is a constant and is anchored to the beginning of the string — for example, col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'. However, if your database does not use the C locale you will need to create the index with a special operator class to support indexing of pattern-matching queries; see Section 11.9 below." -- Craig Ringer 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] MinGW compiled client library
On 08/14/2013 08:57 AM, John R Pierce wrote: > > no. GCC uses a totally different libc in its generated code. that alone > ensures its binaries are not directly comparable.linking code with > incompatible libc's is gonna result in some gnarly messes, imagine what > kind of ugly stuff could happen if you used one malloc and another free ? Windows code is required to defend against this and PostgreSQL is no exception. It's very common on Windows for different libraries to use different libcs. Horrible, but common. They deal with this by doing things like: * Always free()ing memory in the same module it was malloc()'d in; * Never passing file handles around, instead using wrapper functions; etc. libpq compiled with gcc should work in an MSVC-compiled executable so long as the port/ code for mingw is correct and there are no undiscovered portability bugs. In this case I'm wondering if we've got an issue with selection of socket flags. Michael, can you try some older versions and see if you can find when this problem first appeared? Does it only affect mingw-64, or is the 32-bit version affected too? -- Craig Ringer 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] MinGW compiled client library
On 8/13/2013 2:25 PM, Michael Cronenworth wrote: On 08/13/2013 01:40 PM, John R Pierce wrote: >They use Microsoft Visual C (I forget the exact version, but I believe most >recent versions are supported, including the "Express" versions). Then MinGW should be capable of producing the same binaries. I've tried early gcc 4.7 and the latest gcc 4.8, but neither produce anything that works. no. GCC uses a totally different libc in its generated code. that alone ensures its binaries are not directly comparable.linking code with incompatible libc's is gonna result in some gnarly messes, imagine what kind of ugly stuff could happen if you used one malloc and another free ? -- 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
Re: [GENERAL] MinGW compiled client library
On 8/13/2013 5:25 PM, Michael Cronenworth wrote: On 08/13/2013 01:40 PM, John R Pierce wrote: They use Microsoft Visual C (I forget the exact version, but I believe most recent versions are supported, including the "Express" versions). Then MinGW should be capable of producing the same binaries. I'm curious what led you to that conclusion. The MinGW site itself does not make such a claim. See http://www.mingw.org/wiki/MixingCompilers. -- Guy Rouillier -- 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] SORT and Merge Join via Index
On 14/08/13 12:02, Robert James wrote: I noticed that when I have an index on (a,b) of table t, and I do an SELECT * FROM t ORDER BY a ASC, it doesn't use the index. When I create a new index of only a, it does use the index. Why is that? And, more importantly, when I do a query involving a merge join of table t, which requires sorting table t, the planner does the sort manually using quicksort, not using the index. The time that step takes is identical to the ORDER BY without using the index. What do I need to do to have Postgres use the index for the merge join? (Postgres 8.3) Thanks! It might be that the RAM taken up by an index of (a,b) rather than (a) triggers the plan to reject it and/or the extra I/O to scan the extra disk blocks required by the index of (a,b)? I cringe when I used to gaily use indexes without any regard for these factors! :-( Cheers, Gavin
Re: [GENERAL] SORT and Merge Join via Index
On 8/13/13, Robert James wrote: > I noticed that when I have an index on (a,b) of table t, and I do an > SELECT * FROM t ORDER BY a ASC, it doesn't use the index. When I > create a new index of only a, it does use the index. Why is that? > > And, more importantly, when I do a query involving a merge join of > table t, which requires sorting table t, the planner does the sort > manually using quicksort, not using the index. The time that step > takes is identical to the ORDER BY without using the index. What do I > need to do to have Postgres use the index for the merge join? > > (Postgres 8.3) Interestingly enough, in the JOIN query, if I replace "t" with: (SELECT f1, f2 FROM t ORDER BY f1 ASC) AS t_ Postgres does use the index, getting the query done in half the time! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SORT and Merge Join via Index
I noticed that when I have an index on (a,b) of table t, and I do an SELECT * FROM t ORDER BY a ASC, it doesn't use the index. When I create a new index of only a, it does use the index. Why is that? And, more importantly, when I do a query involving a merge join of table t, which requires sorting table t, the planner does the sort manually using quicksort, not using the index. The time that step takes is identical to the ORDER BY without using the index. What do I need to do to have Postgres use the index for the merge join? (Postgres 8.3) Thanks! -- 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] Performance of ORDER BY RANDOM to select random rows?
On Sun, Aug 11, 2013 at 9:59 PM, Victor Hooi wrote: > Hmm, aha, so the ORDER BY RANDOM behaviour hasn't changed - just to confirm > - this means that Postgres will duplicate the table, add a new column, > generate random numbers for every record, then sort by that new column, > right? It doesn't duplicate the table, it sec scans it and uses top-N sort if we use limit, and memory or disc sort depending on the data size if we don't use limit. > I've just read the above anecdotally on the internet, but I'm curious if the > actual implementation is documented somewhere officially apart from the > source? Running the query through EXPLAIN didn't seem to tell me much > additional information. I can not say about official docs, but you will find a good sorting explanation here http://www.depesz.com/2013/05/09/explaining-the-unexplainable-part-3/ > @Sergey - Thanks for the tip about using WITH RECURSIVE. I'm actually doing > something similar in my application code in Django - basically take the max > id, then generate a random integer between 0 and max id. However, it is > dependent on how evenly distributed the record IDs are - in our case, if we > delete a large number of records, it might affect things. You can try to look at pg_stats.histogram_bounds to work the issue around, however it is just my assumption, I have newer tried it. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] What type of index do I need for this JOIN?
I'm doing a JOIN which is very slow: JOIN t ON t.f1 LIKE (q.f1 || '%') t1 has an INDEX on (f1, f2) which I thought would help for this. But Postgres seems to still use a (very slow) Nested Loop. What type of index would be appropriate for this? (My goal is to join on a substring starting from the first character) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Materializing a view by hand
I have a view which is very slow to computer, but doesn't change often. I'd like to materialize it. I thought I'd do a simple poor man's materialize by: 1) ALTER VIEW myview RENAME to _myview 2) SELECT * INTO myview FROM _myview The only problem is that all my other views, which are dependent on myview, automatically rename to _myview. That would normally be very helpful but is exactly the opposite of what I want! Is there a work around? I'm running Postgres 8.3 - upgrading is a possibility but difficult. -- 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] View permission error after upgrading from 8.4 -> 9.2
Brian Hirt writes: > None of the relations used by vcredit_info are views. Â They are all > tables. Â Oddly, I dropped the view and recreated it and the problem went > away. Â Earlier I was just using create or replace view and the problem > persisted. Hm. I think that CREATE OR REPLACE VIEW may not change the view owner, so the behavior would be explainable if the view's original owner did not have permissions to read that table. 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] MinGW compiled client library
On 08/13/2013 01:40 PM, John R Pierce wrote: > They use Microsoft Visual C (I forget the exact version, but I believe most > recent versions are supported, including the "Express" versions). Then MinGW should be capable of producing the same binaries. I've tried early gcc 4.7 and the latest gcc 4.8, but neither produce anything that works. -- 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] View permission error after upgrading from 8.4 -> 9.2
None of the relations used by vcredit_info are views. They are all tables. Oddly, I dropped the view and recreated it and the problem went away. Earlier I was just using create or replace view and the problem persisted. The schema was created by using pg_restore from an 8.4 custom dump. I can do another pg_restore and see if the problem is reproducible if you want. On Aug 13, 2013, at 12:03 PM, Tom Lane wrote: Brian Hirt writes: I'm upgrading our database from 8.4 to 9.2 and I've run across a view that is no longer working. � When selecting from the view, I get a permission denied error on one of the referenced tables. � However, I can run the view's query directly without problems and I have read access to all the tables the view accesses. Permissions checks for tables referenced by a view are done as the view's owner. I'm suspicious that one of the relations used in your view vcredit_info is itself a view that references developer_title, and is owned by some other user with less privilege than you. 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] Please help
On 08/13/2013 10:29 AM, Thodi Viola wrote: The error mentioned is orcan you see the attachment. Well either no password or the wrong password supplied for the postgres user. I do not use StackBuilder, so someone else will need to help out with the proper place/method to supply the password. Thodi Viola -- Adrian Klaver adrian.kla...@gmail.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] MinGW compiled client library
On 8/13/2013 10:35 AM, Michael Cronenworth wrote: What environment does EnterpriseDB use to create their Windows binaries? They use Microsoft Visual C (I forget the exact version, but I believe most recent versions are supported, including the "Express" versions). -- 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
Re: [GENERAL] View permission error after upgrading from 8.4 -> 9.2
Brian Hirt writes: > I'm upgrading our database from 8.4 to 9.2 and I've run across a view that is > no longer working. When selecting from the view, I get a permission denied > error on one of the referenced tables. However, I can run the view's query > directly without problems and I have read access to all the tables the view > accesses. Permissions checks for tables referenced by a view are done as the view's owner. I'm suspicious that one of the relations used in your view vcredit_info is itself a view that references developer_title, and is owned by some other user with less privilege than you. 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] Please help
The error mentioned is orcan you see the attachment. Thodi Viola 2013/8/13 Adrian Klaver > On 08/13/2013 08:57 AM, Thodi Viola wrote: > >> Dear Srs >> >> Could you please help me to fix the error shown in attachment, i'm use >> windows 7. >> >> >> > What is in the error log mentioned in the pop up? > > > -- > Adrian Klaver > adrian.kla...@gmail.com > -- --- Melhores cumprimentos / Best regards *Thodi Viola* Geólogo de Pesquisa / Exploration Geologist Membro / Member of: AGMM ** *Contactos:* +258 821 217 160 / 845 237 792 *Skype*: thodi.viola Maputo / Tete - Moçambique "descansar não significa parar de trabalhar mas mudar de actividade...tente e veja o resultado / rest does not mean stop working but changing activity ... try and see the result" createdb: could not connect to database template1: FATAL: password authentication failed for user "postgres" -- 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] Difference between terminate/cancel?
Le 2013-08-13 à 13:25, Kevin Grittner a écrit : > François Beausoleil wrote: > >> What is the difference between both? > >> I had some processes stuck in IDLE in transaction after a machine >> died (through pgbouncer), and cancel did not close the >> connections, while terminate did kill the transactions. > >> The docs at >> http://www.postgresql.org/docs/9.1/static/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL-TABLE >> [...] > > According to the docs you cite: > > pg_cancel_backend(): Cancel a backend's current query > pg_terminate_backend(): Terminate a backend > > A connection which is idle or idle in transaction does not have a > current query to cancel, but it has a backend process which can be > terminated. Oh, interesting! Now I understand better. Extracted like that, it's obvious what the difference is. Thanks! François smime.p7s Description: S/MIME cryptographic signature
[GENERAL] MinGW compiled client library
Hello, When the client library (version 9.2.x) is compiled with a MinGW-w64 environment the resulting libpq.dll will not function. This has been reported previously with two bug reports, which have gone untouched. Bug 8151: http://www.postgresql.org/message-id/e1ubelm-0007nk...@wrigleys.postgresql.org Bug 8162: http://www.postgresql.org/message-id/e1uclpd-l4...@wrigleys.postgresql.org I have tried compiling with every option enabled and every option disabled. Does anyone have any pointers or would anyone be willing to help solve this issue? What environment does EnterpriseDB use to create their Windows binaries? Thanks, Michael -- 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] Difference between terminate/cancel?
François Beausoleil wrote: > What is the difference between both? > I had some processes stuck in IDLE in transaction after a machine > died (through pgbouncer), and cancel did not close the > connections, while terminate did kill the transactions. > The docs at > http://www.postgresql.org/docs/9.1/static/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL-TABLE > [...] According to the docs you cite: pg_cancel_backend(): Cancel a backend's current query pg_terminate_backend(): Terminate a backend A connection which is idle or idle in transaction does not have a current query to cancel, but it has a backend process which can be terminated. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Difference between terminate/cancel?
Hi! What is the difference between both? cancel sends QUIT while terminate sends TERM, but what effect does this have on the backends, shared memory, etc? I had some processes stuck in IDLE in transaction after a machine died (through pgbouncer), and cancel did not close the connections, while terminate did kill the transactions. The docs at http://www.postgresql.org/docs/9.1/static/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL-TABLE do name the signals, but I can't find other documentation on the effects of TERM/QUIT (I'm on 9.1, but devel says the same thing). Are there pointers in the docs on the differences? Thanks! François smime.p7s Description: S/MIME cryptographic signature
[GENERAL] View permission error after upgrading from 8.4 -> 9.2
I'm upgrading our database from 8.4 to 9.2 and I've run across a view that is no longer working. When selecting from the view, I get a permission denied error on one of the referenced tables. However, I can run the view's query directly without problems and I have read access to all the tables the view accesses. I'm a bit confused as to what's causing this. I'm logged in as the postgres superuser and don't have any permissions set up for the views/tables in question. Any help would be appreciated. basement_QA=# create or replace view vcredit_info as SELECT game_credit.developer_id, credit_submission.game_id, credit_submission.platform_id, game_credit.game_credit_title_id, game_credit_title.developer_title_id, ( SELECT min(substr(gv.release_date::text, 1, 4)::integer) AS min FROM game_version gv WHERE gv.approved = 1 AND gv.game_id = credit_submission.game_id AND gv.release_type_id = 1 AND gv.platform_id = credit_submission.platform_id) AS first_year, ( SELECT max(substr(gv.release_date::text, 1, 4)::integer) AS max FROM game_version gv WHERE gv.approved = 1 AND gv.game_id = credit_submission.game_id AND gv.release_type_id = 1 AND gv.platform_id = credit_submission.platform_id) AS last_year, developer_title.credit_title_category_id FROM game_credit JOIN credit_submission USING (credit_submission_id) JOIN game_status USING (game_id, platform_id) JOIN game_credit_title USING (game_credit_title_id) JOIN developer_title USING (developer_title_id) WHERE game_status.approved = 1 AND credit_submission.approved = 1; CREATE VIEW basement_QA=# select count(*) from vcredit_info where game_id = 30997; ERROR: permission denied for relation developer_title basement_QA=# select count(*) from developer_title; count --- 224 (1 row) basement_QA=# select count(*) from (SELECT game_credit.developer_id, credit_submission.game_id, basement_QA(# credit_submission.platform_id, game_credit.game_credit_title_id, basement_QA(# game_credit_title.developer_title_id, basement_QA(# ( SELECT min(substr(gv.release_date::text, 1, 4)::integer) AS min basement_QA(# FROM game_version gv basement_QA(# WHERE gv.approved = 1 AND gv.game_id = credit_submission.game_id AND gv.release_type_id = 1 AND gv.platform_id = credit_submission.platform_id) AS first_year, basement_QA(# ( SELECT max(substr(gv.release_date::text, 1, 4)::integer) AS max basement_QA(# FROM game_version gv basement_QA(# WHERE gv.approved = 1 AND gv.game_id = credit_submission.game_id AND gv.release_type_id = 1 AND gv.platform_id = credit_submission.platform_id) AS last_year, basement_QA(# developer_title.credit_title_category_id basement_QA(# FROM game_credit basement_QA(# JOIN credit_submission USING (credit_submission_id) basement_QA(# JOIN game_status USING (game_id, platform_id) basement_QA(# JOIN game_credit_title USING (game_credit_title_id) basement_QA(# JOIN developer_title USING (developer_title_id) basement_QA(# WHERE game_status.approved = 1 AND credit_submission.approved = 1) as myview where myview.game_id = 30997; count --- 66 (1 row) basement_QA=# select CURRENT_USER; current_user -- postgres (1 row) basement_QA=# \dp vcredit_info Access privileges Schema | Name | Type | Access privileges | Column access privileges +--+--+---+-- public | vcredit_info | view | | (1 row) basement_QA=# \dp developer_title; Access privileges Schema | Name | Type | Access privileges | Column access privileges +-+---+---+-- public | developer_title | table | | (1 row)
Re: [GENERAL] Convincing the query planner to play nice
On Sat, Aug 10, 2013 at 5:24 PM, Tim Kane wrote: > > Again, just thinking out loud here.. In a scenario where specific > clustering isn't an option... > I wonder if the query planner should consider the physical > distribution/ordering of values on disk, and use that as a factor when > applying the random_page_cost in the QEP's? It does do that, based on the "correlation" column in pg_stats. However, because your original random_page_cost is already very close to seq_page_cost, this adjustment doesn't have a huge effect in your case. I don't know how much of an effect it would have even then, because of the range overlap issue that Tom mentions. Cheers, Jeff -- 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] Please help
On 08/13/2013 08:57 AM, Thodi Viola wrote: Dear Srs Could you please help me to fix the error shown in attachment, i'm use windows 7. What is in the error log mentioned in the pop up? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Fwd: Error Stack builder
-- Forwarded message -- From: Selena Deckelmann Date: 2013/8/13 Subject: Re: Error Stack builder To: Thodi Viola Please contact pgsql-general@postgresql.org for help. -selena 2013/8/13 Thodi Viola > > Dear Sr. > > Could you please help me to fix the error in attachment. I'm trying to > install the stack builder application in windows 7. > > --- > Melhores cumprimentos / Best regards > > *Thodi Viola* > Geólogo de Pesquisa / Exploration Geologist > Membro / Member of: AGMM > ** > *Contactos:* > +258 821 217 160 / 845 237 792 > *Skype*: thodi.viola > Maputo / Tete - Moçambique > "descansar não significa parar de trabalhar mas mudar de > actividade...tente e veja o resultado / rest does not mean stop working but > changing activity ... try and see the result" > -- http://postgresql.org Me - http://chesnok.com -- --- Melhores cumprimentos / Best regards *Thodi Viola* Geólogo de Pesquisa / Exploration Geologist Membro / Member of: AGMM ** *Contactos:* +258 821 217 160 / 845 237 792 *Skype*: thodi.viola Maputo / Tete - Moçambique "descansar não significa parar de trabalhar mas mudar de actividade...tente e veja o resultado / rest does not mean stop working but changing activity ... try and see the result"
Re: [GENERAL] Convincing the query planner to play nice
On Sat, Aug 10, 2013 at 10:32 AM, Timothy Kane wrote: > > Hi all, > > I seem to be having some grief with the 9.1.9 query planner favouring an > index scan + merge join, over a sequential scan + hash join. > Logically I would have considered the index+merge to be faster, as suggested > by the explain output - but in practice, it is in fact slower by orders of > magnitude. > > In my timings below, I've tried to reduce the impact of any OS or > shared_buffer level caching (restarting postgres, and flushing OS cache > between queries-). Are you sure that that is the right thing to do? It seems unlikely that your production server is constantly executing your query from a cold start. Why test it that way? > > I've provided my settings as shown: > > > =# show seq_page_cost; > seq_page_cost > --- > 1 > (1 row) > > Time: 0.355 ms > =# show random_page_cost; > random_page_cost > -- > 2.2 > (1 row) Given that you are testing your query from a cold start (and assuming against odds that that is the correct thing to do), 2.2 is probably a factor of 20 too small for this setting. Cheers, Jeff -- 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] How to avoid Force Autovacuum
Hi Team,
Today also we faced issue in autovacuum.. Is there any workaround for this
instead of upgrading,, If yes means can you please give me tuning
parameters..
> log_autovacuum_min_duration = 0
That is good for debugging. But what are you seeing in the log as the
result of this?
There is nothing logged during autovacuum
This is the Pid in Pg_stat_activity
postgres=# select * from pg_stat_activity where pid=25769;
datid | datname | pid | usesysid | usename | application_name |
client_addr | client_hostname | client_port | backend_start
| xact_start | query_start | state_change | waiting |
state | query
---+-+---+--+--+--+-+-+-+---+---+-+--+-+---+---
16408 | db1 | 25769 | 10 | postgres | |
| | | 2013-08-13 04:00:14.767093-07 |
2013-08-13 04:00:14.765484-07 | | | f |
|
(1 row)
This is the top command:
postgres 25769 30705 93 03:54 ?00:01:45 postgres: autovacuum worker
process db1
postgres 24680 30705 84 03:55 ?00:00:33 postgres: autovacuum worker
process db2
postgres 24692 30705 79 03:55 ?00:00:26 postgres: autovacuum worker
process db3
On Sat, Aug 10, 2013 at 12:23 AM, Jeff Janes wrote:
> On Fri, Aug 9, 2013 at 9:06 AM, Kevin Grittner wrote:
> > Vishalakshi Navaneethakrishnan wrote:
> >
> >> select * from pg_database where datname = 'template0';
> >> -[ RECORD 1 ]-+
> >> datname | template0
> >> datdba| 10
> >> encoding | 6
> >> datcollate| en_US.UTF-8
> >> datctype | en_US.UTF-8
> >> datistemplate | t
> >> datallowconn | f
> >> datconnlimit | -1
> >> datlastsysoid | 12865
> >> datfrozenxid | 2025732249
> >> dattablespace | 1663
> >> datacl| {=c/postgres,postgres=CTc/postgres}
> >>
> >>
> >> select * from pg_stat_database where datname = 'template0';
> >> -[ RECORD 1 ]--+--
> >> datid | 12865
> >> datname| template0
> >> numbackends| 0
> >> xact_commit| 320390
> >> xact_rollback | 7
> >> blks_read | 3797
> >> blks_hit | 9458783
> >> tup_returned | 105872028
> >> tup_fetched| 1771782
> >> tup_inserted | 10
> >> tup_updated| 457
> >> tup_deleted| 10
> >> conflicts | 0
> >> temp_files | 0
> >> temp_bytes | 0
> >> deadlocks | 0
> >> blk_read_time | 0
> >> blk_write_time | 0
> >> stats_reset| 2013-04-19 19:22:39.013056-07
> >
> > Well, that's why template0 is getting vacuumed. At some point
> > someone must have set it to allow connections; otherwise you would
> > have zero for commits, rollbacks, and all those block and tuple
> > counts.
>
> Non-zero values are normal. There is no mechanism to prevent
> template0 from getting vacuumed. template0 will get vacuumed once
> every autovacuum_freeze_max_age even if no one has ever connected to
> it, and that vacuum will cause block reads and writes to happen. (But
> I'm not sure why it would contribute xact_rollback or tup_updated, and
> the tup_returned seems awfully high to be due to only anti-wrap-around
> vacs.)
>
> Cheers,
>
> Jeff
>
--
Best Regards,
Vishalakshi.N
