[GENERAL] Ignore hash indices on replicas
I'm using Postgres hash indices on a streaming replica master. As is documented, hash indices are not logged, so the replica does not have access to them. I understand that the current wisdom is "don't use hash indices", but (unfortunately?) I have benchmarks that show that our particular application is faster by quite a bit when a hash index is available. I assume that fixing the hash index logging issue hasn't been a priority due to low interest / technical limitations, but I'm curious for a stopgap measure -- can we somehow configure Postgres to ignore hash indices on a replica, using other b-tree indices or even a sequential scan? I know I can do this on a per-connection basis by disabling various index lookup methods, but it'd be nice if it just ignored invalid indices on its own. I've not seen much reference to this problem around, but I do apologize if I've missed it in the manual or it is extremely obvious how you do this :) Thanks, Steven -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Pg CRUD for joined tables
I'm looking for FOSS PostgreSQL CRUD software, preferably Perl-compatible, which will enable me to design input forms which can handle input to tables which reference other tables by foreign key. Most CRUD applications I have seen so far only deal with direct, form field to table field input but I want to build forms which have the same fields as a VIEW derived from multiple table joins. In other words, I want the CRUD to handle the the joins. Any suggestions? gvim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Transaction question
Hi all,
I have an anomaly on my hands that I'm at a loss to understand.
We recently ran a small survey where participants were required to
answer all the questions. After validation for skipped questions,
mis-ranking answers that had to be ranked and so on, I did all of the
inserts to the survey_answers table inside a transaction block followed
by a commit. Immediately after, I updated the survey_response table
and set a timestamp to show the submitted time.
In 3 of the 38 responses, the logs show the inserts with no database
errors followed by the update statement, however, there are no entries
for that person in the survey_answers table. It is as if the
transaction rolled back, but if so, that fact is not in the logs.
Here are the log entries for one of the transaction blocks in question:
2012-06-19 15:37:36.150735500 LOG: statement: create temp
table if not exists rank_test (value integer unique not null)
2012-06-19 15:37:36.201496500 LOG: statement: truncate
rank_test
2012-06-19 15:37:36.218830500 LOG: statement: insert into
rank_test values(5)
2012-06-19 15:37:36.220442500 LOG: statement: insert into
rank_test values(4)
2012-06-19 15:37:36.221109500 LOG: statement: insert into
rank_test values(3)
2012-06-19 15:37:36.221654500 LOG: statement: insert into
rank_test values(2)
2012-06-19 15:37:36.222142500 LOG: statement: insert into
rank_test values(1)
2012-06-19 15:37:36.222759500 LOG: statement: begin
2012-06-19 15:37:36.223783500 LOG: statement: INSERT INTO
survey_answers
(srv_answers_srv_id,srv_answers_pp_id,srv_answers_question_id,srv_answers_answer,srv_answers_answer_text,srv_answers_rank,srv_answers_sub_question_id)
VALUES ('2','25399','13',NULL,NULL,'5','1')
2012-06-19 15:37:36.232725500 LOG: statement: INSERT INTO
survey_answers
(srv_answers_srv_id,srv_answers_pp_id,srv_answers_question_id,srv_answers_answer,srv_answers_answer_text,srv_answers_rank,srv_answers_sub_question_id)
VALUES ('2','25399','13',NULL,NULL,'4','2')
2012-06-19 15:37:36.234958500 LOG: statement: INSERT INTO
survey_answers
(srv_answers_srv_id,srv_answers_pp_id,srv_answers_question_id,srv_answers_answer,srv_answers_answer_text,srv_answers_rank,srv_answers_sub_question_id)
VALUES ('2','25399','13',NULL,NULL,'3','3')
2012-06-19 15:37:36.237111500 LOG: statement: INSERT INTO
survey_answers
(srv_answers_srv_id,srv_answers_pp_id,srv_answers_question_id,srv_answers_answer,srv_answers_answer_text,srv_answers_rank,srv_answers_sub_question_id)
VALUES ('2','25399','13',NULL,NULL,'2','4')
2012-06-19 15:37:36.239208500 LOG: statement: INSERT INTO
survey_answers
(srv_answers_srv_id,srv_answers_pp_id,srv_answers_question_id,srv_answers_answer,srv_answers_answer_text,srv_answers_rank,srv_answers_sub_question_id)
VALUES ('2','25399','13',NULL,NULL,'1','5')
2012-06-19 15:37:36.241350500 LOG: statement: INSERT INTO
survey_answers
(srv_answers_srv_id,srv_answers_pp_id,srv_answers_question_id,srv_answers_answer,srv_answers_answer_text,srv_answers_rank,srv_answers_sub_question_id)
VALUES ('2','25399','15','4',NULL,NULL,NULL)
2012-06-19 15:37:36.244361500 LOG: statement: INSERT INTO
survey_answers
(srv_answers_srv_id,srv_answers_pp_id,srv_answers_question_id,srv_answers_answer,srv_answers_answer_text,srv_answers_rank,srv_answers_sub_question_id)
VALUES ('2','25399','16',NULL,'Difficult behaviors',NULL,'1')
2012-06-19 15:37:36.246509500 LOG: statement: INSERT INTO
survey_answers
(srv_answers_srv_id,srv_answers_pp_id,srv_answers_question_id,srv_answers_answer,srv_answers_answer_text,srv_answers_rank,srv_answers_sub_question_id)
VALUES ('2','25399','16',NULL,'Collecting from parents',NULL,'2')
2012-06-19 15:37:36.248644500 LOG: statement: INSERT INTO
survey_answers
(srv_answers_srv_id,srv_answers_pp_id,srv_answers_question_id,srv_answers_answer,srv_answers_answer_text,srv_answers_rank,srv_answers_sub_question_id)
VALUES ('2','25399','16',NULL,'How to still have a life outside child
care.',NULL,'3')
2012-06-19 15:37:36.250742500 LOG: statement: INSERT INTO
survey_answers
(srv_answers_srv_id,srv_answers_pp_id,srv_answers_question_id,srv_answers_answer,srv_answers_answer_text,srv_answers_rank,srv_answers_sub_question_id)
VALUES ('2','25399','17','1',NULL,NULL,NULL)
2012-06-19 15:37:36.252916500 LOG: statement: INSERT INTO
survey_answers
(srv_answers_srv_id,srv_answers_pp_id,srv_answers_question_id,srv_answers_answer,srv_answers_answer_text,srv_answers_rank,srv_answers_sub_question_id)
VALUES ('2','25399','18','1',NULL,NULL,NULL)
2012-06-19 15:37:36.255100500 LOG: statement: INSERT INTO
survey_answers
(srv_answers_srv_id,srv_answers_pp_id,srv_answers_question_id,srv_answers_answer,srv_answers_answer_text,srv_answers_rank,srv_answers_sub_question_id)
VALUES ('2','25399','19','1',NULL,NULL,NULL)
2012-06-19 15:37:36.257256500 LOG: statement: INSERT INTO
survey_answers
(srv_answers_srv_id,srv_answers_pp_id,srv_answers_question_id,srv_answers_answer,srv_a
[GENERAL] Error with plpython
Hello. Strange thing happening: We rencently installed plpython in one of our test servers and installed an extension to use the google api (http://pypi.python.org/pypi/googlemaps ) , everything went fine we tested this function: CREATE OR REPLACE FUNCTION google_reverse_geocode(lat numeric, lon numeric) RETURNS text AS $BODY$ from googlemaps import GoogleMaps gmaps= GoogleMaps() address = gmaps.latlng_to_address(lat,lon) return (address) $BODY$ LANGUAGE plpythonu VOLATILE COST 100; ALTER FUNCTION google_reverse_geocode(numeric, numeric) OWNER TO pgsql; However, once we installed it on our production server that function doesnt work, it keeps sending this message: ERROR: ImportError: cannot import name SSLError CONTEXT: Traceback (most recent call last): The strange thing is that our server are the same in everything so we don’t know why is failing in our production eviroment. Thanks in advance.
Re: [GENERAL] BUG? Regular expression matching of optional character group at beginning of RE
Viktor Rosenfeld writes: > I've noticed that regular expressions which are anchored at the > beginning of the text but have an optional part at the beginning > (e.g. '^(ge)?kommen$') are not evaluated correctly if there is an > index on the column. I have committed fixes for this. Thanks for the report! regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Error with plpython
Hello. Strange thing happening: We rencently installed plpython in one of our test servers and installed an extension to use the google api (http://pypi.python.org/pypi/googlemaps ) , everything went fine we tested this function: CREATE OR REPLACE FUNCTION google_reverse_geocode(lat numeric, lon numeric) RETURNS text AS $BODY$ from googlemaps import GoogleMaps gmaps= GoogleMaps() address = gmaps.latlng_to_address(lat,lon) return (address) $BODY$ LANGUAGE plpythonu VOLATILE COST 100; ALTER FUNCTION google_reverse_geocode(numeric, numeric) OWNER TO pgsql; However, once we installed it on our production server that function doesnt work, it keeps sending this message: ERROR: ImportError: cannot import name SSLError CONTEXT: Traceback (most recent call last): The strange thing is that our server are the same in everything so we don’t know why is failing in our production eviroment. Thanks in advance.
[GENERAL] Sequence moves forward when failover is triggerred
Hey All, We used the linked guide to setup streaming replication. http://wiki.postgresql.org/wiki/Streaming_Replication When testing the failover procedure, we noticed that when the new master comes up, some sequences have moved forward (by between 30 and 40). I see there's a "cache" option when creating the sequence but we're not using that. Is this to be expected? Thanks, Andy -- Andy Chambers Software Engineer (e) achamb...@mcna.net (t) 954-682-0573 CONFIDENTIALITY NOTICE: This electronic mail may contain information that is privileged, confidential, and/or otherwise protected from disclosure to anyone other than its intended recipient(s). Any dissemination or use of this electronic mail or its contents by persons other than the intended recipient(s) is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail so that we may correct our internal records. Please then delete the original message. Thank you.
Re: [GENERAL] Do I need archive_mode = on for hot standby?
On Mon, Jul 2, 2012 at 3:33 AM, Janne H wrote: > Hi there. > > I'm planning on setting up a master database and multiple hot standby > slaves using streaming replication. > > If I use a large(*) value on > wal_keep_segments > do I really need archive_mode = on then? > > Any potential problems with this strategy I should be aware about? > > (*) With large value I mean a value such that a failed slave will be up > and running again (with a few days margin) before the master server starts > to clean out old WAL segments. > I believe you are correct. At least that's what it says here... http://wiki.postgresql.org/wiki/Streaming_Replication -- Andy
Re: [GENERAL] Sequence moves forward when failover is triggerred
Andy Chambers writes: > When testing the failover procedure, we noticed that when the new master > comes up, some sequences have moved forward (by between 30 and 40). I see > there's a "cache" option when creating the sequence but we're not using > that. > Is this to be expected? Yes. This is an artifact of an optimization that reduces the number of WAL records generated by nextval() calls --- server processes will write WAL records that say they've consumed multiple sequence values ahead of where they actually have. AFAICS this is not distinguishably different from the case where a transaction consumes that number of sequence values and then rolls back, so I don't see much wrong with that optimization. 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] Sequence moves forward when failover is triggerred
On Tue, Jul 10, 2012 at 6:48 PM, Tom Lane wrote: > Andy Chambers writes: > > When testing the failover procedure, we noticed that when the new master > > comes up, some sequences have moved forward (by between 30 and 40). I > see > > there's a "cache" option when creating the sequence but we're not using > > that. > > > Is this to be expected? > > Yes. This is an artifact of an optimization that reduces the number of > WAL records generated by nextval() calls --- server processes will write > WAL records that say they've consumed multiple sequence values ahead of > where they actually have. > > AFAICS this is not distinguishably different from the case where a > transaction consumes that number of sequence values and then rolls back, > so I don't see much wrong with that optimization. > OK Cool. Thanks for confirming. I think I made a poor decision by having our application generate checkbook numbers on demand using sequences. I've since realized (due to this and other reasons like not being able to see what nextval() would return without actually moving the sequence forward) that it would probably be better to generate an entire checkbook's worth of numbers whenever the checks are physically received from the bank. Then just have the app pull the next available check. Andy
Re: [GENERAL] Transaction question
On 07/10/2012 01:06 PM, Jeff Ross wrote: Hi all, I have an anomaly on my hands that I'm at a loss to understand. We recently ran a small survey where participants were required to answer all the questions. After validation for skipped questions, mis-ranking answers that had to be ranked and so on, I did all of the inserts to the survey_answers table inside a transaction block followed by a commit. Immediately after, I updated the survey_response table and set a timestamp to show the submitted time. In 3 of the 38 responses, the logs show the inserts with no database errors followed by the update statement, however, there are no entries for that person in the survey_answers table. It is as if the transaction rolled back, but if so, that fact is not in the logs. After finding these anomalies, I cut and pasted the insert statements from the logs into a file and inserted them manually with psql -f. No errors, so I can't see why the transaction should have rolled back, if indeed that is what happened. Is it possible for a transaction to silently fail? This is 9.1.3 running on OpenBSD and on a BBU RAID 1 mirror. It is the master in a hot-standy setup. Logging is set to "all". I have no reason to believe (yet, anyway) that this is some sort of hardware problem as I see no indication of that anywhere else. Thanks for any and all ideas! For your initial attempt everything was done in one session? I am also confused by this: 2012-06-19 15:37:36.258912500 LOG: statement: commit 2012-06-19 15:29:11.573396500 LOG: statement: update survey_response set srv_resp_submitted = now() where srv_resp_srv_id = 2 and srv_resp_pp_id = 25399 Note the time stamps. Is this the actual log? Jeff Ross Wyoming Children's Action Alliance -- 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] Error with plpython
On 07/10/2012 02:59 PM, Efraín Déctor wrote: Hello. Strange thing happening: We rencently installed plpython in one of our test servers and installed an extension to use the google api (http://pypi.python.org/pypi/googlemaps) , everything went fine we tested this function: CREATE OR REPLACE FUNCTION google_reverse_geocode(lat numeric, lon numeric) RETURNS text AS $BODY$ from googlemaps import GoogleMaps gmaps= GoogleMaps() address = gmaps.latlng_to_address(lat,lon) return (address) $BODY$ LANGUAGE plpythonu VOLATILE COST 100; ALTER FUNCTION google_reverse_geocode(numeric, numeric) OWNER TO pgsql; However, once we installed it on our production server that function doesnt work, it keeps sending this message: ERROR: ImportError: cannot import name SSLError CONTEXT: Traceback (most recent call last): The strange thing is that our server are the same in everything so we don’t know why is failing in our production eviroment. At a guess something is not the same:) 1) Does SSLError exist on the production server 2) If it does exist, is it in the Python path? Thanks in advance. -- 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] Error with plpython
We tested, the code directly into Python: from googlemaps import GoogleMaps gmaps= GoogleMaps() address = gmaps.latlng_to_address(18.835124317498853,-97.11448417315677) repr(address) And on both servers work without a problem. My guess is that something about OpenSSL on the production server is not working with plpython. But I don't know how to fix this. Thanks -Mensaje original- From: Adrian Klaver Sent: Tuesday, July 10, 2012 7:24 PM To: Efraín Déctor Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Error with plpython On 07/10/2012 02:59 PM, Efraín Déctor wrote: Hello. Strange thing happening: We rencently installed plpython in one of our test servers and installed an extension to use the google api (http://pypi.python.org/pypi/googlemaps) , everything went fine we tested this function: CREATE OR REPLACE FUNCTION google_reverse_geocode(lat numeric, lon numeric) RETURNS text AS $BODY$ from googlemaps import GoogleMaps gmaps= GoogleMaps() address = gmaps.latlng_to_address(lat,lon) return (address) $BODY$ LANGUAGE plpythonu VOLATILE COST 100; ALTER FUNCTION google_reverse_geocode(numeric, numeric) OWNER TO pgsql; However, once we installed it on our production server that function doesnt work, it keeps sending this message: ERROR: ImportError: cannot import name SSLError CONTEXT: Traceback (most recent call last): The strange thing is that our server are the same in everything so we don’t know why is failing in our production eviroment. At a guess something is not the same:) 1) Does SSLError exist on the production server 2) If it does exist, is it in the Python path? Thanks in advance. -- 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] Error with plpython
On 07/10/2012 05:34 PM, Efraín Déctor wrote: We tested, the code directly into Python: from googlemaps import GoogleMaps gmaps= GoogleMaps() address = gmaps.latlng_to_address(18.835124317498853,-97.11448417315677) repr(address) And on both servers work without a problem. My guess is that something about OpenSSL on the production server is not working with plpython. But I don't know how to fix this. So in your function add the following to the beginning and see what it returns on each server. More to the point is there a difference?: import sys plpy.notice(sys.path) Thanks -- 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] Transaction question
On 7/10/12 6:21 PM, Adrian Klaver wrote: On 07/10/2012 01:06 PM, Jeff Ross wrote: Hi all, I have an anomaly on my hands that I'm at a loss to understand. We recently ran a small survey where participants were required to answer all the questions. After validation for skipped questions, mis-ranking answers that had to be ranked and so on, I did all of the inserts to the survey_answers table inside a transaction block followed by a commit. Immediately after, I updated the survey_response table and set a timestamp to show the submitted time. In 3 of the 38 responses, the logs show the inserts with no database errors followed by the update statement, however, there are no entries for that person in the survey_answers table. It is as if the transaction rolled back, but if so, that fact is not in the logs. After finding these anomalies, I cut and pasted the insert statements from the logs into a file and inserted them manually with psql -f. No errors, so I can't see why the transaction should have rolled back, if indeed that is what happened. Is it possible for a transaction to silently fail? This is 9.1.3 running on OpenBSD and on a BBU RAID 1 mirror. It is the master in a hot-standy setup. Logging is set to "all". I have no reason to believe (yet, anyway) that this is some sort of hardware problem as I see no indication of that anywhere else. Thanks for any and all ideas! For your initial attempt everything was done in one session? All the inserts were done in one session, yes. I am also confused by this: 2012-06-19 15:37:36.258912500 LOG: statement: commit 2012-06-19 15:29:11.573396500 LOG: statement: update survey_response set srv_resp_submitted = now() where srv_resp_srv_id = 2 and srv_resp_pp_id = 25399 Note the time stamps. Is this the actual log? Crap. I noticed that I'd somehow got another update statement when I pasted into the e-mail so I altered the srv_resp_pp_id to match rather than go get the real entry from the logs again. That is for sure my bad and I apologize! The sequence of events are as I described, though. The inserts happen inside a transaction, the update happens immediately after the commit. Jeff Jeff Ross Wyoming Children's Action Alliance -- 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] Transaction question
On 07/10/2012 07:30 PM, Jeff Ross wrote: On 7/10/12 6:21 PM, Adrian Klaver wrote: On 07/10/2012 01:06 PM, Jeff Ross wrote: Hi all, Thanks for any and all ideas! For your initial attempt everything was done in one session? All the inserts were done in one session, yes. I am also confused by this: 2012-06-19 15:37:36.258912500 LOG: statement: commit 2012-06-19 15:29:11.573396500 LOG: statement: update survey_response set srv_resp_submitted = now() where srv_resp_srv_id = 2 and srv_resp_pp_id = 25399 Note the time stamps. Is this the actual log? Crap. I noticed that I'd somehow got another update statement when I pasted into the e-mail so I altered the srv_resp_pp_id to match rather than go get the real entry from the logs again. That is for sure my bad and I apologize! The sequence of events are as I described, though. The inserts happen inside a transaction, the update happens immediately after the commit. So would it be possible to see the actual log sequence? Jeff Jeff Ross Wyoming Children's Action Alliance -- 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] Python + listen/notify
Hi all. I needed to have a trigger firing after a commit, but allready realized it's not possible. As i searched upon a solution i came across the LISTEN / NOTIFY. I haven't yet realized exactly what i can do with it, but can i use it to run a simple python script located out my DB? Can't run it inside the DB since i need to do it after the completion of a few triggers (after commit). Any help would be much appreciated! Thanks in advance! Sent from my iPad -- 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] Python + listen/notify
On Tue, Jul 10, 2012 at 3:56 PM, Filipe Brandão wrote: > Hi all. > I needed to have a trigger firing after a commit, but allready realized it's > not possible. > As i searched upon a solution i came across the LISTEN / NOTIFY. I haven't > yet realized exactly what i can do with it, but can i use it to run a simple > python script located out my DB? > Can't run it inside the DB since i need to do it after the completion of a > few triggers (after commit). Certainly: your python program should stay connected to the database, waiting to receive the NOTIFYs, and then take appropriate action. See: http://packages.python.org/psycopg2/advanced.html#asynchronous-notifications Josh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error with plpython
On 07/11/2012 06:16 AM, Efraín Déctor wrote: However, once we installed it on our production server that function doesnt work, it keeps sending this message: ERROR: ImportError: cannot import name SSLError CONTEXT: Traceback (most recent call last): The strange thing is that our server are the same in everything so we don’t know why is failing in our production eviroment. I'm guessing it isn't really the same. You're probably missing a library that Python's SSL support requires, missing some Python modules, or have a library that isn't compatible with the one Python's SSL support was built against. Since you've neglected to describe your client or server environments at all, it's hard to say more. -- Craig Ringer -- 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] Sequence moves forward when failover is triggerred
On 07/11/2012 07:23 AM, Andy Chambers wrote: I think I made a poor decision by having our application generate checkbook numbers on demand using sequences. Sure did. Sequences are exempt from most transactional rules; that's why they're fast and lock-free. I'm surprised to find that the only mention of this is at the very bottom of this page: http://www.postgresql.org/docs/9.1/static/functions-sequence.html as it's an important property of sequences and one that shouldn't just be a footnote. I'd use a real table for this job. -- Craig Ringer
