Re: [GENERAL] Fwd: corrupted files
Hi! Thank you, I actually tried that and it seems that only lead to even more corrupted data. I am currently trying to recover the 'hot-standby' host that is also unhappy about one of the wal_files. I am looking at the wal with less and see only data i do not care about in it (mostly session-logging/statistics data). I am trying to remember, there was a tool that plotted the contents of the wal_files in a more readable format ... lg,k On Tue, Jul 30, 2013 at 8:23 AM, raghu ram wrote: > > On Tue, Jul 30, 2013 at 4:07 AM, Klaus Ita wrote: > >> Sorry for cross-posting, i read that pg-bug was not the right place for >> this email >> >> Hi list! >> >> depressed me gets error messages like these: >> >> 2013-07-29 20:57:09 UTC ERROR: could not access >> status of transaction 8393477 >> 2013-07-29 20:57:09 UTC DETAIL: Could not open >> file "pg_clog/0008": No such file or directory. >> >> combined with the error output of queries that do not work. >> >> I looked in pg_clog and correct, 0008 is missing. >> >> >> > You can recreate a missed "pg_clog" file with below command: > > dd if=/dev/zero of=~/9.1/main/pg_clog/0008 bs=256k count=1 (To make the > uncommitted record as they haven't been committed.) > > and then try to start the cluster. > > Thanks & Regards > Raghu Ram > >
[GENERAL] Recovery failure
Hi List! On an originally designated hot_standby that had been not so hot for the last 24h i tried to replay the wal_files left over from a (seemingly corrupted) master server [missing clog... ]. I get this output: "/etc/postgresql/9.1/main/postgresql.conf" 584L, 19942C written root@pgstandby7:/var/lib/postgresql/9.1# /etc/init.d/postgresql restart Restarting PostgreSQL 9.1 database server: main2013-07-30 06:18:20 UTC LOG: database system was interrupted while in recovery at log time 2013-07-29 09:39:03 UTC 2013-07-30 06:18:20 UTC HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target. 2013-07-30 06:18:20 UTC LOG: entering standby mode 2013-07-30 06:18:20 UTC LOG: incomplete startup packet 2013-07-30 06:18:20 UTC LOG: restored log file "0001027A002B" from archive 2013-07-30 06:18:20 UTC LOG: redo starts at 27A/2B78 2013-07-30 06:18:21 UTC FATAL: the database system is starting up 2013-07-30 06:18:21 UTC FATAL: the database system is starting up 2013-07-30 06:18:21 UTC LOG: restored log file "0001027A002C" from archive 2013-07-30 06:18:22 UTC FATAL: the database system is starting up 2013-07-30 06:18:22 UTC LOG: restored log file "0001027A002D" from archive 2013-07-30 06:18:22 UTC FATAL: the database system is starting up 2013-07-30 06:18:22 UTC LOG: restored log file "0001027A002E" from archive 2013-07-30 06:18:22 UTC LOG: consistent recovery state reached at 27A/2E3F42E8 2013-07-30 06:18:22 UTC PANIC: _bt_restore_page: cannot add item to page 2013-07-30 06:18:22 UTC CONTEXT: xlog redo split_r: rel 1663/16405/797541 left 4743, right 18008, next 9681, level 0, firstright 194 2013-07-30 06:18:22 UTC LOG: startup process (PID 11637) was terminated by signal 6: Aborted 2013-07-30 06:18:22 UTC LOG: terminating any other active server processes The PostgreSQL server failed to start. Please check the log output: 2013-07-30 06:18:20 UTC LOG: database system was interrupted while in recovery at log time 2013-07-29 09:39:03 UTC 2013-07-30 06:18:20 UTC HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target. 2013-07-30 06:18:20 UTC LOG: entering standby mode 2013-07-30 06:18:20 UTC LOG: incomplete startup packet 2013-07-30 06:18:20 UTC LOG: restored log file "0001027A002B" from archive 2013-07-30 06:18:20 UTC LOG: redo starts at 27A/2B78 2013-07-30 06:18:21 UTC FATAL: the database system is starting up 2013-07-30 06:18:21 UTC FATAL: the database system is starting up 2013-07-30 06:18:21 UTC LOG: restored log file "0001027A002C" from archive 2013-07-30 06:18:22 UTC FATAL: the database system is starting up 2013-07-30 06:18:22 UTC LOG: restored log file "0001027A002D" from archive 2013-07-30 06:18:22 UTC FATAL: the database system is starting up 2013-07-30 06:18:22 UTC LOG: restored log file "0001027A002E" from archive 2013-07-30 06:18:22 UTC LOG: consistent recovery state reached at 27A/2E3F42E8 2013-07-30 06:18:22 UTC PANIC: _bt_restore_page: cannot add item to page 2013-07-30 06:18:22 UTC CONTEXT: xlog redo split_r: rel 1663/16405/797541 left 4743, right 18008, next 9681, level 0, firstright 194 2013-07-30 06:18:22 UTC LOG: startup process (PID 11637) was terminated by signal 6: Aborted 2013-07-30 06:18:22 UTC LOG: terminating any other active server processes ... failed! failed! root@pgstandby7:/var/lib/postgresql/9.1# sha1sum /home/validad-pg-backups/pgmaster/wal_files/0001027A002E.gz 5e9390c165b0885f165ed2bceafdd88692994b8a /home/validad-pg-backups/pgmaster/wal_files/0001027A002E.gz The wal_file itself '0001027A002E.gz' is the same on all my 3 backup destinations so if there was a corruption, it occured on the master host. It there a way to 'skip' this wal file? Initially, I agreed with 'greg stark' on pgsql-bugs, that there must have been a filesystem / whatever problem with my master cluster, but how can that propagate to a hot/warm standby? shouldn't they write/delete their own files? mandatory basic info: #-- # CUSTOMIZED OPTIONS #-- #custom_variable_classes = '' # list of custom variable class names listen_addresses = '*' # what IP address(es) to listen on; max_connections = 25# (change requires restart) timezone = 'Etc/UTC' shared_buffers = 250MB # min 128kB maintenance_work_mem = 20MB checkpoint_completion_target = 0.9 effective_cache_size = 200MB hot_standby = off # "on" allows queries during recovery max_standby_archive_delay = 90min # max delay before canceling queries # when reading WAL from archive;
Re: [GENERAL] Fwd: corrupted files
On Tue, Jul 30, 2013 at 4:07 AM, Klaus Ita wrote: > Sorry for cross-posting, i read that pg-bug was not the right place for > this email > > Hi list! > > depressed me gets error messages like these: > > 2013-07-29 20:57:09 UTC ERROR: could not access > status of transaction 8393477 > 2013-07-29 20:57:09 UTC DETAIL: Could not open > file "pg_clog/0008": No such file or directory. > > combined with the error output of queries that do not work. > > I looked in pg_clog and correct, 0008 is missing. > > > You can recreate a missed "pg_clog" file with below command: dd if=/dev/zero of=~/9.1/main/pg_clog/0008 bs=256k count=1 (To make the uncommitted record as they haven't been committed.) and then try to start the cluster. Thanks & Regards Raghu Ram
Re: [GENERAL] how to get UPDATEXML function in postgresql as it works in oracle
On Tue, Jul 30, 2013 at 9:51 AM, saritha N wrote:
> Thanks for your reply Raghavendra,
>
>
Thanks for the update.
Request to mark postgresql group email while replying so it will help much
if other's have better idea as well if any correction in my test case.
> Whatever you have sent its very useful for me.I need to add one more node
> between the values,like
> InfosysBangalore
> How to add?
>
>
postgres=# update xdata set
xmlcode='InfosysBangalore' where
cast(xpath('//values/text()',xmlcode) as text[]) = '{Infosys}';
UPDATE 1
postgres=# select xmlparse(content xmlcode) from xdata ;
xmlparse
--
Enterprisedb
Wipro
InfosysBangalore
(3 rows)
Some of the xml related links:
http://www.postgresql.org/docs/9.2/static/functions-xml.html
http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.2#Ensure_that_xpath.28.29_escapes_special_characters_in_string_values
---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
Re: [GENERAL] to know what columns are getting updated
On Tue, Jul 30, 2013 at 6:05 AM, Sajeev Mayandi wrote: > Hi, > > I have bunch of rules created for tables to implement upsert functionality. > My problem is our tables gets updated from multiple places , non > –necessarily with the same no of columns. I want to figure out columns are > being asked to be updated > > E.g. The rule for the the table base-table is > > CREATE OR REPLACE RULE base-table-rule AS > ON INSERT TO base-table >WHERE (EXISTS ( SELECT 1 >FROM base-table > WHERE bas-table::x1 = new.x1 )) > DO INSTEAD UPDATE base-table SET x1=new.x1,x2=new.x2,x3 > =new.x3,x4=new.x4 > WHERE base-table.x1= new.x1; > I suppose this is for loggin purposes, so I would suggest to DO ALSO and add a debuggin statement, like for instance a log entry in a table or a raise instruction. Could it solve the problem? Anyway it seems to me there's a design problem: essentially you are converting an insert on duplicated key into an update, would not be better to use the right statement for the right purpose? Luca -- 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] Fastest Index/Algorithm to find similar sentences
I worked on a library project once that needed to perform similarity searches. The first thing needed was to construct a word dictionary where there was a number corresponding to each word. 1, 'aardvark' ... 9, 'zygote' Then you need a list of stop words like 'AND', 'THE': https://en.wikipedia.org/wiki/Stop_words Then, you write a sentence parser that turns words into their numbers So now, a bibliography entry (for example) will be a vector of numbers. You can query with things like wordcount, word x NEAR word y, etc. If the database supports it, you can also query with bitmap indexes. I have not used the PostgreSQL bitmap indexes much, but they look like they might be quite useful: http://wiki.postgresql.org/wiki/Bitmap_Indexes We used something called ALA library parsing rules that stripped off special characters, made capitalization uniform, etc. http://www.ala.org/tools/guidelines/standardsguidelines Something like this project was the outcome: http://www.ala.org/lita/ital/21/4/su You might look into library software. Maybe you can find something useful here: http://www.loc.gov/marc/marctools.html I see that there are some sourceforge MARC record projects: http://sourceforge.net/directory/os:windows/freshness:recently-updated/?q=marc%20records -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] to know what columns are getting updated
Hi, I have bunch of rules created for tables to implement upsert functionality. My problem is our tables gets updated from multiple places , non –necessarily with the same no of columns. I want to figure out columns are being asked to be updated E.g. The rule for the the table base-table is CREATE OR REPLACE RULE base-table-rule AS ON INSERT TO base-table WHERE (EXISTS ( SELECT 1 FROM base-table WHERE bas-table::x1 = new.x1 )) DO INSTEAD UPDATE base-table SET x1=new.x1,x2=new.x2,x3 =new.x3,x4=new.x4 WHERE base-table.x1= new.x1; 1) user 1 comes with the below insert Insert into base-table(x1,x2,x3,x4) values(v1,v2,v3,v4); 2) user 2 comes with the below insert Insert into base-table(x1,x2) values(v1,v2); Since user 2 uses only x1 and x2 as its column the rule replaces x3 and x4 with null. Is there a way to figure out that only x1 and x2 is being asked for an updating. In the above example column x1 is the primary key. Thanks, Sajeev
Re: [GENERAL] Fastest Index/Algorithm to find similar sentences
On Sat, Jul 27, 2013 at 10:04 AM, Janek Sendrowski wrote:
> If I'm searching for a sentence like "The tiger is the largest cat species"
> for example.
> I can only find the sentences, which include the words "tiger, largest, cat,
> species", but I also like to have the sentences with only three or even two
> of these words.
You can use & (AND), | (OR), and ! (NOT) operators in tsquery, so you
can achieve what you want just like this:
[local]:5432 grayhemp@grayhemp=# select to_tsquery('tiger | largest |
cat | species') @@ to_tsvector('The tiger is the largest cat');
?column?
--
t
Or may be I understand something wrong again?
>
> Janek
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA
Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [HACKERS] maintenance_work_mem and CREATE INDEX time
Hi Jeff, On Tue, Jul 30, 2013 at 3:25 AM, Jeff Janes wrote: > On Tue, Jul 23, 2013 at 10:56 PM, Amit Langote > wrote: >> On Wed, Jul 24, 2013 at 3:20 AM, Jeff Janes wrote: >>> >>> The heap structure used in external sorts is cache-unfriendly. The >>> bigger the heap used, the more this unfriendliness becomes apparent. >>> And the bigger maintenance_work_mem, the bigger the heap used. >>> >>> The bigger heap also means you have fewer "runs" to merge in the >>> external sort. However, as long as the number of runs still fits in >>> the same number of merge passes, this is generally not a meaningful >>> difference. >> >> Does fewer runs mean more time (in whichever phase of external sort)? > > That's complicated. In general fewer runs are faster, as the heap > used at that stage is smaller. But this difference is small. If you > can get the number of runs down to a level that needs fewer passes > over the data, that will make things faster. But this is rare. If > the sort isn't already being done in a single pass, then your sort > must be huge or your working memory setting is pathologically tiny. > > There is a rough conservation of total heap layers between the two > phases: the initial tuple heap, and the merge stage heap-of-tapes. > Say for example that by increasing work_mem, you can increase the > initial heap from 25 layers to 27 layers, while decreasing the merge > phase heap from 5 layers to 3 layers. The total number of comparisons > for the entire sort will be about the same, but the comparisons across > the 27 layer heap are much more likely to need to go to main RAM, > rather than come from L3 cache (or whatever the cache level is). > If I my assumption that fewer runs mean longer runs is plausible, may it be correct to think that performsort step (performsort_done - performsort_starting) time increases when such longer runs are created due to larger workMem? -- Amit Langote -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Fwd: corrupted files
Sorry for cross-posting, i read that pg-bug was not the right place for this email Hi list! depressed me gets error messages like these: 2013-07-29 20:57:09 UTC ERROR: could not access status of transaction 8393477 2013-07-29 20:57:09 UTC DETAIL: Could not open file "pg_clog/0008": No such file or directory. combined with the error output of queries that do not work. I looked in pg_clog and correct, 0008 is missing. On this linux machine on (3.2.0-4-amd64 #1 SMP Debian 3.2.46-1 x86_64 GNU/Linux) I am using xfs on raid1 on a megacli raid controller with 16 disks, no battery, this is why write through is enabled, no cacheing. I quite extensively created indices in transactions and removed those within these transactions to do fast deletes (foreign key constraints) before i got the error??? Now it might be that the memory on the server is corrupt? dunno, but i think it's the only 'cheap' part in the whole game. * tried to get one of the warm standby's up but one complains about not being the same pg cluster as the 'wal files'. the other hot standby won't start for some locale reason. (it's not that I did not have backups ;) ). the cluster is 'working', i get the error around 1/sec but the other clients seem fine, so it's really only a few tables that are corrupted. I cannot really take down the machine as it's quite a busy few million queries a day cluster. before the current error, i got some error that X.1 was missing which was (luckily) an index file that i could recreate via 'reindex', but i fear we're now at a table / transaction corruption which i cannot just 'rewrite'. I would not at all mind just discarding all those transactions that have accumulated in pg_clog postgres@pgmaster:~/9.1/main/pg_clog$ ls -alrt | wc -l 180 Is there any way, even with data loss to get rid of those transactions and just let the cluster behave again? It's serving some web-apps for users so some minor data loss will not be the issue. quite desperate... postgres@[local]:5432 [postgres] # select version(); version -- PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit (1 row) Customized options: #-- # CUSTOMIZED OPTIONS #-- #custom_variable_classes = '' # list of custom variable class names listen_addresses = '*' # what IP address(es) to listen on; max_connections = 320 # (change requires restart) timezone = 'Etc/UTC' shared_buffers = 2GB# min 128kB maintenance_work_mem = 250MB checkpoint_completion_target = 0.9 effective_cache_size = 20GB effective_io_concurrency = 6# 1-1000. 0 disables prefetching archive_mode= on wal_level = 'hot_standby' # http://www.postgresql.org/docs/9.1/static/runtime-config-wal.html#GUC-WAL-LEVEL archive_command = '/opt/postgres_archive_command.pl --file_path=%p --file_name=%f --work_dir=/var/tmp/ --destination_hosts= va-pg-back...@dx.ipv6.ex.net --destination_sftp_hosts=u671@ipv6.u71.y--destination_hosts= va-pg-back...@y7.ipv6.ex.net' max_wal_senders = 3 # max number of walsender processes wal_keep_segments = 50 # in logfile segments, 16MB each; 0 disables thx in advance, klaus
Re: [GENERAL] Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!!
- Original Message - From: Stephen Brearley I need someone to please look at my error log which I posted nearly six weeks ago, and tell me what is wrong. Are you trying to tell us how to help you? Or are you looking for help? Since my e-mail suggesting that you work to get PostgreSQL to install cleanly, did you: 1. Unistall PostrgreSQL? If so how? 2. Remove all traces of PostgreSQL from the registry? 3. Remove the Windows postgres user? 4. Rename your current data directory to something other than - C:\Program Files\PostgreSQL\9.2\data - C:\ProgramData\PostgreSQL\9.2\data 5. Having completed the above, try using the installer from http://www.enterprisedb.com/postgresql-924-installers-win64?ls=Crossover&type=Crossover to re-install PostgreSQL? If you did all of the above, what happened? George -- 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] Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!!
On 07/29/2013 12:41 PM, Adrian Klaver wrote: On 07/29/2013 12:23 PM, Stephen Brearley wrote: Hi Folks/Alvaro/George Weaver/Sachin Kotwal <<< PLEASE CAN SOMEONE HELP ME!! >>> To start the process and catch up any one not following the first time, some questions: 1) Exactly what are you installing i.e the one click installer from EDB ? 2) When you reinstalled did you change versions(say 9.2 --> 9.2)? Oops meant 9.1 --> 9.2 3) When you do the install and get the connect error does the Process Manager show Postgres running? 4) What is in your pg_hba.conf file? -- 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] Re: Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!!
Stephen Brearley wrote on 29.07.2013 21:23: 1.Explain why my bug report has not been responded to Because it's not a bug as you simply upgraded incorrectly. 5.Tell me how I should uninstall Postgres, if I am doing this wrong You did run the "Uninstall", did you? 6.Tell me how to remove any hidden users or how I should reset/remove directories etc. There are no hidden users - especially not with 9.2 (previous versions did create a new Windows user, but that is not the case with 9.2). Unfortunately you failed to mention from which version you upgraded. On re-installing Postgres, I have not been able to get it to work. During re-installation I get an error message saying the program exited with an error code, but otherwise appears to be okay. However, when I go into pgadmin and enter my password to connect to the server (any password gives the same response), I get a popup saying ‘Server doesn’t listen’, could not connect, connection refused etc’. The setup defaults to port 5432 during the install process. I’ve checked postgresql.conf which has not been installed, but I’ve looked at an old copy that I moved into the data folder, and that looks at port 5432. And did you verify that Postgres was actually running (e.g. by looking into the taskmanager) Postgres was working okay before, so I don’t think it’s a problem with my system or firewall, as I have not changed anything. After my initial install, I edited the registry to point to the D: drive, as I prefer to have my data stored on a separate partition, and this worked okay. What exactly did you "edit in the registry"? You shouldn't normally need to do that if you ran initdb correctly. Consequently it will not allow me access to pg.log What is "pg.log"? The Postgres install will not install with a data folder that is not empty, so I had to re-name my data folder to data_old, and moved the contents back in afterwards. What was the version before? If you upgrade from a major version to another (e.g. from 9.1 to 9.2) you can't just "copy" the data folder. You need to migrate it using pg_upgrade (which requires the old server to be still installed) or you need to use pg_dump and pg_restore to get the data from the old install into the new one. This is clearly documented in the manual. http://www.postgresql.org/docs/current/static/upgrading.html The registry seems to be pointing to the right place to find my data. Again it should not be necessary to edit anything in the registry. If you need to change the Windows service (which I assume you tried to do with hacking the registry) you should use pg_ctl for that. I don’t seem to be able to get anywhere with pgadmin, as when I try to connect I get a fatal password authentication error now. Which means the server *is* running, you simply supplied the wrong password. pgadmin.log 2013-07-18 11:17:56 ERROR : Error connecting to the server: FATAL: password authentication failed for user "SDB" That is not the logfile from your installation, it's a "normal" message from pgadmin that the password supplied for the user SDB was wrong. That has nothing to do with re-installing Postgres and clearly nothing with the "hidden" users you were referring to. So apparently your new version **is** running correctly, you just have the wrong credentials. The main difficulty seems to be trying to re-install Postgres. If you already have created a database, the install program balks at having a data folder that is not empty, causing me to use the above workaround to copy back my data afterwards. Should be able to do this! Again: the upgrade process is clearly documented in the manual. Thomas -- 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] Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!!
On 07/29/2013 12:23 PM, Stephen Brearley wrote: Hi Folks/Alvaro/George Weaver/Sachin Kotwal <<< PLEASE CAN SOMEONE HELP ME!! >>> To start the process and catch up any one not following the first time, some questions: 1) Exactly what are you installing i.e the one click installer from EDB ? 2) When you reinstalled did you change versions(say 9.2 --> 9.2)? 3) When you do the install and get the connect error does the Process Manager show Postgres running? 4) What is in your pg_hba.conf file? -- Adrian Klaver adrian.kla...@gmail.comt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!!
Hi Folks/Alvaro/George Weaver/Sachin Kotwal <<< PLEASE CAN SOMEONE HELP ME!! >>> Apologies for 'shouting': I have been trying since 21st June to get someone to help me, since my initial post using the bug reporting form which has still been ignored. I am just trying to get started with Postgres and cannot get anywhere with it, as it is refusing to install. I need someone to please look at my error log which I posted nearly six weeks ago, and tell me what is wrong. George's suggestion that I worry about getting Postgres to install correctly is EXACTLY THE PROBLEM I HAVE!! I can't work why it will not do so. I have tried re-naming the folder afterwards to sort the data as he has suggested, but as I mentioned the install is failing near the end, as shown in the log...WHY??? Sachin has said that I have not performed my uninstall correctly. What have I done wrong?? Please tell me as I have no idea. Simply telling me 'I have done it wrong' does not help. Obviously I have tried to do it right. I have repeatedly uninstalled and re-installed over and over and over again, and still I get the same result!! I have uninstalled by going into Windows Control Panel, selecting Postgres and the Uninstall option, which is the usual way to remove Windows programs, unless there is a specific Uninstall utility. If I am supposed to remove a hidden Windows user, I have no idea how to do this. Please can someone: 1. Explain why my bug report has not been responded to 2. Say what the attached file and email character limits actually are, and why they are so low 3. Look at the attached log file 4. Tell me what is wrong -or make some suggestions/how to find out 5. Tell me how I should uninstall Postgres, if I am doing this wrong 6. Tell me how to remove any hidden users or how I should reset/remove directories etc. 7. Give some general guidance on how to fix the problem. Apologies for moaning to everyone -I do appreciate your help- but I have been waiting a long time and CANNOT GET ANYWHERE WITH POSTGRESQL!! (which I think should be brilliant). Many thanks Stephen Brearley Original problem: On re-installing Postgres, I have not been able to get it to work. During re-installation I get an error message saying the program exited with an error code, but otherwise appears to be okay. However, when I go into pgadmin and enter my password to connect to the server (any password gives the same response), I get a popup saying 'Server doesn't listen', could not connect, connection refused etc'. The setup defaults to port 5432 during the install process. I've checked postgresql.conf which has not been installed, but I've looked at an old copy that I moved into the data folder, and that looks at port 5432. Postgres was working okay before, so I don't think it's a problem with my system or firewall, as I have not changed anything. After my initial install, I edited the registry to point to the D: drive, as I prefer to have my data stored on a separate partition, and this worked okay. The uninstall leaves the data folder intact, and appears to leave the old service password in case other services use it. Consequently it will not allow me access to pg.log, even with administrator privileges, only by using pgadmin. The Postgres install will not install with a data folder that is not empty, so I had to re-name my data folder to data_old, and moved the contents back in afterwards. The registry seems to be pointing to the right place to find my data. >From the install log (as attached), I see Postgres complains that loadmodules.vbs and adminpack did not install correctly. When I inspected the adminpack directory it was empty, but I'm thinking that there could be something else wrong. On my latest attempt to re-install, I don't seem to be able to get anywhere with pgadmin, as when I try to connect I get a fatal password authentication error now. The main difficulty seems to be trying to re-install Postgres. If you already have created a database, the install program balks at having a data folder that is not empty, causing me to use the above workaround to copy back my data afterwards. Should be able to do this! Thanks Stephen pgadmin.log Description: Binary data -- 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] Event trigger information accessibility on plpgsql
On Mon, Jul 29, 2013 at 10:08 PM, Javier de la Torre wrote: > You think it will be possible to, instead of comparing schemas, looking > for the last modified OID on the DB to figure out where it happened? > Not really. When a row has been updated, you can only see the new values. By comparing the row's xmin value to txid_current() you could tell that it was updated during this transaction, but there is no way to access the old row. And after a row has been deleted, there is absolutely no way for the current transaction to see it any longer. I thought that I would try my hand at writing an event trigger that was able to capture ALTER TABLE .. RENAME and ALTER TABLE ... RENAME COLUMN. Turns out to be far harder than it looks. After much hacking around, I managed to come up with a solution. The attached script audit_ddl.sql does the trick. $ psql psql (9.4devel) Type "help" for help. postgres=# create table foo(column_one text, column_two integer); NOTICE: on_start: taking catalog snapshot... NOTICE: on_commit: checking for DDL actions. CREATE TABLE postgres=# begin; BEGIN postgres=# alter table foo rename column column_one to col1; NOTICE: on_start: taking catalog snapshot... ALTER TABLE postgres=# alter table foo rename column column_two to col2; ALTER TABLE postgres=# commit; NOTICE: on_commit: checking for DDL actions. NOTICE: on_commit: table foo column column_one renamed to col1. NOTICE: on_commit: table foo column column_two renamed to col2. COMMIT Disclaimer: this is a nasty and grotesque series of hacks. You've been warned... a) using pg_advisory_xact_lock_shared() as a session-scoped variable that gets automatically reset at the end of the transaction. b) mucking around in the pg_locks view to determine if that advisory lock is already held. c) creating a temporary table whose sole purpose is to cause a constraint trigger to be fired on transaction commit. (oh yes, if you execute SET CONSTRAINTS ... during the transaction, you will surely break this.) Oh, and you have to remember to ALTER EVENT TRIGGER audit_ddl_event_trigger DISABLE; before attempting to DROP anything in the audit_ddl schema, or recursive hilarity will ensue. Regards, Andrew Tipton audit_ddl.sql Description: Binary data -- 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] async streaming and recovery_target_timeline=latest
On Jul 28, 2013, at 5:29 PM, Amit Langote wrote: > I think, the WAL recycling on standby names the recycled segments with > the latest timelineID (in this case it's 0x10) which creates WALs that > there shouldn't have been like 0010146A0001 instead of > 000F146A0001. This patch recently applied to 9.1.9 (but > not in any stable release so far) solves this problem as far as I can > see. Try and see if you can patch it: > > http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=424cc31a3785bd01108e6f4b20941c6442d3d2d0 Thanks for the link Amit. That makes for 2 patches we're eagerly awaiting in 9.1.10.
Re: [GENERAL] [HACKERS] maintenance_work_mem and CREATE INDEX time
On Tue, Jul 23, 2013 at 10:56 PM, Amit Langote wrote: > On Wed, Jul 24, 2013 at 3:20 AM, Jeff Janes wrote: >> >> The heap structure used in external sorts is cache-unfriendly. The >> bigger the heap used, the more this unfriendliness becomes apparent. >> And the bigger maintenance_work_mem, the bigger the heap used. >> >> The bigger heap also means you have fewer "runs" to merge in the >> external sort. However, as long as the number of runs still fits in >> the same number of merge passes, this is generally not a meaningful >> difference. > > Does fewer runs mean more time (in whichever phase of external sort)? That's complicated. In general fewer runs are faster, as the heap used at that stage is smaller. But this difference is small. If you can get the number of runs down to a level that needs fewer passes over the data, that will make things faster. But this is rare. If the sort isn't already being done in a single pass, then your sort must be huge or your working memory setting is pathologically tiny. There is a rough conservation of total heap layers between the two phases: the initial tuple heap, and the merge stage heap-of-tapes. Say for example that by increasing work_mem, you can increase the initial heap from 25 layers to 27 layers, while decreasing the merge phase heap from 5 layers to 3 layers. The total number of comparisons for the entire sort will be about the same, but the comparisons across the 27 layer heap are much more likely to need to go to main RAM, rather than come from L3 cache (or whatever the cache level is). Cheers, Jeff -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [BUGS] Incorrect response code after XA recovery
On Jul 29, 2013, at 16:57, Tom Jenkinson wrote: > Hi Tom, > > On Mon 29 Jul 2013 15:46:12 BST, Tom Lane wrote: >> Tom Jenkinson writes: >>> A little bit of information in the linked bugzilla report is that the >>> exception being returned has an XA error code of XAER_RMERR "An error >>> occurred in rolling back the transaction branch. The resource manager is >>> free to forget about the branch when returning this error so long as all >>> accessing threads of control have been notified of the branch’s state." >> >>> That does not sound right to me, wouldn't XAER_NOTA "The specified XID >>> is not known by the resource manager" be more accurate? >> >> No idea, but in any case that's outside Postgres' purview. It's barely >> possible that the Postgres JDBC driver has something to do with that, >> but it sounds more like the XA manager's turf. > > I am not sure what you mean here as I don't know the structure of how the > PostGres project is packaged, all I know is that the PostGres JDBC driver > component appears to be returning an XAException with the message "Error > rolling back prepared transaction" and an errorCode of XAException.XAER_RMERR > rather than XAER_NOTA. Looking at the error codes, it appears that it isn't even the Postgres JDBC driver returning that error, but the XA manager you're using, which is not a part of Postgres (nor is the JDBC driver, for that matter - that's a separate project). The errors you're quoting are from the XA manager and are about XA manager stuff. For all we know, the actual error appears to be occuring in the XA manager and not in Postgres. It's possible that the XA manager error is a result of an error that Postgres returned, but since the XA manager prints its own error message and not the original one, you'll need to uncover those error messages before we can help you with them. For all we know at this point, the error is with your XA manager, not with Postgres. If you want to be sure, grep the source of the JDBC driver for those error codes; I doubt you'll find them in there. Google was kind enough to point me here: http://jdbc.postgresql.org/development/git.html Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [BUGS] Incorrect response code after XA recovery
Hi Tom, On Mon 29 Jul 2013 15:46:12 BST, Tom Lane wrote: Tom Jenkinson writes: A little bit of information in the linked bugzilla report is that the exception being returned has an XA error code of XAER_RMERR "An error occurred in rolling back the transaction branch. The resource manager is free to forget about the branch when returning this error so long as all accessing threads of control have been notified of the branch’s state." That does not sound right to me, wouldn't XAER_NOTA "The specified XID is not known by the resource manager" be more accurate? No idea, but in any case that's outside Postgres' purview. It's barely possible that the Postgres JDBC driver has something to do with that, but it sounds more like the XA manager's turf. I am not sure what you mean here as I don't know the structure of how the PostGres project is packaged, all I know is that the PostGres JDBC driver component appears to be returning an XAException with the message "Error rolling back prepared transaction" and an errorCode of XAException.XAER_RMERR rather than XAER_NOTA. Is there a different component within your bug tracking system we should be using to raise this against the JDBC driver instead? Thanks, Tom -- 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] [BUGS] Incorrect response code after XA recovery
Hi Tom, A little bit of information in the linked bugzilla report is that the exception being returned has an XA error code of XAER_RMERR "An error occurred in rolling back the transaction branch. The resource manager is free to forget about the branch when returning this error so long as all accessing threads of control have been notified of the branch’s state." That does not sound right to me, wouldn't XAER_NOTA "The specified XID is not known by the resource manager" be more accurate? Thanks, Tom On 29/07/13 14:50, Tom Lane wrote: Ondrej Chaloupka writes: The OTS specification requires both bottom up and top down recovery to be triggered by the recovering resource. This causes that two rollback calls are done against the DB. DB receives rollback call and does the rollback. Then for the second time it returns the exceptional code. As the DB already rollbacked the transaction and forgot about it the DB returns error that no such transaction exists. But this seems to be against OTS specification. It's not likely that we would consider changing the behavior of ROLLBACK PREPARED. The alternatives we would have are (1) silently accept a ROLLBACK against a non-existent transaction ID, or (2) remember every rolled-back ID forever. Neither seems sane in the least. It seems to me that this is something client-side code, probably the XA manager, would need to deal with. The XA manager already has to track uncommitted 2-phase transactions, and would furthermore have the best idea of when it would be safe to forget about a rolled-back ID. Right offhand it appears to me that that Red Hat bug is filed against the correct component, and you need to push them harder to fix their bug/shortcoming rather than claim it's our problem. 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] how _not_ to log?
On Jul 26, 2013, at 11:27 AM, Jeff Janes wrote: > That does not look like the entire message. What was before and after > it in the log? > > For example: > > ERROR: role "foobar" already exists > STATEMENT: create role foobar encrypted password 'XXX'; > > If it were not for the ERROR, the STATEMENT would not be being logged, > in my hands. > Ah yes, that's it: Jul 29 16:12:39 staging-db11 postgres[28849]: [34-1] ERROR: role "foobar" already exists Jul 29 16:12:39 staging-db11 postgres[28849]: [34-2] STATEMENT: create role foobar with replication encrypted password 'XXX'; Interesting. I thought I was checking to see if the role existed in the chef recipe, but I guess that's somehow failing and so it's trying to create the role. Somehow I spaced that it was doing a create role here instead of an update until you got me to dig into the log messages more. :-) I guess I have some work on my end to do to make this work properly. Thanks, all, for your help, and have fun! -tspencer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to get UPDATEXML function in postgresql as it works in oracle
On Mon, Jul 29, 2013 at 7:14 PM, saritha N wrote:
> Hi,
>
> I am new to postgresql.We are migrating our application from oracle to
> postgresql.We are using postgresql version 9.2.All most everything we are
> migrated but I am unable to write a function for UPDATEXML which works same
> as in oracle.Please help me to resolve .
>
> Are you looking in this way
create table xdata(id int,xmlcode xml);
insert into xdata values (1,'Infosys');
insert into xdata values (1,'Enterprisedb');
insert into xdata values (1,'Wipro');
postgres=# select * from xdata ;
id |xmlcode
+---
1 | Infosys
1 | Enterprisedb
1 | Wipro
(3 rows)
postgres=# update xdata set xmlcode='Infosys-Bangalore'
where cast(xpath('//values/text()',xmlcode) as text[]) = '{Infosys}';
UPDATE 1
postgres=# select * from xdata ;
id | xmlcode
+
1 | Enterprisedb
1 | Wipro
1 | Infosys-Bangalore
(3 rows)
---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
Re: [GENERAL] 5 is not a smallint but '5' is
John DeSoi writes:
> I was surprised to discover this today. I can work around it, but it seems
> counterintuitive. If 5 can't be parsed as a smallint, I would feel better if
> '5' was not one either.
Yeah, 5 is an int not a smallint, but '5' is not a smallint: it's a
literal of unknown type, for which we try to deduce a type from context.
> temp=# select itest(5);
> ERROR: function itest(integer) does not exist
> temp=# select itest('5');
> itest
> ---
> 10
> (1 row)
In this example, since you only have one function named itest(),
the parser is able to deduce that the literal must be intended
to be of type smallint.
By and large, since there's an implicit cast from smallint to int and
not vice versa, it's usually best to declare functions as taking int
even if you expect they'll mainly be called with smallint parameters.
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] how to get UPDATEXML function in postgresql as it works in oracle
On Jul 29, 2013, at 15:44, saritha N wrote: > I am new to postgresql.We are migrating our application from oracle to > postgresql.We are using postgresql version 9.2.All most everything we are > migrated but I am unable to write a function for UPDATEXML which works same > as in oracle.Please help me to resolve . I'm sure many of use would love to help you, but most people in here use Postgres instead of Oracle and are not all that likely to know what the UPDATEXML function does. They might also not really feel the need to go look that up for you on the internet. Perhaps you would care to explain? I think that increases your chances of getting an answer ;) Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 5 is not a smallint but '5' is
On Mon, Jul 29, 2013 at 8:11 AM, John DeSoi wrote:
> I was surprised to discover this today. I can work around it, but it seems
> counterintuitive. If 5 can't be parsed as a smallint, I would feel better
> if '5' was not one either.
>
>
> temp=# create or replace function itest (param smallint) returns integer
> as $$ select $1 + 5; $$ language sql;
> CREATE FUNCTION
> temp=# select itest(5);
> ERROR: function itest(integer) does not exist
> LINE 1: select itest(5);
>^
> HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.
> temp=# select itest('5');
> itest
> ---
> 10
> (1 row)
>
>
FWIW, It works if you don't rely on the implicit cast, and explicitly cast
it to smallint:
select itest(5::smallint);
itest
---
10
Re: [GENERAL] Viewing another role's search path?
2013/7/23 Adrian Klaver : > On 07/23/2013 05:15 AM, Ian Lawrence Barwick wrote: >> >> Is there some simple way of viewing the search path (or other >> role-specific setting) for a role different to the current role? Apart >> from >> querying 'pg_db_role_setting' directly? >> >> Just wondering if I'm missing something obvious. > > > http://www.postgresql.org/docs/9.2/interactive/app-psql.html > > \drds [ role-pattern [ database-pattern ] ] > Lists defined configuration settings. These settings can be role-specific, > database-specific, or both. role-pattern and database-pattern are used to > select specific roles and databases to list, respectively. If omitted, or if > * is specified, all settings are listed, including those not role-specific > or database-specific, respectively. > > The ALTER ROLE and ALTER DATABASE commands are used to define per-role and > per-database configuration settings. Thanks, I missed that one. Unfortunately it produces a false negative if a user hasn't had their search path explicitly set, e.g.: postgres=# CREATE ROLE sp_test LOGIN; CREATE ROLE postgres=# \drds sp_test No matching settings found. postgres=# \c - sp_test You are now connected to database "postgres" as user "sp_test". postgres=> SHOW search_path ; search_path "$user",public (1 row) postgres=> \c - postgres You are now connected to database "postgres" as user "postgres". postgres=# ALTER ROLE sp_test SET search_path ="$user",public, foo; ALTER ROLE postgres=# \drds sp_test List of settings Role | Database | Settings -+--+- sp_test | | search_path="$user", public, foo Also it's psql-specific, so doesn't really lend itself as a global solution. The custom view will have to do for now. Regards Ian Barwick -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 5 is not a smallint but '5' is
I was surprised to discover this today. I can work around it, but it seems
counterintuitive. If 5 can't be parsed as a smallint, I would feel better if
'5' was not one either.
John DeSoi, Ph.D.
psql (9.2.4)
Type "help" for help.
temp=# create or replace function itest (param smallint) returns integer as $$
select $1 + 5; $$ language sql;
CREATE FUNCTION
temp=# select itest(5);
ERROR: function itest(integer) does not exist
LINE 1: select itest(5);
^
HINT: No function matches the given name and argument types. You might need to
add explicit type casts.
temp=# select itest('5');
itest
---
10
(1 row)
--
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] to_char with locale decimal separator
On 07/29/2013 07:27 AM, Ingmar Brouns wrote: On Mon, Jul 29, 2013 at 3:45 PM, Adrian Klaver wrote: On 07/29/2013 04:24 AM, Ingmar Brouns wrote: Hi, This work?: test=> select replace(1.500::text, '.', ','); replace - 1,500 (1 row) that would work, but that requires keeping track of which decimal separator to use yourself. If you change the locale, the code has to change as well. As to_char already converts the decimal separator in a locale aware manner, I wonder whether there is a way to do this using the existing locale facilities. Sorry, I just got to your second post where you explained that. I can't think of a way at the moment. Ingmar -- 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] [BUGS] Incorrect response code after XA recovery
Tom Jenkinson writes: > A little bit of information in the linked bugzilla report is that the > exception being returned has an XA error code of XAER_RMERR "An error > occurred in rolling back the transaction branch. The resource manager is > free to forget about the branch when returning this error so long as all > accessing threads of control have been notified of the branchs state." > That does not sound right to me, wouldn't XAER_NOTA "The specified XID > is not known by the resource manager" be more accurate? No idea, but in any case that's outside Postgres' purview. It's barely possible that the Postgres JDBC driver has something to do with that, but it sounds more like the XA manager's turf. 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] to_char with locale decimal separator
On Mon, Jul 29, 2013 at 3:45 PM, Adrian Klaver wrote: > On 07/29/2013 04:24 AM, Ingmar Brouns wrote: >> >> Hi, >> >> I need to convert some numerical values to text using the decimal >> separator that corresponds to the current locale. However, I do >> not want to lose information by padding with zero decimals or >> truncating zero decimals. So I basically want a text cast that >> also replaces the dot by a comma. I've looked at the to_char >> function and the formatting patterns, but when using those I >> either add or truncate zero decimals. >> >> >> # show lc_numeric; >> lc_numeric >> >> nl_NL.utf8 >> (1 row) >> >> # select 1.500::text; >> text >> --- >> 1.500 >> (1 row) >> >> # select to_char(1.500, '99D'); >> to_char >> -- >> 1,5000 >> (1 row) >> >> # select to_char(1.500, 'FM99D'); >> to_char >> - >> 1,5 >> (1 row) >> >> >> I would like to have '1,500' as the output, what is the best way >> to achieve this? > > > This work?: > > test=> select replace(1.500::text, '.', ','); > replace > - > 1,500 > (1 row) > that would work, but that requires keeping track of which decimal separator to use yourself. If you change the locale, the code has to change as well. As to_char already converts the decimal separator in a locale aware manner, I wonder whether there is a way to do this using the existing locale facilities. Ingmar -- 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] Event trigger information accessibility on plpgsql
Thanks Andrew, You think it will be possible to, instead of comparing schemas, looking for the last modified OID on the DB to figure out where it happened? On Jul 29, 2013, at 3:42 PM, Andrew Tipton wrote: > On Mon, Jul 29, 2013 at 9:12 PM, Javier de la Torre > wrote: > Hi, > > I was looking at the new event triggers on 9.3 and was doing some testing. I > have compiled PostgreSQL 9.3 Beta2 > It looks from the examples that the only info right now available on plpgsql > when the triggers fire is tg_event and tg_tag. > > When developing on C you get access to more things. But would it be possible > to do a plpgsql trigger where I have access to the table name and OID when I > do an ALTER table to rename a column? Right now it feels i can only know that > a table has been altered, but not which one > > I recall that the event triggers functionality was quite large, and hence was > split across a series of patches. Only some of those patches were able to be > committed in time for 9.3, hence the lack of information accessible from > plpgsql functions. [The crux of the issue preventing the remaining patches > from landing was, I believe, disagreement over how to expose the additional > information in a consistent manner.] > > Here is a crazy idea that might work: create an event trigger which is fired > on any CREATE or ALTER TABLE command (for any table), and in the trigger > function compare the current state of the catalog (pg_class and pg_attribute) > with a "snapshot" of the previous catalog state. If anything has changed, > perform the appropriate actions and update the "snapshot" with the new state. > > The downside is that this might be quite slow. But how often do you plan to > be creating and altering tables? The full scans of the catalog tables will > only happen when CREATE TABLE or ALTER TABLE commands are executed, which > might be acceptable. And when additional event trigger information is > presumably added in 9.4, you can simply treat it as a performance > optimization. > > [I'm toying with the idea of an extension which (ab)uses event triggers in > precisely this manner. The goal is to provide built-in version control that > doesn't need any external tools to be run after changes have been made to the > schema.] > > > Regards, > Andrew Tipton
Re: [GENERAL] [BUGS] Incorrect response code after XA recovery
Ondrej Chaloupka writes: > The OTS specification requires both bottom up and top down recovery to be > triggered by the recovering resource. This causes that two rollback calls are > done against the DB. DB receives rollback call and does the rollback. Then > for the second time it returns the exceptional code. As the DB already > rollbacked the transaction and forgot about it the DB returns error that no > such transaction exists. But this seems to be against OTS specification. It's not likely that we would consider changing the behavior of ROLLBACK PREPARED. The alternatives we would have are (1) silently accept a ROLLBACK against a non-existent transaction ID, or (2) remember every rolled-back ID forever. Neither seems sane in the least. It seems to me that this is something client-side code, probably the XA manager, would need to deal with. The XA manager already has to track uncommitted 2-phase transactions, and would furthermore have the best idea of when it would be safe to forget about a rolled-back ID. Right offhand it appears to me that that Red Hat bug is filed against the correct component, and you need to push them harder to fix their bug/shortcoming rather than claim it's our problem. 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] to_char with locale decimal separator
On 07/29/2013 04:24 AM, Ingmar Brouns wrote: Hi, I need to convert some numerical values to text using the decimal separator that corresponds to the current locale. However, I do not want to lose information by padding with zero decimals or truncating zero decimals. So I basically want a text cast that also replaces the dot by a comma. I've looked at the to_char function and the formatting patterns, but when using those I either add or truncate zero decimals. # show lc_numeric; lc_numeric nl_NL.utf8 (1 row) # select 1.500::text; text --- 1.500 (1 row) # select to_char(1.500, '99D'); to_char -- 1,5000 (1 row) # select to_char(1.500, 'FM99D'); to_char - 1,5 (1 row) I would like to have '1,500' as the output, what is the best way to achieve this? This work?: test=> select replace(1.500::text, '.', ','); replace - 1,500 (1 row) Thanks in advance, Ingmar -- 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] how to get UPDATEXML function in postgresql as it works in oracle
Hi, I am new to postgresql.We are migrating our application from oracle to postgresql.We are using postgresql version 9.2.All most everything we are migrated but I am unable to write a function for UPDATEXML which works same as in oracle.Please help me to resolve . Thanks & Regards, Saritha
Re: [GENERAL] Event trigger information accessibility on plpgsql
On Mon, Jul 29, 2013 at 9:12 PM, Javier de la Torre wrote: > Hi, > > I was looking at the new event triggers on 9.3 and was doing some testing. > I have compiled PostgreSQL 9.3 Beta2 > It looks from the examples that the only info right now available on > plpgsql when the triggers fire is tg_event and tg_tag. > > When developing on C you get access to more things. But would it be > possible to do a plpgsql trigger where I have access to the table name and > OID when I do an ALTER table to rename a column? Right now it feels i can > only know that a table has been altered, but not which one > I recall that the event triggers functionality was quite large, and hence was split across a series of patches. Only some of those patches were able to be committed in time for 9.3, hence the lack of information accessible from plpgsql functions. [The crux of the issue preventing the remaining patches from landing was, I believe, disagreement over how to expose the additional information in a consistent manner.] Here is a crazy idea that might work: create an event trigger which is fired on any CREATE or ALTER TABLE command (for any table), and in the trigger function compare the current state of the catalog (pg_class and pg_attribute) with a "snapshot" of the previous catalog state. If anything has changed, perform the appropriate actions and update the "snapshot" with the new state. The downside is that this might be quite slow. But how often do you plan to be creating and altering tables? The full scans of the catalog tables will only happen when CREATE TABLE or ALTER TABLE commands are executed, which might be acceptable. And when additional event trigger information is presumably added in 9.4, you can simply treat it as a performance optimization. [I'm toying with the idea of an extension which (ab)uses event triggers in precisely this manner. The goal is to provide built-in version control that doesn't need any external tools to be run after changes have been made to the schema.] Regards, Andrew Tipton
[GENERAL] Incorrect response code after XA recovery
Hi, I would like to consult with you a problematic response put by PostgreSQL after transaction recovery run by Narayana (JBossTS). I work on tests for Narayana and I hit a issue with PostgreSQL. The db returns incorrect code XAException.XA_HEURHAZ when the TM does recovery after crash of the jboss eap app server. The exception is following: Caused by: org.postgresql.util.PSQLException: ERROR: prepared transaction with identifier "131072_AP//fwAAAd7TXOBR8jj5KDE=_AP//fwAAAd7TXOBR8jj5LQAA" does not exist It's run on PostgreSQL 9.2 but the older versions seem to be affected as well. The problem occurs when TM runs on JTS transactions. The idea of the test: The test enlists two resources to a transaction. There is called prepare on resource of PostgreSQL. The app server crashes before prepare is called on second transaction participant. After restart of the app server TM tries to recover the transaction. As the fail occurs during prepare phase rollback is expected. The OTS specification requires both bottom up and top down recovery to be triggered by the recovering resource. This causes that two rollback calls are done against the DB. DB receives rollback call and does the rollback. Then for the second time it returns the exceptional code. As the DB already rollbacked the transaction and forgot about it the DB returns error that no such transaction exists. But this seems to be against OTS specification. There are some more details in the following bugzilla: https://bugzilla.redhat.com/show_bug.cgi?id=988724 Do you have some experience with such behaviour? Can I suppose this being problem of PostgreSQL? Or is there already some bug for this issue in Postgres bugtracking system? Thank you Ondra -- 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] to_char with locale decimal separator
On Mon, Jul 29, 2013 at 1:24 PM, Ingmar Brouns wrote: > Hi, > > I need to convert some numerical values to text using the decimal > separator that corresponds to the current locale. However, I do > not want to lose information by padding with zero decimals or > truncating zero decimals. So I basically want a text cast that > also replaces the dot by a comma. I've looked at the to_char > function and the formatting patterns, but when using those I > either add or truncate zero decimals. > > > # show lc_numeric; > lc_numeric > > nl_NL.utf8 > (1 row) > > # select 1.500::text; > text > --- > 1.500 > (1 row) > > # select to_char(1.500, '99D'); > to_char > -- >1,5000 > (1 row) > > # select to_char(1.500, 'FM99D'); > to_char > - > 1,5 > (1 row) > Maybe its important to add that the nr of decimals in the values is variable. I could of course adjust the pattern to work for 1.500, but I'm looking for a solution that will work with an arbitrary numerical value and that's a little more elegant than casting to text, and then replacing the dot by a comma. > > I would like to have '1,500' as the output, what is the best way > to achieve this? > > Thanks in advance, > > Ingmar -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Event trigger information accessibility on plpgsql
Hi, I was looking at the new event triggers on 9.3 and was doing some testing. I have compiled PostgreSQL 9.3 Beta2 It looks from the examples that the only info right now available on plpgsql when the triggers fire is tg_event and tg_tag. When developing on C you get access to more things. But would it be possible to do a plpgsql trigger where I have access to the table name and OID when I do an ALTER table to rename a column? Right now it feels i can only know that a table has been altered, but not which one Thanks in advance. Javier de la Torre @jatorre CartoDB 148 Lafayette St. PH, New York, 10013,USA +1 347 320 7715 www.cartodb.com Map, analyze and build applications with your data
[GENERAL] to_char with locale decimal separator
Hi, I need to convert some numerical values to text using the decimal separator that corresponds to the current locale. However, I do not want to lose information by padding with zero decimals or truncating zero decimals. So I basically want a text cast that also replaces the dot by a comma. I've looked at the to_char function and the formatting patterns, but when using those I either add or truncate zero decimals. # show lc_numeric; lc_numeric nl_NL.utf8 (1 row) # select 1.500::text; text --- 1.500 (1 row) # select to_char(1.500, '99D'); to_char -- 1,5000 (1 row) # select to_char(1.500, 'FM99D'); to_char - 1,5 (1 row) I would like to have '1,500' as the output, what is the best way to achieve this? Thanks in advance, Ingmar -- 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] postgres FDW cost estimation options unrecognized in 9.3-beta1
Hi Lonni, Greetings, I have a postgresql-9.3-beta1 cluster setup (from the yum.postgresql.org RPMs), where I'm experimenting with the postgres FDW extension. The documentation ( http://www.postgresql.org/docs/9.3/static/postgres-fdw.html ) references three Cost Estimation Options which can be set for a foreign table or a foreign server. However when I attempt to set them, I always get an error that the option is not found: ### nightly=# show SERVER_VERSION ; server_version 9.3beta1 nightly=# \des+ List of foreign servers Name| Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options| Description ---+---+--+---+--+-+-- -+- cuda_db10 | lfriedman | postgres_fdw | | | | (host 'cuda-db10', dbname 'nightly', port '5432') | (1 row) nightly=# ALTER SERVER cuda_db10 OPTIONS (SET use_remote_estimate 'true') ; ERROR: option "use_remote_estimate" not found ### Am I doing something wrong, or is this a bug? You got this error because you can't alter, in a server, an option which is not yet defined using 'SET'. You could do in this way if your server was already created with the option 'use_remote_estimate' set, just for instance, to 'false': nightly=# \des+ List of foreign servers Name| Owner | Foreign-data wrapper | Access privileges | Type | Version |FDW Options | Description ---+---+--+---+--+-+---+- cuda_db10 | lfriedman | postgres_fdw | | | | (host 'cuda-db10', dbname 'nightly', port '5432', use_remote_estimate 'false') | (1 row) nightly=# ALTER SERVER cuda_db10 OPTIONS (SET use_remote_estimate 'true') ; ALTER SERVER nightly=# \des+ List of foreign servers Name| Owner | Foreign-data wrapper | Access privileges | Type | Version |FDW Options | Description ---+---+--+---+--+-+---+- cuda_db10 | lfriedman | postgres_fdw | | | | (host 'cuda-db10', dbname 'nightly', port '5432', use_remote_estimate 'true') | (1 row) If your server was not created with any 'use_remote_estimate' option, you have to add it in this way: nightly=# \des+ List of foreign servers Name| Owner | Foreign-data wrapper | Access privileges | Type | Version |FDW Options| Description ---+---+--+---+--+-+ --+- cuda_db10 | lfriedman | postgres_fdw | | | | (host 'cuda-db10', dbname 'nightly', port '5432') | (1 row) nightly=# ALTER SERVER cuda_db10 OPTIONS (use_remote_estimate 'true') ; ALTER SERVER nightly=# \des+ List of foreign servers Name| Owner | Foreign-data wrapper | Access privileges | Type | Version |FDW Options | Description ---+---+--+---+--+-+---+- cuda_db10 | lfriedman | postgres_fdw | | | | (host 'cuda-db10', dbname 'nightly', port '5432', use_remote_estimate 'true') | (1 row) To create your server with 'use_remote_estimate' option already set to 'true' you have to do: nightly=# CREATE SERVER cuda_db10 FOREIGN DATA WRAPPER postgres_fdw OPTIONS(host 'cuda-db10', dbname 'nightly', port '5432', use_remote_estimate 'true'); CREATE SERVER nightly=# \des+ List of foreign servers Name| Owner | Foreign-data wrapper | Access privileges | Type | Version |FDW Options | Description ---+---+--+---+--+-+---+- cuda_db10 | lfriedman | postgres_fdw | | | | (host 'cuda-db10', dbname 'nightly', port '5432', use_remote_estimate 'true') | (1 row) Hope this can help. Giuseppe. -- Giuseppe B
Re: [GENERAL] SQL for multimedia retrieval
On Sat, Jul 27, 2013 at 10:24 PM, Olivier Austina wrote: > Hi, > > Can SQL be used to retrieve data from multimedia content such as audio, > video, image, full text? > Regards > Olivier > I am not sure if this is really helpful but anyway, if you are willing to spend some efforts on getting multimedia content retrieval to work in PostgreSQL, you could use GiST (http://www.postgresql.org/docs/9.2/static/gist.html) and write custom indexing methods. These methods would define your content retrieval criteria while GiST itself provides an interface so that you could store and retrieve the indexed content using PostgreSQL. -- Amit Langote -- 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] Trigger and deadlock
Here is pg_lock for relation Y (= 2027300) locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted --+--+--+--+---++---+-+---+--++--+--+- tuple| 2026760 | 2027300 | 365 |42 || | | | | 6/313 | 9274 | ShareLock | f tuple| 2026760 | 2027300 | 365 |42 || | | | | 5/113 | 9273 | ExclusiveLock | f relation | 2026760 | 2027300 | | || | | | | 3/2532 | 9104 | AccessShareLock | t relation | 2026760 | 2027300 | | || | | | | 3/2532 | 9104 | RowShareLock | t relation | 2026760 | 2027300 | | || | | | | 3/2532 | 9104 | RowExclusiveLock | t relation | 2026760 | 2027300 | | || | | | | 5/113 | 9273 | AccessShareLock | t relation | 2026760 | 2027300 | | || | | | | 5/113 | 9273 | RowShareLock | t relation | 2026760 | 2027300 | | || | | | | 5/113 | 9273 | RowExclusiveLock | t tuple| 2026760 | 2027300 | 365 |42 || | | | | 3/2532 | 9104 | ExclusiveLock | t relation | 2026760 | 2027300 | | || | | | | 6/313 | 9274 | AccessShareLock | t relation | 2026760 | 2027300 | | || | | | | 6/313 | 9274 | RowShareLock | t 29-07-2013 10:12:29,004 ERROR GrailsExceptionResolver - PSQLException occurred when processing request: [POST] /api/annotation.json ERROR: deadlock detected Détail : Process 9273 waits for ExclusiveLock on tuple (365,42) of relation 2027300 of database 2026760; blocked by process 9104. Process 9104 waits for ShareLock on transaction 1286966; blocked by process 9273. Indice : See server log for query details. Où : SQL statement "UPDATE y SET count_x = count_x + 1 WHERE y.id = NEW.y_id" PL/pgSQL function "incrementy" line 6 at SQL statement. Stacktrace follows: org.postgresql.util.PSQLException: ERROR: deadlock detected I don't understand where the "ExclusiveLock" come from. Postgresql Doc says ( http://www.postgresql.org/docs/9.1/static/explicit-locking.html): "EXCLUSIVE: This lock mode is not automatically acquired on tables by any PostgreSQL command." In log, I just see that Hibernate just read the row y (365,42) (simple select). No explicit lock. Loïc 2013/7/29 Loïc Rollus > Hi, > > Thanks for your quick reply! > I found the table. But the ctid of the row has changed. But during my > test, I update only 1 row from this table, so I know the row. > > I had already put log_statement to 'all'. It's strange because in the log, > I only see simple "SELECT ... FROM" on this table (no UPDATE or SELECT FOR > UPDATE). But I can't see request from trigger (not show by > log_statement='all'?). > > Here is a log sample (simplify: insert into X should update the counter on > the specific row from Y) > > > 1286781 22142 2013-07-26 13:55:22 CEST LOG: execute : insert > into X... > 1286780 22426 2013-07-26 13:55:22 CEST LOG: execute : insert > into X... > > it should be UPDATE on Y from trigger here > 1286781 22142 2013-07-26 13:55:22 CEST LOG: execute select this_.id as > id54_0_, from > [...22142 follow its way, commit, and carry another request ] > 1286785 22142 2013-07-26 13:55:23 CEST LOG: execute : insert > into X > 1286785 22142 2013-07-26 13:55:25 CEST ERROR: deadlock detected > 1286785 22142 2013-07-26 13:55:25 CEST DETAIL: Process 22142 waits for > ShareLock on transaction 1286780; blocked by process 22426. > Process 22426 waits for ExclusiveLock on tuple (365,13) of relation > 2027300 of database 2026760; blocked by process 22142. > 1286785 22142 2013-07-26 13:55:25 CEST HINT: See server log for query > details. > 1286785 22142 2013-07-26 13:55:25 CEST CONTEXT: SQL statement "UPDATE Y > 1286785 22142 2013-07-26 13:55:25 CEST STATEMENT: insert into X > Process 22426 waits for ShareLock on transaction 1286782; blocked by > process 22429. > 1286785 22142 2013-07-26 13:55:25 CEST LOG: execute S_3: ROLLBACK > > I will try to query pg_locks to see more info > > Thanks > > > > > > > > 2013/7/26 Albe Laurenz > >> Loïc Rollus wrote: >> > I've try to make some concurrency robustness test with an web server >> app that use Hibernate and >> > Postgres. >> > It seems that my trigger make deadlock when multiple thread use it. >> > >> > I will try to simplify examples: >> > I hav
