I received the following message (connnection failed to pqsql) while using ejabberd 2.1.6 with postgres 9. If I'm using posgres previous version 8.4 with ejabberd 2.1.6, the ejabberd runs perfect.
I'm not sure where the root cause of this issue is. Thanks for the suggestion and comment.
=INFO REPORT==== 2011-01-31 16:53:38 ===
I(<0.273.0>:ejabberd_odbc:216) : pgsql connection failed:
** Reason: {{badmatch,{ok,[{"SELECT 345",
[{"oid",text,65534,26,4,-1,1247},
{"typname",text,1,19,64,-1,1247}],
[["16","bool"],
["17","bytea"],
["18","char"],
["19","name"],
["20","int8"],
["21","int2"],
["22","int2vector"],
["23","int4"],
["24","regproc"],
["25","text"],
["26","oid"],
["27","tid"],
["28","xid"],
["29","cid"],
["30","oidvector"],
["71","pg_type"],
["75","pg_attribute"],
["81","pg_proc"],
["83","pg_class"],
["142","xml"],
["143","_xml"],
["210","smgr"],
["600","point"],
["601","lseg"],
["602","path"],
["603","box"],
["604","polygon"],
["628","line"],
["629","_line"],
["700","float4"],
["701","float8"],
["702","abstime"],
["703","reltime"],
["704","tinterval"],
["705","unknown"],
["718","circle"],
["719","_circle"],
["790","money"],
["791","_money"],
["829","macaddr"],
["869","inet"],
["650","cidr"],
["1000","_bool"],
["1001","_bytea"],
["1002","_char"],
["1003","_name"],
["1005","_int2"],
["1006","_int2vector"],
["1007","_int4"],
["1008","_regproc"],
["1009","_text"],
["1028","_oid"],
["1010","_tid"],
["1011","_xid"],
["1012","_cid"],
["1013","_oidvector"],
["1014","_bpchar"],
["1015","_varchar"],
["1016","_int8"],
["1017","_point"],
["1018","_lseg"],
["1019","_path"],
["1020","_box"],
["1021","_float4"],
["1022","_float8"],
["1023","_abstime"],
["1024","_reltime"],
["1025","_tinterval"],
["1027","_polygon"],
["1033","aclitem"],
["1034","_aclitem"],
["1040","_macaddr"],
["1041","_inet"],
["651","_cidr"],
["1263","_cstring"],
["1042","bpchar"],
["1043","varchar"],
["1082","date"],
["1083","time"],
["1114","timestamp"],
["1115","_timestamp"],
["1182","_date"],
["1183","_time"],
["1184","timestamptz"],
["1185","_timestamptz"],
["1186","interval"],
["1187","_interval"],
["1231","_numeric"],
["1266","timetz"],
["1270","_timetz"],
["1560","bit"],
["1561","_bit"],
["1562","varbit"],
["1563","_varbit"],
["1700","numeric"],
["1790","refcursor"],
["2201","_refcursor"],
["2202","regprocedure"],
["2203","regoper"],
["2204","regoperator"],
["2205","regclass"],
["2206","regtype"],
["2207","_regprocedure"],
["2208","_regoper"],
["2209","_regoperator"],
["2210","_regclass"],
["2211","_regtype"],
["2950","uuid"],
["2951","_uuid"],
["3614","tsvector"],
["3642","gtsvector"],
["3615","tsquery"],
["3734","regconfig"],
["3769","regdictionary"],
["3643","_tsvector"],
["3644","_gtsvector"],
["3645","_tsquery"],
["3735","_regconfig"],
["3770","_regdictionary"],
["2970","txid_snapshot"],
["2949","_txid_snapshot"],
["2249","record"],
["2287","_record"],
["2275","cstring"],
["2276","any"],
["2277","anyarray"],
["2278","void"],
["2279","trigger"],
["2280","language_handler"],
["2281","internal"],
["2282","opaque"],
["2283","anyelement"],
["2776","anynonarray"],
["3500","anyenum"],
["10000","pg_attrdef"],
["10001","pg_constraint"],
["10002","pg_inherits"],
["10003","pg_index"],
["10004","pg_operator"],
["10005","pg_opfamily"],
["10006","pg_opclass"],
["10116","pg_am"],
["10117","pg_amop"],
["10475","pg_amproc"],
["10726","pg_language"],
["10727","pg_largeobject_metadata"],
["10728","pg_largeobject"],
["10729","pg_aggregate"],
["10730","pg_statistic"],
["10731","pg_rewrite"],
["10732","pg_trigger"],
["10733","pg_description"],
["10734","pg_cast"],
["10926","pg_enum"],
["10927","pg_namespace"],
["10928","pg_conversion"],
["10929","pg_depend"],
["1248","pg_database"],
["10930","pg_db_role_setting"],
["10931","pg_tablespace"],
["10932","pg_pltemplate"],
["2842","pg_authid"],
["2843","pg_auth_members"],
["10933","pg_shdepend"],
["10934","pg_shdescription"],
["10935","pg_ts_config"],
["10936","pg_ts_config_map"],
["10937","pg_ts_dict"],
["10938","pg_ts_parser"],
["10939","pg_ts_template"],
["10940","pg_foreign_data_wrapper"],
["10941","pg_foreign_server"],
["10942","pg_user_mapping"],
["10943","pg_default_acl"],
["10944","pg_toast_2604"],
["10945","pg_toast_2606"],
["10946","pg_toast_2609"],
["10947","pg_toast_1255"],
["10948","pg_toast_2618"],
["10949","pg_toast_2619"],
["10950","pg_toast_2620"],
["10951","pg_toast_1262"],
["10952","pg_toast_2396"],
["10953","pg_toast_2964"],
["10955","pg_roles"],
["10958","pg_shadow"],
["10961","pg_group"],
["10964","pg_user"],
["10967","pg_rules"],
["10971","pg_views"],
["10974","pg_tables"],
["10978","pg_indexes"],
["10982","pg_stats"],
["10986","pg_locks"],
["10989","pg_cursors"],
["10992","pg_prepared_xacts"],
["10996","pg_prepared_statements"],
["10999","pg_settings"],
["11004","pg_timezone_abbrevs"],
["11007","pg_timezone_names"],
["11010","pg_stat_all_tables"],
["11014","pg_stat_sys_tables"],
["11017","pg_stat_user_tables"],
["11020","pg_statio_all_tables"],
["11024","pg_statio_sys_tables"],
["11027","pg_statio_user_tables"],
["11030","pg_stat_all_indexes"],
["11034","pg_stat_sys_indexes"],
["11037","pg_stat_user_indexes"],
["11040","pg_statio_all_indexes"],
["11044","pg_statio_sys_indexes"],
["11047","pg_statio_user_indexes"],
["11050","pg_statio_all_sequences"],
["11053","pg_statio_sys_sequences"],
["11056","pg_statio_user_sequences"],
["11059","pg_stat_activity"],
["11062","pg_stat_database"],
["11065","pg_stat_user_functions"],
["11069","pg_stat_bgwriter"],
["11072","pg_user_mappings"],
["11354","cardinal_number"],
["11356","character_data"],
["11357","sql_identifier"],
["11359","information_schema_catalog_name"],
["11361","time_stamp"],
["11362","yes_or_no"],
["11365","applicable_roles"],
["11369","administrable_role_authorizations"],
["11372","attributes"],
["11376","check_constraint_routine_usage"],
["11380","check_constraints"],
["11384","column_domain_usage"],
["11388","column_privileges"],
["11392","column_udt_usage"],
["11396","columns"],
["11400","constraint_column_usage"],
["11404","constraint_table_usage"],
["11408","domain_constraints"],
["11412","domain_udt_usage"],
["11415","domains"],
["11419","enabled_roles"],
["11422","key_column_usage"],
["11426","parameters"],
["11430","referential_constraints"],
["11434","role_column_grants"],
["11437","routine_privileges"],
["11441","role_routine_grants"],
["11444","routines"],
["11448","schemata"],
["11451","sequences"],
["11455","sql_features"],
["11457","pg_toast_11454"],
["11460","sql_implementation_info"],
["11462","pg_toast_11459"],
["11465","sql_languages"],
["11467","pg_toast_11464"],
["11470","sql_packages"],
["11472","pg_toast_11469"],
["11475","sql_parts"],
["11477","pg_toast_11474"],
["11480","sql_sizing"],
["11482","pg_toast_11479"],
["11485","sql_sizing_profiles"],
["11487","pg_toast_11484"],
["11490","table_constraints"],
["11494","table_privileges"],
["11498","role_table_grants"],
["11501","tables"],
["11505","triggered_update_columns"],
["11509","triggers"],
["11513","usage_privileges"],
["11517","role_usage_grants"],
["11520","view_column_usage"],
["11524","view_routine_usage"],
["11528","view_table_usage"],
["11532","views"],
["11536","data_type_privileges"],
["11540","element_types"],
["11544","_pg_foreign_data_wrappers"],
["11547","foreign_data_wrapper_options"],
["11550","foreign_data_wrappers"],
["11553","_pg_foreign_servers"],
["11556","foreign_server_options"],
["11559","foreign_servers"],
["11562","_pg_user_mappings"],
["11565","user_mapping_options"],
["11569","user_mappings"],
["26179","users"],
["26178","_users"],
["26182","pg_toast_26177"],
["26188","last"],
["26187","_last"],
["26190","pg_toast_26186"],
["26196","rosterusers"],
["26195","_rosterusers"],
["26199","pg_toast_26194"],
["26206","rostergroups"],
["26205","_rostergroups"],
["26208","pg_toast_26204"],
["26212","spool_seq_seq"],
["26215","spool"],
["26214","_spool"],
["26219","pg_toast_26213"],
["26224","vcard"],
["26223","_vcard"],
["26227","pg_toast_26222"],
["26233","vcard_search"],
["26232","_vcard_search"],
["26235","pg_toast_26231"],
["26252","privacy_default_list"],
["26251","_privacy_default_list"],
["26254","pg_toast_26250"],
["26259","privacy_list_id_seq"],
["26262","privacy_list"],
["26261","_privacy_list"],
["26266","pg_toast_26260"],
["26274","privacy_list_data"],
["26273","_privacy_list_data"],
["26276","pg_toast_26272"],
["26285","private_storage"],
["26284","_private_storage"],
["26288","pg_toast_26283"],
["26294","roster_version"],
["26293","_roster_version"],
["26296","pg_toast_26292"],
["26301","pubsub_node_nodeid_seq"],
["26304","pubsub_node"],
["26303","_pubsub_node"],
["26307","pg_toast_26302"],
["26315","pubsub_node_option"],
["26314","_pubsub_node_option"],
["26317","pg_toast_26313"],
["26327","pubsub_node_owner"],
["26326","_pubsub_node_owner"],
["26329","pg_toast_26325"],
["26338","pubsub_state_stateid_seq"],
["26341","pubsub_state"],
["26340","_pubsub_state"],
["26344","pg_toast_26339"],
["26357","pubsub_item"],
["26356","_pubsub_item"],
["26359","pg_toast_26355"],
["26370","pubsub_subscription_opt"],
["26369","_pubsub_subscription_opt"],
["26372","pg_toast_26368"]]}]}},
[{pgsql_proto,connected,2},
{gen_server,init_it,6},
{proc_lib,init_p,5}]}
** Retry after: 30 seconds
ejabberd already received a
ejabberd already received a change related to PostgreSQL 9 support:
Maybe some parts of the code don't yet support 9.
Please edit pgsql_proto.erl and replace the function connected with this one, where I've added some io:fomat lines. Compile that file, install, and trigger again the error. Let's hope you get log lines in ejabberd.log and we can debug the problem.
%% Connected state. Can now start to push messages %% between frontend and backend. But first some setup. connected(StateData, Sock) -> %% Protocol unwrapping process. Factored out to make future %% SSL and unix domain support easier. Store process under %% 'socket' in the process dictionary. ResStarted = pgsql_tcp:start_link(Sock, self()), io:format("startedlink: ~n~p~n", [ResStarted]), {ok, Unwrapper} = ResStarted, ResControlled = gen_tcp:controlling_process(Sock, Unwrapper), io:format("controlledprocess: ~n~p~n", [ResControlled]), ok = ResControlled, %% Lookup oid to type names and store them in a dictionary under %% 'oidmap' in the process dictionary. Packet = encode_message(squery, "SELECT oid, typname FROM pg_type"), ResSent = send(Sock, Packet), io:format("sent: ~n~p~n", [ResSent]), ok = ResSent, ResProcessed = process_squery([]), io:format("processsquery: ~n~p~n", [ResProcessed]), {ok, [{"SELECT" ++ _, _ColDesc, Rows}]} = ResProcessed, Rows1 = lists:map(fun ([CodeS, NameS]) -> Code = list_to_integer(CodeS), Name = list_to_atom(NameS), {Code, Name} end, Rows), OidMap = dict:from_list(Rows1), {ok, StateData#state{oidmap = OidMap}}.I tried to follow your above
I tried to follow your above instruction. Here is my steps:
1. Download pgsql_proto.erl from
2. Compile pgsql_proto.erl by using c(pgsql_proto.erl) on Program Files\ejabberd-2.1.6\bin\werl.exe
3. Stop ejabberd service
4. I put the generated beam files to Program Files\ejabberd-2.1.6\lib\pgsql-2009.1102\ebin. The size of pgsql_proto.beam is 12Kb. Comparing with the original one is 6Kb. Not sure why the size has big different.
5. Start the ejabberd service
6. The login process is successfull but I receive the following error. I think this error does not impact with the application. Just want to make sure only. Thanks
My question:
1. The size of beam file is almost twice size. Not sure has impact or not.
2. Your modification code looks like just adding some information only. So basically, I cannot login to ejabberd. But the result is I can login successfully with the following error. Looks like the source code of pgsql_proto.erl file (included on the windows installer) is not the same with mine.
3. The following error.
Thanks for your kind response.
=ERROR REPORT==== 2011-02-14 14:35:06 ===
E(<0.356.0>:mod_pubsub_odbc:3624) : transaction return internal error: {aborted,
{undef,
[{node_flat,
get_entity_subscriptions_for_send_last,
["pubsub.natalius-vaio",
{jid,
"admin",
"natalius-vaio",
"40016422031297668906430002",
"admin",
"natalius-vaio",
"40016422031297668906430002"}]},
{mod_pubsub_odbc,
node_call,
3},
{ejabberd_odbc,
execute_bloc,
1},
{ejabberd_odbc,
run_sql_cmd,
4},
{p1_fsm,
handle_msg,
10},
{proc_lib,
init_p,
5}]}}
=ERROR REPORT==== 2011-02-14 14:35:06 ===
E(<0.356.0>:mod_pubsub_odbc:3624) : transaction return internal error: {aborted,
{undef,
[{node_hometree,
get_entity_subscriptions_for_send_last,
["pubsub.natalius-vaio",
{jid,
"admin",
"natalius-vaio",
"40016422031297668906430002",
"admin",
"natalius-vaio",
"40016422031297668906430002"}]},
{mod_pubsub_odbc,
node_call,
3},
{ejabberd_odbc,
execute_bloc,
1},
{ejabberd_odbc,
run_sql_cmd,
4},
{p1_fsm,
handle_msg,
10},
{proc_lib,
init_p,
5}]}}
=ERROR REPORT==== 2011-02-14 14:35:06 ===
E(<0.356.0>:mod_pubsub_odbc:3624) : transaction return internal error: {aborted,
{undef,
[{node_pep,
get_entity_subscriptions_for_send_last,
["pubsub.natalius-vaio",
{jid,
"admin",
"natalius-vaio",
"40016422031297668906430002",
"admin",
"natalius-vaio",
"40016422031297668906430002"}]},
{mod_pubsub_odbc,
node_call,
3},
{ejabberd_odbc,
execute_bloc,
1},
{ejabberd_odbc,
run_sql_cmd,
4},
{p1_fsm,
handle_msg,
10},
{proc_lib,
init_p,
5}]}}
Re
1. The size of beam file is almost twice size. Not sure has impact or not.
That is normal: the beam files included in the installer are compressed with some option. Don't worry about the size.
2. Your modification code looks like just adding some information only.
Right, my modification was intended to print debug information.
But the result is I can login successfully with the following error. Looks like the source code of pgsql_proto.erl file (included on the windows installer) is not the same with mine.
Program Files\ejabberd-2.1.6\lib\pgsql-2009.1102\ebin
The last change in pgsql driver was done in 2009/11/02, that's the version included in the installer and that's the version you compiled, so it's strange that your compilation works and the initial did not.
3. The following error.
Check in the ejabberd Guide the mod_pubsub configuration example that says:
"Using ODBC database requires use of dedicated plugins."