[GENERAL] PL/pgSQL - Help or advice please on using unbound cursors
Hi all, Apologies if the answer to my question is obvious, but I'm fairly new to writing functions in PG. I think my problem is has a simple solution, but I'm damned if I can find it :-/ (Postgres v9.1.1 on Linux 64-bit) I'm trying to write a function which will :- 1. Take 3 input params; a catalog, schema and name for a table (to uniquely identify the target table) 2. Take further input params indicating the ORDER by clauses when reading the table (see step 4) 3. Identify and drop the primary key from the table 4. Create a cursor to scan the table in the required sequence 5. UPDATE the record currently referenced by the cursor to set a new primary key. 6. Close the cursor 7. Restore the primary key I'm stuck on step 5 when looping around the records returned from the cursor. Reading the doco (from both PG and Oracle), I believe I can only use an unbound cursor when the SELECT statement is built dynamically via the function, so using the FOR/NEXT construct is not an option as that only works with bound cursors. The problem I have is that I cannot for the life of me work out how I check for dropping off the end of the table when I cursor down it. Here's an example code fragment where I'm cursoring down the cursor results and attempting to detect I've dropped of the end :- EXECUTE ''DECLARE cursor1 CURSOR FOR SELECT ident FROM '' || tableHN || '' ORDER BY Name FOR UPDATE''; LOOP EXECUTE ''FETCH NEXT FROM cursor1 INTO rec''; recCount = recCount + 1; RAISE NOTICE ''Fetched ok %'', recCount; IF FOUND THEN EXECUTE ''UPDATE '' || tableHN || '' SET %1 = '' || recCount || '' WHERE CURRENT OF cursor1''; ELSE RAISE NOTICE ''Not Found''; EXIT; END IF; END LOOP; I never see the Not Found notice, so the IF FOUND test never appears to be triggered. Although I can catch this with a BEGIN + EXCEPTION triggered when the UPDATE call occurs after processing the last record, this results in the transaction being rolled back, so I loose the changes. I've also tried using IF cursor1%notfound but I get an error which I guess is because the cursor is not a bound cursor. Any advice on the correct way to detect end-of-resultset when using a cursor in this way or any other thoughts please. Many thanks Andrew -- 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] PL/pgSQL - Help or advice please on using unbound cursors
Hello dynamic SQL has not impact on FOUND variable - use GET DIAGNOSTICS varname = ROW_COUNT instead. Regards Pavel Stehule 2012/7/23 Andrew Hastie and...@ahastie.net: Hi all, Apologies if the answer to my question is obvious, but I'm fairly new to writing functions in PG. I think my problem is has a simple solution, but I'm damned if I can find it :-/ (Postgres v9.1.1 on Linux 64-bit) I'm trying to write a function which will :- 1. Take 3 input params; a catalog, schema and name for a table (to uniquely identify the target table) 2. Take further input params indicating the ORDER by clauses when reading the table (see step 4) 3. Identify and drop the primary key from the table 4. Create a cursor to scan the table in the required sequence 5. UPDATE the record currently referenced by the cursor to set a new primary key. 6. Close the cursor 7. Restore the primary key I'm stuck on step 5 when looping around the records returned from the cursor. Reading the doco (from both PG and Oracle), I believe I can only use an unbound cursor when the SELECT statement is built dynamically via the function, so using the FOR/NEXT construct is not an option as that only works with bound cursors. The problem I have is that I cannot for the life of me work out how I check for dropping off the end of the table when I cursor down it. Here's an example code fragment where I'm cursoring down the cursor results and attempting to detect I've dropped of the end :- EXECUTE ''DECLARE cursor1 CURSOR FOR SELECT ident FROM '' || tableHN || '' ORDER BY Name FOR UPDATE''; LOOP EXECUTE ''FETCH NEXT FROM cursor1 INTO rec''; recCount = recCount + 1; RAISE NOTICE ''Fetched ok %'', recCount; IF FOUND THEN EXECUTE ''UPDATE '' || tableHN || '' SET %1 = '' || recCount || '' WHERE CURRENT OF cursor1''; ELSE RAISE NOTICE ''Not Found''; EXIT; END IF; END LOOP; I never see the Not Found notice, so the IF FOUND test never appears to be triggered. Although I can catch this with a BEGIN + EXCEPTION triggered when the UPDATE call occurs after processing the last record, this results in the transaction being rolled back, so I loose the changes. I've also tried using IF cursor1%notfound but I get an error which I guess is because the cursor is not a bound cursor. Any advice on the correct way to detect end-of-resultset when using a cursor in this way or any other thoughts please. Many thanks Andrew -- 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] Problem with 9.1 streaming replication
Hi all. While testing a replication setup with PostgreSQL 9.1.4, I'm having an error after promoting the slave to master : some file under the 'base' subdirectory could not be read, that only 0 bytes could be fetched (see the log extract at the end) Indeed the actual file size is 0. I believe that whatever configuration mistake I may have made, such a corruption should never happen, isn't it ? That error is persistent accross the cluster restarts. Basically, the DB is corrupted, almost nothing works. The only option is to reconstruct it from a dump. The replication itself works, I'm able to start it with pg_basebackup in both ways. I thought for a while that the error happended because I hade made the mistake not to configure wal_keep_segments (didn't realize the default value was not small but actually zero). Is that realistic I set it since the first attempts to a value that I believe to be generous (1024, that should mean 16 GB of WAL). After that, I had a succesful failover simulation. But the error got back with the same fatal corruption symptoms yesterday. It seems to be correlated to the size of data being replicated. This time, that was right after a pg_restore. (dumps in custom format are around 50 MB). The bandwith between the servers is quite sufficient : I witnessed up to 70 MB/s with rsync. Promotion is done with Debian's pg_ctlcluster promote, which I believe to be like other Debian tools a wrapper to select the right cluster. Application software starts after the promotion. Any hint appreciated, thanks ! Precise version: 9.1.4-2~bpo60+1 from Debian squeeze-backports Log extract (french locale, here): 2012-07-22 21:27:59 UTC LOG: restauration termin?e de l'archive 2012-07-22 21:27:59 UTC LOG: le syst?me de bases de donn?es est pr?t pour accepter les connexions 2012-07-22 21:27:59 UTC LOG: lancement du processus autovacuum 2012-07-22 21:30:19 UTC ERREUR: n'a pas pu lire le bloc 0 du fichier « base/142824/151268 » : a lu seulement 0 octets sur 8192 -- Georges Racinet Anybox SAS, http://anybox.fr Bureau: 09 53 53 72 97 Portable: 06 51 32 07 27 GPG: 0x33AB0A35, sur serveurs publics -- 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_ctl promote -- wait for slave to be promoted fully ?
awesome. thanks for the help. Will test it out. thanks, Manoj On 07/20/2012 06:57 PM, Chris Angelico wrote: On Sat, Jul 21, 2012 at 5:28 AM, Manoj Govindassamy ma...@nimblestorage.com wrote: -- Anyway I can query the state of DB to know its status slave or master ?? So, that i can issue write statements only after I know that the DB is the new master now Yep. Use pg_is_in_recovery() - it's false on the master, true on slaves. http://www.postgresql.org/docs/9.1/static/functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] meaning of waiting column in pg_stat_activity?
Just wondering...if a query is waiting, what is it waiting for? Is this only for locks, or can it be for disk, or a memory buffer? (If there is a description of the meaning of things in pg_stat_activity and it's relatives, I'd love a pointer, 'cause I can't seem to find it.) -- e-Mail is the equivalent of a postcard written in pencil. This message may not have been sent by me, or intended for you. It may have been read or even modified while in transit. e-Mail disclaimers have the same force in law as a note passed in study hall. If your corporate attorney says that you need an disclaimer in your signature, you need a new corporate attorney. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Reporting tool for Npgsql
Hi there! I´m using Visual Studio 2010 - C# and Postgres with NPGSQL for data provider. I´d like to know what´s the best option for a Report Builder on this scenario. Thanks -- View this message in context: http://postgresql.1045698.n5.nabble.com/Reporting-tool-for-Npgsql-tp5717654.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] General guidance: Levenshtein distance versus other similarity algorithms
Hi, I am hoping you can give me some guidance here. I'm using postgresql 9.1. Basically, I'm trying to create a query on a table of businesses that will return all similar matches to a business name. This is a huge table, and there is a lot of variation in names. The length of the string can be up to 255. I've used regex, but there are always some variations of the name that are missed when I do a regex. So I decided to look at distance measures. Has anyone compared the fuzzstrmatch package to pgsimilarity? Would the levenshtein function in postgresql be the best way to go here? If so, should I use levenshtein in the contribution package or install the pgsimilarity package? Has anyone tried both implementations? This would be my query: Select * from table WHERE levenshtein (column_name,'Name of the business') = 3 ORDER BY levenshtein (column_name, 'Name of the business') Limit 10; Thank you so much for your help. Rachel
Re: [GENERAL] meaning of waiting column in pg_stat_activity?
On Mon, 2012-07-23 at 16:13 -0400, Chris Curvey wrote: Just wondering...if a query is waiting, what is it waiting for? Locks. Is this only for locks, or can it be for disk, or a memory buffer? Only locks. (If there is a description of the meaning of things in pg_stat_activity and it's relatives, I'd love a pointer, 'cause I can't seem to find it.) There isn't much at the moment. 9.2 docs will be better in that regard. See http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW for example (but be careful that some columns of pg_stat_activity change in 9.2). -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.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] Odd corruption issue reported on dba.stackexchange.com, need advice
On Thu, 2012-07-19 at 11:35 +0800, Craig Ringer wrote: The short version is that the person did a bulk-load of some PostGIS data using the osm2pgsql data-loader tool ( http://wiki.openstreetmap.org/wiki/Osm2pgsql) to populate a Pg 9.1 database. This appeared to succeed, but when the server was restarted it failed to come up, complaining that WAL contains references to invalid pages and page 1493172 of relation base/16385/477861 was uninitialized (for many different pages). The logs of the shutdown suggest that a backend probably crashed, but that shouldn't cause the WAL and heap corruption observed by the OP. Is it possible that the machine has write cache enabled? Regards, Jeff Davis -- 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] Odd corruption issue reported on dba.stackexchange.com, need advice
On 07/24/2012 08:48 AM, Jeff Davis wrote: On Thu, 2012-07-19 at 11:35 +0800, Craig Ringer wrote: The short version is that the person did a bulk-load of some PostGIS data using the osm2pgsql data-loader tool ( http://wiki.openstreetmap.org/wiki/Osm2pgsql) to populate a Pg 9.1 database. This appeared to succeed, but when the server was restarted it failed to come up, complaining that WAL contains references to invalid pages and page 1493172 of relation base/16385/477861 was uninitialized (for many different pages). The logs of the shutdown suggest that a backend probably crashed, but that shouldn't cause the WAL and heap corruption observed by the OP. Is it possible that the machine has write cache enabled? It shouldn't matter - only PostgreSQL was restarted, not the whole machine - and cleanly at that. Very strange. -- 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
