[GENERAL] PG 9.1.1 - availability of xslt_process()
Hi, I'm using the compiled version of PG 9.1.1 on win32 . try-ing to call the xslt_process(text,text) (two parameter version) PG say me it is not available. In the docs are say that it is available only if compiled with the libxslt lib. I see that dll is available in the lib folder. So I guess it should be available. To verify if it is available I search that function in the list of functions with pg-admin. But it is not available.Neither the three parameter version of xslt_process() is available. Perhaps is need to run some script to have it available ? Thx, -- - Andrea Peri . . . . . . . . . qwerty àèìòù -
Re: [GENERAL] PG 9.1.1 - availability of xslt_process()
On 10/26/11 11:13 PM, Andrea Peri wrote: Hi, I'm using the compiled version of PG 9.1.1 on win32 . try-ing to call the xslt_process(text,text) (two parameter version) PG say me it is not available. In the docs are say that it is available only if compiled with the libxslt lib. I see that dll is available in the lib folder. So I guess it should be available. To verify if it is available I search that function in the list of functions with pg-admin. But it is not available.Neither the three parameter version of xslt_process() is available. Perhaps is need to run some script to have it available ? isn't that part of the deprecated XML2 contributed module? wild guess says you need to install the contrib module with something like... CREATE EXTENSION xml2; but, as a deprecated module, XML2 has been on the 'remove soon' list since 8.4. Instead,, you should use the SQL/XML standard compliant functions built into postgres, -- john r pierceN 37, W 122 santa cruz ca mid-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] PG 9.1.1 - availability of xslt_process()
On Oct 27, 2011, at 11:43 AM, Andrea Peri wrote: Hi, I'm using the compiled version of PG 9.1.1 on win32 . try-ing to call the xslt_process(text,text) (two parameter version) PG say me it is not available. In the docs are say that it is available only if compiled with the libxslt lib. I see that dll is available in the lib folder. So I guess it should be available. To verify if it is available I search that function in the list of functions with pg-admin. But it is not available.Neither the three parameter version of xslt_process() is available. Perhaps is need to run some script to have it available ? xslt_process function is part of xml2 contrib module. Execute following for xml2 CREATE EXTENSION xml2; Thanks Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company Blog: http://vibhork.blogspot.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] specifying multiple ldapserver in pg_hba.conf
On Wed, Oct 26, 2011 at 23:00, Darin Perusich darin.perus...@ctg.com wrote: Are you able to specify multiple ldapservers in pg_hba.conf and if so what is the format? I'd like to be able to build some redundancy incase one of the ldap servers goes down. This is unfortunately currently not possible. To do this, you need to set up some IP level redundancy for your LDAP. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Can someone help explain what's going on from the attached logs?
On Wed, Oct 26, 2011 at 6:41 PM, Chris Redekop ch...@replicon.com wrote: Caveat #2 applies here http://developer.postgresql.org/pgdocs/postgres/hot-standby.html#HOT-STANDBY-CAVEATS The consistent state is delayed until your long running transactions end, which is workload dependent but transient. I'm not quite sure how this correlates to what I'm seeing in 9.1.1. When attempting to start a hotstandby while the primary is under load it seems to get stuck in that 'starting up' mode even when there are no open transactions. If I get it into this state, and then remove all the load from the primary it still will not finish starting up. If I select from pg_stat_activity on the primary it shows a couple connections, but they all have null 'xact_start's and IDLE 'current_query's. Even if I then kill all the connections to the primary (via pg_terminate_backend) the hotstandby still will not finish starting up. I would assume there can't be any transactions in progress if there are no connections to the primary. Attempting to restart the hotstandby when in this state produces the same result. If there is a transaction in progress for the duration of the backup (or something like that) can it cause it to get into this state? There's nothing in the log you've shown to indicate any problems. Yes, when that caveat applies we may wait for some time to find a good starting point. That could be anywhere from seconds to hours, depending upon the exact load on the master, but shouldn't be any longer than your longest running write transaction executing at that time. -- Simon Riggs 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] GIN : Working with term positions
2011/10/26 Yoann Moreau yoann.mor...@univ-avignon.fr:
On 21/10/11 12:23, Yoann Moreau wrote:
Hello,
I'm using a GIN index for a text column on a big table. I use it to rank
the rows, but I also need to get the term positions for each document of a
subset of documents. I assume these positions are stored in the index,
because doc says positions can be used for cover density ranking and because
to_tsvector function gives them :
select * from to_tsvector('I get lexemes and I get term positions.');
to_tsvector
'get':2,6 'lexem':3 'posit':8 'term':7
I can get the term positions with to_tsvector but only by parsing the
result string, is there any more handy way ? Something like :
select * from term_and_positions('I get lexemes and I get term
positions.');
term | positions
-+---
'get' | {2,6}
'lexem' | {3}
Then, from the term positions, I need to get the character offset of these
term positions. I assume it is NOT stored in the GIN index. By character
offset I mean the character count from string begining to the term. For the
previous example it would be : 'get' -- {2,20}.
I thought about using ts_headline to return the whole text with term
tagged and then parse it to compute the character offsets from the tags. But
this function is very slow, seems like it does not use the GIN index at all.
And I suppose it can't because there is no way to know from a term position
where its substring is in the text.
Now I think the only solution is to make my own C function parsing the
text like to_tsvector does and counting terms AND characters read from the
begining of the text to match them. I got a look on the code, and it does
not seems easy to do because characters offset or string length are never
used by the parsetext function (ts_parse.c). If you have any other
suggestion, would love to hear that !
Regards, Yoann Moreau
Hello again, I'm sorry my need is actually a bit different than what I have
asked. I need to get the term positions using the GIN index, when I query my
text column, i.e. for a given term.
For example for 2 rows of a 'docs' table with a text column 'text' :
'I get lexemes and I get term positions.'
'Did you get the positions ?'
I'd need a function like this :
select term_positions(text, 'get') from docs;
id_doc | positions
-+---
1 | {2,6}
2 | {3}
I know it can't be as simple as this, because the query would first need to
be filtered with a WHERE using a tsquery and this can't be done in the
function called like in my example. I suppose such a feature does not exist,
but is there any way to get the positions of the matching terms when
querying a GIN index ?
The only possible way I imagine right now is to firstly filter the rows with
to_tsvector(text) @@ to_tsquery('get') and then call to_tsvector(text)
for the n highest ranked rows, parsing the string returned by the function
to find the term and its positions. But would be way more efficient to get
them directly at the first call when matching the terms with @@ operator. I
know it would be impossible if the query contain more than 1 term because it
can't return 2 arrays of position in one row (i.e. for one document), but
for now I'm trying to do this for 1 query term.
Any help or advice would be welcome !
By the way, I have done the C function computing the character offset of a
given term position for a text column. It's not done in a good way, but it's
more a topic for hackers postgresql list.
Don't forget when you success that word positions are affected by the
word removed by stop-words.
--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation
--
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] pglesslog for Postgres 9.1.1
Hi, I'm sorry I'm not good in C, anyone can help to put a patch or release a new version for that? Regards Louis From: Tom Lane t...@sss.pgh.pa.us To: mailtolouis2020-postg...@yahoo.com mailtolouis2020-postg...@yahoo.com Cc: Postgres pgsql-general@postgresql.org Sent: Wednesday, October 26, 2011 3:42 PM Subject: Re: [GENERAL] pglesslog for Postgres 9.1.1 mailtolouis2020-postg...@yahoo.com mailtolouis2020-postg...@yahoo.com writes: remove.c:182: error: ‘XLOG_GIN_INSERT’ undeclared (first use in this function) remove.c:182: error: (Each undeclared identifier is reported only once remove.c:182: error: for each function it appears in.) remove.c:184: error: ‘XLOG_GIN_VACUUM_PAGE’ undeclared (first use in this function) remove.c:186: error: ‘XLOG_GIN_DELETE_PAGE’ undeclared (first use in this function) That stuff got moved to gin_private.h in 9.1 ... regards, tom lane
Re: [GENERAL] Saving score of 3 players into a table
Thank you Michal and others - On Wed, Oct 26, 2011 at 11:11 PM, Michael Glaesemann g...@seespotcode.net wrote: Get games for a particular user: SELECT g.gid, g.rounds, g.finished FROM pref_games g JOIN pref_scores u USING (gid) WHERE u.id = :id; Now, add the participants for those games SELECT g.gid, g.rounds, g.finished, p.id, p.money, p.quit FROM pref_games g JOIN pref_scores u USING (gid) JOIN pref_scores p USING (gid) WHERE u.id = :id; I don't know what kind of JOIN that is (above) - but it works well: # SELECT g.gid, g.rounds, g.finished, p.id, p.money, p.quit FROM pref_games g JOIN pref_scores u USING (gid) JOIN pref_scores p USING (gid) WHERE u.id = 'DE9411'; gid | rounds | finished | id | money | quit --++++---+-- 43 | 12 | 2011-10-26 14:57:54.045975 | OK510649006288 | -240 | f 43 | 12 | 2011-10-26 14:57:54.045975 | DE9411 |64 | f 43 | 12 | 2011-10-26 14:57:54.045975 | OK355993104857 | 176 | f 159 | 19 | 2011-10-26 15:55:54.650444 | DE9396 |70 | f 159 | 19 | 2011-10-26 15:55:54.650444 | DE9411 | -110 | f 159 | 19 | 2011-10-26 15:55:54.650444 | OK5409550866 |42 | f 224 | 16 | 2011-10-26 16:27:20.996753 | DE9396 | 4 | f 224 | 16 | 2011-10-26 16:27:20.996753 | DE9411 |66 | f 224 | 16 | 2011-10-26 16:27:20.996753 | OK5409550866 | -70 | f 297 | 20 | 2011-10-26 17:05:53.514124 | OK486555355432 | -114 | f 297 | 20 | 2011-10-26 17:05:53.514124 | DE9411 | -36 | f 297 | 20 | 2011-10-26 17:05:53.514124 | OK5409550866 | 148 | f 385 | 20 | 2011-10-26 17:43:44.473597 | OK486555355432 | 245 | f 385 | 20 | 2011-10-26 17:43:44.473597 | DE9411 |29 | f 385 | 20 | 2011-10-26 17:43:44.473597 | OK5409550866 | -275 | f 479 | 19 | 2011-10-26 18:26:05.00712 | OK486555355432 |30 | f 479 | 19 | 2011-10-26 18:26:05.00712 | DE9411 | -40 | f 479 | 19 | 2011-10-26 18:26:05.00712 | OK5409550866 | 8 | f but now I'm lost even more - how to JOIN this with the pref_users table containing first_name, city for each player: # select first_name, female, avatar, city from pref_users where id = 'DE9411'; first_name | female | avatar| city ++-+-- GRAF63 | f | picture-9411-1299771547.jpg | ALCORCON I'm trying: # SELECT g.gid, g.rounds, g.finished, p.id, p.money, p.quit, i.first_name, i.avatar FROM pref_games g JOIN pref_scores u USING (gid) JOIN pref_scores p USING (gid) JOIN pref_users i USING (id) WHERE u.id = 'DE9411'; ERROR: common column name id appears more than once in left table Another try: # SELECT g.gid, g.rounds, g.finished, p.id, p.money, p.quit, i.first_name, i.avatar FROM pref_games g, pref_users i JOIN pref_scores u USING (gid) JOIN pref_scores p USING (gid) WHERE u.id = 'DE9411' and p.id=i.id; ERROR: column gid specified in USING clause does not exist in left table Regards Alex -- 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] Saving score of 3 players into a table
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alexander Farber Sent: Thursday, October 27, 2011 7:21 AM Cc: pgsql-general Subject: Re: [GENERAL] Saving score of 3 players into a table Thank you Michal and others - On Wed, Oct 26, 2011 at 11:11 PM, Michael Glaesemann g...@seespotcode.net wrote: Get games for a particular user: SELECT g.gid, g.rounds, g.finished FROM pref_games g JOIN pref_scores u USING (gid) WHERE u.id = :id; Now, add the participants for those games SELECT g.gid, g.rounds, g.finished, p.id, p.money, p.quit FROM pref_games g JOIN pref_scores u USING (gid) JOIN pref_scores p USING (gid) WHERE u.id = :id; I don't know what kind of JOIN that is (above) - but it works well: # SELECT g.gid, g.rounds, g.finished, p.id, p.money, p.quit FROM pref_games g JOIN pref_scores u USING (gid) JOIN pref_scores p USING (gid) WHERE u.id = 'DE9411'; gid | rounds | finished | id | money | quit --++++-- -+-- 43 | 12 | 2011-10-26 14:57:54.045975 | OK510649006288 | -240 | f 43 | 12 | 2011-10-26 14:57:54.045975 | DE9411 |64 | f 43 | 12 | 2011-10-26 14:57:54.045975 | OK355993104857 | 176 | f 159 | 19 | 2011-10-26 15:55:54.650444 | DE9396 |70 | f 159 | 19 | 2011-10-26 15:55:54.650444 | DE9411 | -110 | f 159 | 19 | 2011-10-26 15:55:54.650444 | OK5409550866 |42 | f 224 | 16 | 2011-10-26 16:27:20.996753 | DE9396 | 4 | f 224 | 16 | 2011-10-26 16:27:20.996753 | DE9411 |66 | f 224 | 16 | 2011-10-26 16:27:20.996753 | OK5409550866 | -70 | f 297 | 20 | 2011-10-26 17:05:53.514124 | OK486555355432 | -114 | f 297 | 20 | 2011-10-26 17:05:53.514124 | DE9411 | -36 | f 297 | 20 | 2011-10-26 17:05:53.514124 | OK5409550866 | 148 | f 385 | 20 | 2011-10-26 17:43:44.473597 | OK486555355432 | 245 | f 385 | 20 | 2011-10-26 17:43:44.473597 | DE9411 |29 | f 385 | 20 | 2011-10-26 17:43:44.473597 | OK5409550866 | -275 | f 479 | 19 | 2011-10-26 18:26:05.00712 | OK486555355432 |30 | f 479 | 19 | 2011-10-26 18:26:05.00712 | DE9411 | -40 | f 479 | 19 | 2011-10-26 18:26:05.00712 | OK5409550866 | 8 | f but now I'm lost even more - how to JOIN this with the pref_users table containing first_name, city for each player: # select first_name, female, avatar, city from pref_users where id = 'DE9411'; first_name | female | avatar| city ++-+-- GRAF63 | f | picture-9411-1299771547.jpg | ALCORCON I'm trying: # SELECT g.gid, g.rounds, g.finished, p.id, p.money, p.quit, i.first_name, i.avatar FROM pref_games g JOIN pref_scores u USING (gid) JOIN pref_scores p USING (gid) JOIN pref_users i USING (id) WHERE u.id = 'DE9411'; ERROR: common column name id appears more than once in left table Another try: # SELECT g.gid, g.rounds, g.finished, p.id, p.money, p.quit, i.first_name, i.avatar FROM pref_games g, pref_users i JOIN pref_scores u USING (gid) JOIN pref_scores p USING (gid) WHERE u.id = 'DE9411' and p.id=i.id; ERROR: column gid specified in USING clause does not exist in left table Regards Alex -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general --- / Original Message - A) Read the documentation on JOINs until you understand what is going on in the first query (specifically, how do ON, NATURAL, USING relate to each other and to the JOIN itself) B) Avoid mixing JOIN syntax with multiple tables in the FROM clause C) If you are getting ambiguity in columns you either need to force a JOIN order (using parentheses) OR revert to using explicit ON () clauses Note, the column gid ... error above result because the planner is trying to join pref_users AND pref_scores but pref_users does not have a GID column to join on. It's as if you wrote: ( (pref_gamesJOIN(pref_users JOIN pref_scores) ) JOIN pref_scores ) David J. -- 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] Saving score of 3 players into a table
The PostgreSQL docs are unfortunately scarce on JOINs http://www.postgresql.org/docs/8.4/static/tutorial-join.html I've never seen a JOIN producing several rows instead of columns before Michael suggested it in this thread -- 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] Saving score of 3 players into a table
On Oct 27, 2011, at 7:21, Alexander Farber wrote: Thank you Michal and others - On Wed, Oct 26, 2011 at 11:11 PM, Michael Glaesemann g...@seespotcode.net wrote: Get games for a particular user: SELECT g.gid, g.rounds, g.finished FROM pref_games g JOIN pref_scores u USING (gid) WHERE u.id = :id; Now, add the participants for those games SELECT g.gid, g.rounds, g.finished, p.id, p.money, p.quit FROM pref_games g JOIN pref_scores u USING (gid) JOIN pref_scores p USING (gid) WHERE u.id = :id; I don't know what kind of JOIN that is (above) - but it works well: It's just a normal join. There's nothing special about it. but now I'm lost even more - how to JOIN this with the pref_users table containing first_name, city for each player: # select first_name, female, avatar, city from pref_users where id = 'DE9411'; first_name | female | avatar| city ++-+-- GRAF63 | f | picture-9411-1299771547.jpg | ALCORCON I'm trying: # SELECT g.gid, g.rounds, g.finished, p.id, p.money, p.quit, i.first_name, i.avatar FROM pref_games g JOIN pref_scores u USING (gid) JOIN pref_scores p USING (gid) JOIN pref_users i USING (id) WHERE u.id = 'DE9411'; ERROR: common column name id appears more than once in left table There are two id's: u.id, and p.id. You need to specify which one you're joining on with i: SELECT g.gid, g.rounds, g.finished, p.id, p.money, p.quit, i.first_name, i.avatar FROM pref_games g JOIN pref_scores u USING (gid) JOIN pref_scores p USING (gid) JOIN pref_users i ON i.id = p.id WHERE u.id = 'DE9411'; Another try: # SELECT g.gid, g.rounds, g.finished, p.id, p.money, p.quit, i.first_name, i.avatar FROM pref_games g, pref_users i JOIN pref_scores u USING (gid) JOIN pref_scores p USING (gid) WHERE u.id = 'DE9411' and p.id=i.id; ERROR: column gid specified in USING clause does not exist in left table This is complaining about pref_users i JOIN pref_scores u USING (gid) i doesn't have a gid column. Looks like you could use some work on basic SQL. I recommend picking up a basic SQL book. Michael Glaesemann grzm seespotcode net -- 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] Saving score of 3 players into a table
Fair enough. But look in the SQL Commands section under SELECT (FROM clause) as well, as that gives you the syntax and meaning and not just an overview of the concept. David J. On Oct 27, 2011, at 8:27, Alexander Farber alexander.far...@gmail.com wrote: The PostgreSQL docs are unfortunately scarce on JOINs http://www.postgresql.org/docs/8.4/static/tutorial-join.html I've never seen a JOIN producing several rows instead of columns before Michael suggested it in this thread -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgAgent and encoding
Hi.
I can't set proper encoding for pgAgent.
I have two databases: postgres and www.
postgres is encoded in UTF8, www is encoded in WIN1250.
When i run pgAgent's task in www database, it fail (message char cant
be converted to UTF8):
2011-10-27 14:50:29 CEST [nieznany] 1.COPY
BŁĄD: kolumna KodB³êdu nie istnieje przy znaku 80
2011-10-27 14:50:29 CEST [nieznany] 2.COPY
WYRAŻENIE: COPY (
SELECT
to_char(DataPliku,'MM') AS MiesiÂąc zwrotĂłw,
KodB³êdu,NKA,NTA,
sum(case when NRB like '070%' then null else 1 end) as CDR,
sum(case when NRB like '070%' then 1 end) as CDR_070,
array_agg(distinct case when NRB like '070%' then null else
ID
Kobat end) AS RecNR,
array_agg(distinct case when NRB like '070%' then ID Kobat
end)
AS RecNR_070
FROM Bladpol2
WHERE KodB³êdu = '61' AND to_char(DataPliku,'MM') like
'${MONTH_1}'
GROUP BY to_char(DataPliku,'MM'), KodB³êdu, NKA, NTA
ORDER BY NKA, NTA, Min(DataPliku)
)
TO 'e:\raport_61.csv' CSV HEADER DELIMITER ';'
2011-10-27 14:50:29 CEST [nieznany] 1.idle
BĹ ÄD: znak 0x83 kodowania WIN1250 nie ma rĂłwnowaĹĽnego w UTF8
How to set the correct encoding ?
pasman
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How are PGRES_FATAL_ERROR raised?????
Hi! I would like toknow the conditionsorreasons where postgressends aPGRES_FATAL_ERROR. El contenido de este correo electrónico y sus archivos adjuntos son privados y confidenciales y va dirigido exclusivamente a su destinatario. No se autoriza la utilización, retransmisión, diseminación, o cualquier otro uso de esta información por un receptor o entidades distintas al destinatario. Si recibe este correo sin ser el destinatario se le solicita eliminarlo y hacerlo del conocimiento del emisor. La empresa no se hace responsable de transmisiones o comunicaciones no autorizadas o emitidas por personas ajenas a sus colaboradores utilizando éste medio electrónico. The content of this email and its attached files are private and confidential and intended exclusively for the use of the individual or entity to which they are addressed. The retransmission, dissemination, or any other use of this information other than by the intended recipient is prohibited. If you have received this email in error please delete it and notify the sender. The company cannot be held liable for unauthorized electronic transmissions or communications, nor for those emitted by non-company individuals and entities.
[GENERAL] Getting X coordinate from a point(lseg), btw i read the man page about points.
Hi in the main page about geometric operations said: It is possible to access the two component numbers of a point as though it were an array with indices 0 and 1. For example, if t.p is a point column then SELECT p[0] FROM t retrieves the X coordinate and UPDATE t SET p[1] = ... changes the Y coordinate. In the same way, a value of type box or lseg can be treated as an array of two point values. 1st. i have a field Column | Type | Modifiers ---+---+--- id | integer | not null info | lseg | After read the above, i tried to select info from table limit 1; info [(647753.125,2825633.75),(647738.8125,2825626.75)] the value i want to get is:647753.125 so i tried to do: select info[0] from table limit 1; info - (647753.125,2825633.75) i still want to get647753.125, so i did: select info[0][0] from table limit 1; info -- (1 row) But, nothing appears like a NULL. then i did: select point(info[0])[0] from table limit 1; ERROR: syntax error at or near [ LINE 1: select point(info[0])[0] from table limit 1; and finally i wrote this mail :-) Regards. El contenido de este correo electrónico y sus archivos adjuntos son privados y confidenciales y va dirigido exclusivamente a su destinatario. No se autoriza la utilización, retransmisión, diseminación, o cualquier otro uso de esta información por un receptor o entidades distintas al destinatario. Si recibe este correo sin ser el destinatario se le solicita eliminarlo y hacerlo del conocimiento del emisor. La empresa no se hace responsable de transmisiones o comunicaciones no autorizadas o emitidas por personas ajenas a sus colaboradores utilizando éste medio electrónico. The content of this email and its attached files are private and confidential and intended exclusively for the use of the individual or entity to which they are addressed. The retransmission, dissemination, or any other use of this information other than by the intended recipient is prohibited. If you have received this email in error please delete it and notify the sender. The company cannot be held liable for unauthorized electronic transmissions or communications, nor for those emitted by non-company individuals and entities.
[GENERAL] Are pg_xlog/* fiels necessary for PITR?
Hi, I'm backing up the entire server directory from time to time.
pg_xlog/ directory containing WAL files is pretty heavy
(wal_level=archive). Can I exclude it from the regular tar archive?
#!/bin/sh
renice 20 $$ 2/dev/null
pgsql -U pgsql -q -c CHECKPOINT postgres # speed up pg_start_backup()
pgsql -U pgsql -q -c select pg_start_backup('sol') postgres
tar -cjf - /db 2/dev/null | ssh -q -i ~pgsql/.ssh/id_rsa -p 2022 -c
blowfish dbarchive@10.0.0.1 'cat db.tbz'
pgsql -U pgsql -q -c select pg_stop_backup() postgres
sleep 60 #wait for new WAL backups to appear
echo 'ssh -q dbarchive@10.0.0.1 ./post-backup.sh' | su -m pgsql
I want to change tar invocation to be: tar -cjf --exclude 'db/pg_xlog/*' ...
Will there be enough data in case of recovery? (May God forbid... )))
--
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 file size vs. data file size
Ben Chobot be...@silentmedia.com writes: Today I tried to restore a 70GB database with the standard pg_dump -h old_server | psql -h new_server method. I had 100GB set aside for WAL files, which I figured surely would be enough, because all of the data, including indices, is only 70GB. So I was a bit surprised when the restore hung mis-way because my pg_xlogs directory ran out of space. Is it expected that WAL files are less dense than data files? Yes, that's not particularly surprising ... but how come they weren't getting recycled? Perhaps you had configured WAL archiving but it was broken? 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] WAL file size vs. data file size
On Oct 27, 2011, at 8:44 AM, Tom Lane wrote: Ben Chobot be...@silentmedia.com writes: Today I tried to restore a 70GB database with the standard pg_dump -h old_server ∑ | psql -h new_server ∑ method. I had 100GB set aside for WAL files, which I figured surely would be enough, because all of the data, including indices, is only 70GB. So I was a bit surprised when the restore hung mis-way because my pg_xlogs directory ran out of space. Is it expected that WAL files are less dense than data files? Yes, that's not particularly surprising ... but how come they weren't getting recycled? Perhaps you had configured WAL archiving but it was broken? It's because I'm archiving wal files into Amazon's S3, which is slooow. PG is recycling as fast as it can, but when a few MB of COPY rows seem to ballon up to a few hundred MB of WAL files, it has a lot to archive before it can recycle. It'll be fine for steady state but it looks like it's just going to be a waste for this initial load. What's the expected density ratio? I was always under the impression it would be about 1:1 when doing things like COPY, and have never seen anything to the contrary. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] matching against a list of regexp?
Hi:
I need to be able to select all records with a col value that matches any of a
list of regexp. Sort of like...
select a,b,c from foo where d ~ ('^xyz','blah','shrug$');
Does anyone know the right syntax for this?
Thanks!
Re: [GENERAL] matching against a list of regexp?
On Thu, Oct 27, 2011 at 9:18 AM, Gauthier, Dave dave.gauth...@intel.com wrote:
I need to be able to select all records with a col value that matches any of
a list of regexp. Sort of like...
select a,b,c from foo where d ~ ('^xyz','blah','shrug$');
WHERE d ~ '^xyz|blah|shrug$'
--
Regards,
Richard Broersma Jr.
--
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] Are pg_xlog/* fiels necessary for PITR?
On Thu, Oct 27, 2011 at 7:57 PM, rihad ri...@mail.ru wrote:
Hi, I'm backing up the entire server directory from time to time. pg_xlog/
directory containing WAL files is pretty heavy (wal_level=archive). Can I
exclude it from the regular tar archive?
The best would be to perform pg_switch_xlog() and take a backup excluding
pg_xlog.
To recover the last moment TXNs, you might need pg_xlog (depends on when you
would be recovering). pg_switch_xlog() will reduce the dependency on pg_xlog
files to a greater extent.
#!/bin/sh
renice 20 $$ 2/dev/null
pgsql -U pgsql -q -c CHECKPOINT postgres # speed up pg_start_backup()
pg_start_backup() performs a checkpoint and ensures that all the data till
that particular checkpoint and TXN id will be backed up (or marked as needed
for data consistency while restoring and recovering).
pgsql -U pgsql -q -c select pg_start_backup('sol') postgres
tar -cjf - /db 2/dev/null | ssh -q -i ~pgsql/.ssh/id_rsa -p 2022 -c
blowfish dbarchive@10.0.0.1 'cat db.tbz'
pgsql -U pgsql -q -c select pg_stop_backup() postgres
sleep 60 #wait for new WAL backups to appear
echo 'ssh -q dbarchive@10.0.0.1 ./post-backup.sh' | su -m pgsql
I want to change tar invocation to be: tar -cjf --exclude 'db/pg_xlog/*'
...
Will there be enough data in case of recovery? (May God forbid... )))
But, all the WAL Archives between backup start time and end time must be
backed up. They are needed at any cost for the database to be consistent and
the recovery to be smooth.
Recovering to any point-in-time purely depends on your backup strategy.
Thanks
VB
Re: [GENERAL] PostGIS in a commercial project
On Tue, Oct 25, 2011 at 01:41:17PM +0200, Thomas Kellerer wrote: Thank you very much for the detailed explanation. I always have a hard time to understand the GPL especially the dividing line between using, linkin and creating a derived work. That because the GPL does not get to define those terms. They are defined by copyright law, the licence does not get to choose what is a derived work and what isn't. The FSF is of the opinion that anything linked to a GPL library is a derived work, but that isn't true in all cases (libedit vs libreadline is one of those borderline cases). I note in the OPs case they are relying on the customer to install PostGIS. The GPL only applies to *redistribution* not usage. So if you're not supplying your customers with PostGIS then the fact that it's GPL seems completely irrelevent. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
[GENERAL] Server hitting 100% CPU usage, system comes to a crawl.
Hi all, need some help/clues on tracking down a performance issue. PostgreSQL version: 8.3.11 I've got a system that has 32 cores and 128 gigs of ram. We have connection pooling set up, with about 100 - 200 persistent connections open to the database. Our applications then use these connections to query the database constantly, but when a connection isn't currently executing a query, it's IDLE. On average, at any given time, there are 3 - 6 connections that are actually executing a query, while the rest are IDLE. About once a day, queries that normally take just a few seconds slow way down, and start to pile up, to the point where instead of just having 3-6 queries running at any given time, we get 100 - 200. The whole system comes to a crawl, and looking at top, the CPU usage is 99%. Looking at top, I see no SWAP usage, very little IOWait, and there are a large number of postmaster processes at 100% cpu usage (makes sense, at this point there are 150 or so queries currently executing on the database). Tasks: 713 total, 44 running, 668 sleeping, 0 stopped, 1 zombie Cpu(s): 4.4%us, 92.0%sy, 0.0%ni, 3.0%id, 0.0%wa, 0.0%hi, 0.3%si, 0.2%st Mem: 134217728k total, 131229972k used, 2987756k free, 462444k buffers Swap: 8388600k total, 296k used, 8388304k free, 119029580k cached In the past, we noticed that autovacuum was hitting some large tables at the same time this happened, so we turned autovacuum off to see if that was the issue, and it still happened without any vacuums running. We also ruled out checkpoints being the cause. I'm currently digging through some statistics I've been gathering to see if traffic increased at all, or remained the same when the slowdown occurred. I'm also digging through the logs from the postgresql cluster (I increased verbosity yesterday), looking for any clues. Any suggestions or clues on where to look for this to see what can be causing a slowdown like this would be greatly appreciated. Thanks, - Brian F -- 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] Server hitting 100% CPU usage, system comes to a crawl.
On 10/27/11 11:39 AM, Brian Fehrle wrote: I've got a system that has 32 cores and 128 gigs of ram. We have connection pooling set up, with about 100 - 200 persistent connections open to the database. Our applications then use these connections to query the database constantly, but when a connection isn't currently executing a query, it's IDLE. On average, at any given time, there are 3 - 6 connections that are actually executing a query, while the rest are IDLE. thats not a very effective use of pooling. the pooling model, you'd have a connection pool sufficient actual database connections to satisfy your concurrency requirements, and your apps would grab a connection from the pool, do a transaction, then release the connection back to the pool. now, I don't know that this has anything to do with your performance problem, I'm just pointing out this anomaly. a pool doesn't do much good if the clients grab a connection and just sit on it. -- john r pierceN 37, W 122 santa cruz ca mid-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] Server hitting 100% CPU usage, system comes to a crawl.
On Thu, Oct 27, 2011 at 12:39 PM, Brian Fehrle bri...@consistentstate.com wrote: Looking at top, I see no SWAP usage, very little IOWait, and there are a large number of postmaster processes at 100% cpu usage (makes sense, at this point there are 150 or so queries currently executing on the database). Tasks: 713 total, 44 running, 668 sleeping, 0 stopped, 1 zombie Cpu(s): 4.4%us, 92.0%sy, 0.0%ni, 3.0%id, 0.0%wa, 0.0%hi, 0.3%si, 0.2%st Mem: 134217728k total, 131229972k used, 2987756k free, 462444k buffers Swap: 8388600k total, 296k used, 8388304k free, 119029580k cached OK, a few points. 1: You've got a zombie process. Find out what's causing that, it could be a trigger of some type for this behaviour. 2: You're 92% sys. That's bad. It means the OS is chewing up 92% of your 32 cores doing something. what tasks are at the top of the list in top? Try running vmstat 10 for a a minute or so then look at cs and int columns. If cs or int is well over 100k there could be an issue with thrashing, where your app is making some change to the db that requires all backends to be awoken at once and the machine just falls over under the load. -- 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] Server hitting 100% CPU usage, system comes to a crawl.
On Thu, Oct 27, 2011 at 1:48 PM, Scott Marlowe scott.marl...@gmail.com wrote: OK, a few points. 1: You've got a zombie process. Find out what's To expand on the zombie thing, it's quite possible that you're managing to make a pg backend process crashout, which would cause the db to restart midday, which is bad (TM) since that dumps all of shared buffers and forces all clients to reconnect. So look through the system logs for segmentation faults, etc. -- 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] Server hitting 100% CPU usage, system comes to a crawl.
On Thu, Oct 27, 2011 at 1:52 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Thu, Oct 27, 2011 at 1:48 PM, Scott Marlowe scott.marl...@gmail.com wrote: OK, a few points. 1: You've got a zombie process. Find out what's To expand on the zombie thing, it's quite possible that you're managing to make a pg backend process crashout, which would cause the db to restart midday, which is bad (TM) since that dumps all of shared buffers and forces all clients to reconnect. So look through the system logs for segmentation faults, etc. One last thing, you should upgrade to the latest 8.3 version to see if that helps. There was a bug fix around 8.3.13 or so that stopped postgresql from restarting due to a simple data corruption issue that should have only resulted in an error message not a restart of the db. I know, cause I found it. :) Thanks to the pg devs for fixing it. -- 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] Server hitting 100% CPU usage, system comes to a crawl.
On 10/27/2011 02:50 PM, Tom Lane wrote: Brian Fehrlebri...@consistentstate.com writes: Hi all, need some help/clues on tracking down a performance issue. PostgreSQL version: 8.3.11 I've got a system that has 32 cores and 128 gigs of ram. We have connection pooling set up, with about 100 - 200 persistent connections open to the database. Our applications then use these connections to query the database constantly, but when a connection isn't currently executing a query, it'sIDLE. On average, at any given time, there are 3 - 6 connections that are actually executing a query, while the rest areIDLE. About once a day, queries that normally take just a few seconds slow way down, and start to pile up, to the point where instead of just having 3-6 queries running at any given time, we get 100 - 200. The whole system comes to a crawl, and looking at top, the CPU usage is 99%. This is jumping to a conclusion based on insufficient data, but what you describe sounds a bit like the sinval queue contention problems that we fixed in 8.4. Some prior reports of that: http://archives.postgresql.org/pgsql-performance/2008-01/msg1.php http://archives.postgresql.org/pgsql-performance/2010-06/msg00452.php If your symptoms match those, the best fix would be to update to 8.4.x or later, but a stopgap solution would be to cut down on the number of idle backends. regards, tom lane That sounds somewhat close to the same issue I am seeing. Main differences being that my spike lasts for much longer than a few minutes, and can only be resolved when the cluster is restarted. Also, that second link shows TOP where much of the CPU is via the 'user', rather than the 'sys' like mine. Is there anything I can look at more to get more info on this 'sinval que contention problem'? Also, having my cpu usage high in 'sys' rather than 'us', could that be a red flag? Or is that normal? - Brian F -- 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] Server hitting 100% CPU usage, system comes to a crawl.
On Thu, Oct 27, 2011 at 2:39 PM, Brian Fehrle bri...@consistentstate.comwrote: Hi all, need some help/clues on tracking down a performance issue. PostgreSQL version: 8.3.11 I've got a system that has 32 cores and 128 gigs of ram. We have connection pooling set up, with about 100 - 200 persistent connections open to the database. Our applications then use these connections to query the database constantly, but when a connection isn't currently executing a query, it's IDLE. On average, at any given time, there are 3 - 6 connections that are actually executing a query, while the rest are IDLE. Remember, when you read pg_stat_activity, it is showing you query activity from that exact specific moment in time. Just because it looks like only 3-6 connections are executing, doesn't mean that 200 aren't actually executing .1ms statements. With such a beefy box, I would see if you can examine any stats from your connection pooler to find out how many connections are actually getting used. About once a day, queries that normally take just a few seconds slow way down, and start to pile up, to the point where instead of just having 3-6 queries running at any given time, we get 100 - 200. The whole system comes to a crawl, and looking at top, the CPU usage is 99%. Looking at top, I see no SWAP usage, very little IOWait, and there are a large number of postmaster processes at 100% cpu usage (makes sense, at this point there are 150 or so queries currently executing on the database). Tasks: 713 total, 44 running, 668 sleeping, 0 stopped, 1 zombie Cpu(s): 4.4%us, 92.0%sy, 0.0%ni, 3.0%id, 0.0%wa, 0.0%hi, 0.3%si, 0.2%st Mem: 134217728k total, 131229972k used, 2987756k free, 462444k buffers Swap: 8388600k total, 296k used, 8388304k free, 119029580k cached In the past, we noticed that autovacuum was hitting some large tables at the same time this happened, so we turned autovacuum off to see if that was the issue, and it still happened without any vacuums running. That was my next question :) We also ruled out checkpoints being the cause. How exactly did you rule this out? Just because a checkpoint is over doesn't mean that it hasn't had a negative effect on the OS cache. If you're stuck going to disk, that could be hurting you (that being said, you do point to a low I/O wait above, so you're probably correct in ruling this out). I'm currently digging through some statistics I've been gathering to see if traffic increased at all, or remained the same when the slowdown occurred. I'm also digging through the logs from the postgresql cluster (I increased verbosity yesterday), looking for any clues. Any suggestions or clues on where to look for this to see what can be causing a slowdown like this would be greatly appreciated. Are you capturing table-level stats from pg_stat_user_[tables | indexes]? Just because a server doesn't look busy doesn't mean that you're not doing 1000 index scans per second returning 1000 tuples each time. --Scott Thanks, - Brian F -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Custom data type in C with one fixed and one variable attribute
Hi,
I am trying to create a custom data type in C that has a fixed size
and a variable size attribute - is that actually possible? The
documentation mentions only one or the other but a struct in the
pg_trgm extension (TRGM) seems to have that.
The data type I have is
typedef struct {
int4 length;
uint32 foo;
char bar[1];
} oefp;
The external representation of that data type would be (1,
'hexadecimal string here'), for example.
This is my _in function to parse the external cstring.
PG_FUNCTION_INFO_V1(mydatatype_in);
Datum mydatatype_in(PG_FUNCTION_ARGS)
{
char *rawcstring = PG_GETARG_CSTRING(0);
uint32 foo;
char *buffer = (char *) palloc(strlen(rawcstring));
if (sscanf(rawcstring, (%u,%[^)]), foo, buffer) != 2)
{
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg(Invalid input syntax: \%s\, rawcstring)));
}
mydatatype *dt = (mydatatype*) palloc(VARHDRSZ + sizeof(uint32) +
strlen(buffer));
SET_VARSIZE(dt, VARHDRSZ + sizeof(uint32) + strlen(buffer));
memcpy(dt-bar, buffer, strlen(buffer));
dt-foo = foo;
PG_RETURN_POINTER(dt);
}
The problem is however that dt-bar contains not only the input string
but random characters or other garbage as well, so something must go
wrong at the end of the function. Any thoughts what it could be?
Cheers,
Adrian
--
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] Server hitting 100% CPU usage, system comes to a crawl.
On 10/27/2011 02:27 PM, Scott Mead wrote: On Thu, Oct 27, 2011 at 2:39 PM, Brian Fehrle bri...@consistentstate.com mailto:bri...@consistentstate.com wrote: Hi all, need some help/clues on tracking down a performance issue. PostgreSQL version: 8.3.11 I've got a system that has 32 cores and 128 gigs of ram. We have connection pooling set up, with about 100 - 200 persistent connections open to the database. Our applications then use these connections to query the database constantly, but when a connection isn't currently executing a query, it's IDLE. On average, at any given time, there are 3 - 6 connections that are actually executing a query, while the rest are IDLE. Remember, when you read pg_stat_activity, it is showing you query activity from that exact specific moment in time. Just because it looks like only 3-6 connections are executing, doesn't mean that 200 aren't actually executing .1ms statements. With such a beefy box, I would see if you can examine any stats from your connection pooler to find out how many connections are actually getting used. Correct, we're getting a few hundred transactions per second, but under normal operation, polling pg_stat_activity will show the average of 3 - 6 queries that were running at that moment, and those queries run for an average of 5 - 7 seconds. So my belief is that something happens to the system where either a) We get a ton more queries than normal from the application (currently hunting down data to support this), or b) the overall speed of the system slows down so that all queries increase in time so much that polling pg_stat_activity lets me actually see them. About once a day, queries that normally take just a few seconds slow way down, and start to pile up, to the point where instead of just having 3-6 queries running at any given time, we get 100 - 200. The whole system comes to a crawl, and looking at top, the CPU usage is 99%. Looking at top, I see no SWAP usage, very little IOWait, and there are a large number of postmaster processes at 100% cpu usage (makes sense, at this point there are 150 or so queries currently executing on the database). Tasks: 713 total, 44 running, 668 sleeping, 0 stopped, 1 zombie Cpu(s): 4.4%us, 92.0%sy, 0.0%ni, 3.0%id, 0.0%wa, 0.0%hi, 0.3%si, 0.2%st Mem: 134217728k total, 131229972k used, 2987756k free, 462444k buffers Swap: 8388600k total, 296k used, 8388304k free, 119029580k cached In the past, we noticed that autovacuum was hitting some large tables at the same time this happened, so we turned autovacuum off to see if that was the issue, and it still happened without any vacuums running. That was my next question :) We also ruled out checkpoints being the cause. How exactly did you rule this out? Just because a checkpoint is over doesn't mean that it hasn't had a negative effect on the OS cache. If you're stuck going to disk, that could be hurting you (that being said, you do point to a low I/O wait above, so you're probably correct in ruling this out). Checkpoint settings were set to the default per install. 5 minute timeout, 0.5 completion target, and 30s warning. Looking at the logs, we were getting a checkpoint every 5 minutes on the dot. I looked at the data in pg_stat_database and noticed that buffers written by checkpoints are near 4X that of the background writer. So I implemented some changes to get more to be written by the background writer, including increasing the checkpoint timeout to 30 minutes, and setting the frequency of the bgwriter wait time from 200ms to 50ms. checkpoints now happen 30 mins apart on the dot, and there was not a checkpoint happening the last time this issue of major slowdown occured. I'm currently digging through some statistics I've been gathering to see if traffic increased at all, or remained the same when the slowdown occurred. I'm also digging through the logs from the postgresql cluster (I increased verbosity yesterday), looking for any clues. Any suggestions or clues on where to look for this to see what can be causing a slowdown like this would be greatly appreciated. Are you capturing table-level stats from pg_stat_user_[tables | indexes]? Just because a server doesn't look busy doesn't mean that you're not doing 1000 index scans per second returning 1000 tuples each time. I am not grabbing any of those at the moment, I'll look into those. - Brian F --Scott Thanks, - Brian F -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting X coordinate from a point(lseg), btw i read the man page about points.
Ing.Edmundo.Robles.Lopez erob...@sensacd.com.mx writes: Hi in the main page about geometric operations said: It is possible to access the two component numbers of a point as though it were an array with indices 0 and 1. For example, if t.p is a point column then SELECT p[0] FROM t retrieves the X coordinate and UPDATE t SET p[1] = ... changes the Y coordinate. In the same way, a value of type box or lseg can be treated as an array of two point values. [ So how to get p2.x from an lseg value? ] select info[0] from table limit 1; (647753.125,2825633.75) Right, that gets you a point. i still want to get647753.125, so i did: select info[0][0] from table limit 1; Close, but that notation only works for a 2-dimensional array, which an lseg is not. What you need is regression=# select (info[0])[0] from table; f1 647753.125 (1 row) The parenthesized object is a point, and then an entirely separate subscripting operation has to be applied to it to get its X coordinate. then i did: select point(info[0])[0] from table limit 1; Well, that's unnecessary since info[0] is already a point, but the syntactic problem is again that you have to parenthesize the thing that the second subscript is being applied to: select (point(info[0]))[0] from table limit 1; You need parentheses any time you're going to apply subscripting or field selection to something that isn't a simple variable reference. 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] Server hitting 100% CPU usage, system comes to a crawl.
Brian Fehrle bri...@consistentstate.com writes: Hi all, need some help/clues on tracking down a performance issue. PostgreSQL version: 8.3.11 I've got a system that has 32 cores and 128 gigs of ram. We have connection pooling set up, with about 100 - 200 persistent connections open to the database. Our applications then use these connections to query the database constantly, but when a connection isn't currently executing a query, it's IDLE. On average, at any given time, there are 3 - 6 connections that are actually executing a query, while the rest are IDLE. About once a day, queries that normally take just a few seconds slow way down, and start to pile up, to the point where instead of just having 3-6 queries running at any given time, we get 100 - 200. The whole system comes to a crawl, and looking at top, the CPU usage is 99%. This is jumping to a conclusion based on insufficient data, but what you describe sounds a bit like the sinval queue contention problems that we fixed in 8.4. Some prior reports of that: http://archives.postgresql.org/pgsql-performance/2008-01/msg1.php http://archives.postgresql.org/pgsql-performance/2010-06/msg00452.php If your symptoms match those, the best fix would be to update to 8.4.x or later, but a stopgap solution would be to cut down on the number of idle backends. 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] Server hitting 100% CPU usage, system comes to a crawl.
Also, I'm not having any issue with the database restarting itself, simply becoming unresponsive / slow to respond, to the point where just sshing to the box takes about 30 seconds if not longer. Performing a pg_ctl restart on the cluster resolves the issue. I looked through the logs for any segmentation faults, none found. In fact the only thing in my log that seems to be 'bad' are the following. Oct 27 08:53:18 snip postgres[17517]: [28932839-1] user=snip,db=snip ERROR: deadlock detected Oct 27 11:49:22 snip postgres[608]: [19-1] user=snip,db=snip ERROR: could not serialize access due to concurrent update I don't believe these occurred too close to the slowdown. - Brian F On 10/27/2011 02:09 PM, Brian Fehrle wrote: On 10/27/2011 01:48 PM, Scott Marlowe wrote: On Thu, Oct 27, 2011 at 12:39 PM, Brian Fehrle bri...@consistentstate.com wrote: Looking at top, I see no SWAP usage, very little IOWait, and there are a large number of postmaster processes at 100% cpu usage (makes sense, at this point there are 150 or so queries currently executing on the database). Tasks: 713 total, 44 running, 668 sleeping, 0 stopped, 1 zombie Cpu(s): 4.4%us, 92.0%sy, 0.0%ni, 3.0%id, 0.0%wa, 0.0%hi, 0.3%si, 0.2%st Mem: 134217728k total, 131229972k used, 2987756k free, 462444k buffers Swap: 8388600k total, 296k used, 8388304k free, 119029580k cached OK, a few points. 1: You've got a zombie process. Find out what's causing that, it could be a trigger of some type for this behaviour. 2: You're 92% sys. That's bad. It means the OS is chewing up 92% of your 32 cores doing something. what tasks are at the top of the list in top? Out of the top 50 processes in top, 48 of them are postmasters, one is syslog, and one is psql. Each of the postmasters have a high %CPU, the top ones being 80% and higher, the rest being anywhere between 30% - 60%. Would postmaster 'queries' that are running attribute to the sys CPU usage, or should they be under the 'us' CPU usage? Try running vmstat 10 for a a minute or so then look at cs and int columns. If cs or int is well over 100k there could be an issue with thrashing, where your app is making some change to the db that requires all backends to be awoken at once and the machine just falls over under the load. We've restarted the postgresql cluster, so the issue is not happening at this moment. but running a vmstat 10 had my 'cs' average at 3K and 'in' averaging around 9.5K. - Brian F -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL at LISA in Boston: Dec. 7-8
All, We are going to be doing a booth at Usenix LISA[1] conference in Boston from December 7-8. If you live in Boston and are a PostgreSQL enthusiast, or if you plan to attend LISA, I want your help! I need booth volunteers to help me work the booth. All you need is some general knowledge of how to use PostgreSQL, enthusiasm, and 3 (or more) free hours. We will supply the rest, including flyers, magazines, and a t-shirt for volunteers. If you don't already have a pass for the conference, I will get you one for the exhibit hall. Or you can attend the full conference if you pay a registration fee; get $100 off with our community discount, LISA11POSTGRE. We will also have a BOF[2] on the night of the 7th at the conference hotel, where I will demo some 9.1 and 9.2 features and talk about the Postgres project. Finally, PalominoDB is organizing a Boston PUG (PostgreSQL User Group) meeting at which I will speak. Details TBD, but expect it at MIT on one of the nights of the 5th, 6th, or 8th. Maybe we can start a regular BostonPUG! [1] Large Information Systems Administration: http://www.usenix.org/events/lisa11/exhibition.html [2] http://www.usenix.org/events/lisa11/bofs.html#postgres -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] Server hitting 100% CPU usage, system comes to a crawl.
On 10/27/2011 01:48 PM, Scott Marlowe wrote: On Thu, Oct 27, 2011 at 12:39 PM, Brian Fehrle bri...@consistentstate.com wrote: Looking at top, I see no SWAP usage, very little IOWait, and there are a large number of postmaster processes at 100% cpu usage (makes sense, at this point there are 150 or so queries currently executing on the database). Tasks: 713 total, 44 running, 668 sleeping, 0 stopped, 1 zombie Cpu(s): 4.4%us, 92.0%sy, 0.0%ni, 3.0%id, 0.0%wa, 0.0%hi, 0.3%si, 0.2%st Mem: 134217728k total, 131229972k used, 2987756k free, 462444k buffers Swap: 8388600k total, 296k used, 8388304k free, 119029580k cached OK, a few points. 1: You've got a zombie process. Find out what's causing that, it could be a trigger of some type for this behaviour. 2: You're 92% sys. That's bad. It means the OS is chewing up 92% of your 32 cores doing something. what tasks are at the top of the list in top? Out of the top 50 processes in top, 48 of them are postmasters, one is syslog, and one is psql. Each of the postmasters have a high %CPU, the top ones being 80% and higher, the rest being anywhere between 30% - 60%. Would postmaster 'queries' that are running attribute to the sys CPU usage, or should they be under the 'us' CPU usage? Try running vmstat 10 for a a minute or so then look at cs and int columns. If cs or int is well over 100k there could be an issue with thrashing, where your app is making some change to the db that requires all backends to be awoken at once and the machine just falls over under the load. We've restarted the postgresql cluster, so the issue is not happening at this moment. but running a vmstat 10 had my 'cs' average at 3K and 'in' averaging around 9.5K. - Brian F -- 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] Custom data type in C with one fixed and one variable attribute
Adrian Schreyer ams...@cam.ac.uk writes:
The data type I have is
typedef struct {
int4 length;
uint32 foo;
char bar[1];
} oefp;
Seems reasonable enough.
mydatatype *dt = (mydatatype*) palloc(VARHDRSZ + sizeof(uint32) +
strlen(buffer));
SET_VARSIZE(dt, VARHDRSZ + sizeof(uint32) + strlen(buffer));
memcpy(dt-bar, buffer, strlen(buffer));
dt-foo = foo;
Fine, but keep in mind that what you are creating here is a
non-null-terminated string.
The problem is however that dt-bar contains not only the input string
but random characters or other garbage as well, so something must go
wrong at the end of the function. Any thoughts what it could be?
It sounds to me like you are inspecting dt-bar with something that
expects to see a null-terminated string. You could either fix your
inspection code, or expend one more byte to make the string be
null-terminated as stored.
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] Server hitting 100% CPU usage, system comes to a crawl.
On October 27, 2011 01:09:51 PM Brian Fehrle wrote: We've restarted the postgresql cluster, so the issue is not happening at this moment. but running a vmstat 10 had my 'cs' average at 3K and 'in' averaging around 9.5K. Random thought, is there any chance the server is physically overheating? I've seen CPUs throttle really low when overheating, which can make otherwise normal activity seem really slow. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] User feedback requested on temp tables usage for Hot Standby
Some people have asked for the ability to create temp tables on a Hot Standby server. I've got a rough implementation plan but it would have some restrictions, so I would like to check my understanding of the use case for this feature so I don't waste time implementing something nobody actually finds useful. My understanding is that the main use cases for that would be limited to these two options only: 1. CREATE TEMP TABLE foo AS SELECT 2. CREATE TEMP TABLE foo (..); INSERT INTO foo ... and sometimes a TRUNCATE foo; In almost all cases people don't run multiple INSERTs, nor do they run UPDATEs or DELETEs, so the above actions would cover 99% of use cases. Can anyone give backup to that opinion, or alternate viewpoints? Thanks, -- Simon Riggs 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] PostGIS in a commercial project
On Thu, Oct 27, 2011 at 9:44 AM, Martijn van Oosterhout klep...@svana.org wrote: I note in the OPs case they are relying on the customer to install PostGIS. The GPL only applies to *redistribution* not usage. So if you're not supplying your customers with PostGIS then the fact that it's GPL seems completely irrelevent. Also as a note here, if linking implied derivation, then all software that ran on Windows would be illegal to distribute without Microsoft's permission.. Yet at least here in the US, jailbreaking an iPhone is legal in the opinion of the Copyright Office because it allows fair use of the device, namely installing apps that Apple hasn't otherwise authorized. So I'd generally agree with the assessment above. Best Wishes, Chris Travers -- 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] PostGIS in a commercial project
On 10/27/2011 04:24 PM, Chris Travers wrote: On Thu, Oct 27, 2011 at 9:44 AM, Martijn van Oosterhout klep...@svana.org wrote: I note in the OPs case they are relying on the customer to install PostGIS. The GPL only applies to *redistribution* not usage. So if you're not supplying your customers with PostGIS then the fact that it's GPL seems completely irrelevent. Also as a note here, if linking implied derivation, then all software that ran on Windows would be illegal to distribute without Microsoft's permission.. Yet at least here in the US, jailbreaking an iPhone is legal in the opinion of the Copyright Office because it allows fair use of the device, namely installing apps that Apple hasn't otherwise authorized. So I'd generally agree with the assessment above. Not to be a killjoy but unless any of us is an attorney, I suggest we defer to a person with a law degree. This seems more like a question for the SFLC than for the general community. JD Best Wishes, Chris Travers -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development The PostgreSQL Conference - http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579 -- 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] User feedback requested on temp tables usage for Hot Standby
On Oct 27, 2011, at 5:13 PM, Simon Riggs wrote: Some people have asked for the ability to create temp tables on a Hot Standby server. I've got a rough implementation plan but it would have some restrictions, so I would like to check my understanding of the use case for this feature so I don't waste time implementing something nobody actually finds useful. My understanding is that the main use cases for that would be limited to these two options only: 1. CREATE TEMP TABLE foo AS SELECT 2. CREATE TEMP TABLE foo (..); INSERT INTO foo ... and sometimes a TRUNCATE foo; In almost all cases people don't run multiple INSERTs, nor do they run UPDATEs or DELETEs, so the above actions would cover 99% of use cases. Can anyone give backup to that opinion, or alternate viewpoints? The times that we would use a temp table on a slave are times when we would want to materialize a large set of intermediate results while doing ad hoc queries. This seems to cover that….. although, just to be sure, do I understand you in that UDPATEs and DELETEs would not be allowed? That would be fine, but having multiple INSERTs would be very handy. Of course, even having a one-time insert temp table is better than no temp table at all. :) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Server move using rsync
We're intending to move a 470GB PostgreSQL 8.3.13 database using the following technique from http://www.postgresql.org/docs/8.3/interactive/backup-file.html Another option is to use rsync to perform a file system backup. This is done by first running rsync while the database server is running, then shutting down the database server just long enough to do a second rsync. The second rsync will be much quicker than the first, because it has relatively little data to transfer, and the end result will be consistent because the server was down. This method allows a file system backup to be performed with minimal downtime. Except that we plan on an initial rsync which we think might take a couple of days, then subsequent daily rsyncs for up to a week to keep it up to date till we stop the old database, rsync again, and start the new database. A very rough approximation of our database would be half a dozen large tables taking up 1/3 of the disk space, and lots of indexes on those tables taking the other 2/3 of the space. If we assume usage characteristics of: Much less than 1% of indexed data changing per day, with almost all of those updates being within the 1% of most recently added data. Much less than 1% of historical indexed data being deleted per day with most of the deletions expected to affect sets of contiguous file pages. About 1% of new indexed data added per day I'm curious of the impact of vacuum (automatic and manual) during that process on expected amount of work rsync will have to do, and time it will take, and on what the update pattern is on files of Btree indexes. Is it worth making sure vacuum is not run, in order to reduce the amount of files that change during that period? Do a number of additions evenly spread through the domain of an indexed field's values result in localized changes to the indexes files, or changes throughout the files? How about for additions to the end of the domain of an indexed field's values (e.g. adding current dates)? Is there any way during that week, that we can verify whether our partially completed database move process is going to result in a database that starts up ok? Regards, Stephen Denne. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by replying immediately, destroy it and do not copy, disclose or use it in any way. Please consider the environment before printing this e-mail __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/dmzmessaging.htm for details. __ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] JDBC connections very occasionally hang
Hi folks, I'm seeing something that on the face of it sounds very similar to the issue reported at http://archives.postgresql.org/pgsql-general/2011-10/msg00570.php. I am using Postgresql 8.4, and the problem occurs with both the 8.4 JDBC type-3 driver and the 9.1 JDBC type-3 driver. The test I have that causes the failure runs for about 3 hours and is highly multithreaded. By the end of that time I usually see between one and three stuck threads, all waiting inside the JDBC driver for a response from the postgresql server. I can provide a stack trace if requested. The actual queries it locks up on differ from run to run; I've seen it hang on longer-running queries such as a REINDEX, or very basic queries such as an update, or even on a BEGIN TRANSACTION. Locking is not likely to be the problem since the issue occurs with only one thread involved with fair frequency. The database is also running on the same machine as the test client, so that would appear to rule out network glitches. Upon failure, there are no errors or warnings recorded in the postgresql logs either. Because of the volume of queries it will be difficult to determine by simply turning on logging whether all the queries are in fact making it to the server or not. Is there any other diagnostics you could recommend? FWIW, this behavior seems to be new to 8.4; the same software ran flawlessly and reliably on Postgresql 8.2 and 8.3. Karl -- 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] JDBC connections very occasionally hang
Karl Wright daddy...@gmail.com writes: ... By the end of that time I usually see between one and three stuck threads, all waiting inside the JDBC driver for a response from the postgresql server. I can provide a stack trace if requested. How about a stack trace from the connected backend? And what is its state as shown by the pg_stat_activity and pg_locks views? It's hard to tell from what you say here whether the problem is on the server or client side, which is surely the first thing to isolate. 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] pglesslog for Postgres 9.1.1
Hi Louis, 2011/10/27 19:49, mailtolouis2020-postg...@yahoo.com wrote: Hi, I'm sorry I'm not good in C, anyone can help to put a patch or release a new version for that? Regards Louis --- --- *From:* Tom Lane t...@sss.pgh.pa.us *To:* mailtolouis2020-postg...@yahoo.com mailtolouis2020-postg...@yahoo.com *Cc:* Postgres pgsql-general@postgresql.org *Sent:* Wednesday, October 26, 2011 3:42 PM *Subject:* Re: [GENERAL] pglesslog for Postgres 9.1.1 mailtolouis2020-postg...@yahoo.com mailto:mailtolouis2020-postg...@yahoo.com mailtolouis2020-postg...@yahoo.com mailto:mailtolouis2020-postg...@yahoo.com writes: remove.c:182: error: ‘XLOG_GIN_INSERT’ undeclared (first use in this function) remove.c:182: error: (Each undeclared identifier is reported only once remove.c:182: error: for each function it appears in.) remove.c:184: error: ‘XLOG_GIN_VACUUM_PAGE’ undeclared (first use in this function) remove.c:186: error: ‘XLOG_GIN_DELETE_PAGE’ undeclared (first use in this function) That stuff got moved to gin_private.h in 9.1 ... regards, tom lane I'm taking part in. Try this patch, https://gist.github.com/1321650 and build as following. $ make USE_PGXS=1 top_builddir=/path/to/postgresql-9.1.0 Regards, -- NAGAYASU Satoshi satoshi.nagay...@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] Server move using rsync
On Thu, Oct 27, 2011 at 7:37 PM, Stephen Denne stephen.de...@datam.co.nz wrote: We're intending to move a 470GB PostgreSQL 8.3.13 database using the following technique from http://www.postgresql.org/docs/8.3/interactive/backup-file.html Another option is to use rsync to perform a file system backup. This is done by first running rsync while the database server is running, then shutting down the database server just long enough to do a second rsync. The second rsync will be much quicker than the first, because it has relatively little data to transfer, and the end result will be consistent because the server was down. This method allows a file system backup to be performed with minimal downtime. Except that we plan on an initial rsync which we think might take a couple of days, then subsequent daily rsyncs for up to a week to keep it up to date till we stop the old database, rsync again, and start the new database. Sounds reasonable. don't forget the --delete switch or the destination will just keep growing and growing. A very rough approximation of our database would be half a dozen large tables taking up 1/3 of the disk space, and lots of indexes on those tables taking the other 2/3 of the space. If we assume usage characteristics of: Much less than 1% of indexed data changing per day, with almost all of those updates being within the 1% of most recently added data. Much less than 1% of historical indexed data being deleted per day with most of the deletions expected to affect sets of contiguous file pages. About 1% of new indexed data added per day I'm curious of the impact of vacuum (automatic and manual) during that process on expected amount of work rsync will have to do, and time it will take, and on what the update pattern is on files of Btree indexes. Is it worth making sure vacuum is not run, in order to reduce the amount of files that change during that period? Probably not. You can test that theory by turning off vacuum for a day to see how much of a change it makes. My semi-educated scientific wild-assed guess is it won't make any difference, since the file / block will be changed with or without the vacuum, and still have to be copied., Is there any way during that week, that we can verify whether our partially completed database move process is going to result in a database that starts up ok? Try starting it up? In general, the lower the traffic when you rsync the better the chances, but honestly if you're not stopping the database then you shouldn't count on luck to make it work. Note that you CAN do the whole rsync followed by setting up PITR to get a coherent database backup that is guaranteed to start up, assuming you've followed all the instructions on how to set up PITR properly. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
