Closed rossj-cargotel closed 8 years ago
psoo
commented
9 years ago Jeff,
sorry for the delay, i'm not able to reproduce this here. Could you provide your table definitions from your local postgres instance and your remote Informix server? Just the types matter, you could anonymize the column and table names in case they are sensitive.
rossj-cargotel
commented
9 years ago Hi Bernd! Here are the definitions for client_profile, ft_client_profile, and the informix ft_client_profile table. Lots of columns. I'll be moving this to a new server inside our network in the next few days. It will be interesting to see if this is related to the number of columns and the associated round trips between servers.
Table "metro.client_profile"
Column | Type | Modifiers | Storage | Stats target | Description
--------------------+-----------------------------+-------------------------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('metro.client_profile_id_seq'::regclass) | plain | |
co_name | text | not null | extended | |
dba_name | text | | extended | |
addr1 | text | | extended | |
addr2 | text | | extended | |
city | text | | extended | |
state | text | | extended | |
zip | text | | extended | |
ctry | text | | extended | |
contact_fname | text | | extended | |
contact_lname | text | | extended | |
contact_title | text | | extended | |
phone | text | | extended | |
fax | text | | extended | |
email | text | | extended | |
bid_email | text | | extended | |
contact_pref | text | | extended | |
business | text | | extended | |
load_types | text | | extended | |
veh_types | text | | extended | |
num_veh_shipped_mo | integer | | plain | |
primary_locs | text | | extended | |
trade_assoc | text | | extended | |
web_site | text | | extended | |
icc_mc | text | | extended | |
ins_agent | text | | extended | |
liability_limits | text | | extended | |
damage_limits | text | | extended | |
deductible | text | | extended | |
status | text | | extended | |
misc | text | | extended | |
truck_num_type | text | | extended | |
timestamp | timestamp without time zone | | plain | |
flag_shipper | text | | extended | |
flag_carrier | text | | extended | |
flag_priv | text | | extended | |
username | text | | extended | |
password | text | | extended | |
secret_word | text | | extended | |
cell_phone | text | | extended | |
flag_demouser | text | | extended | |
cl_key | text | | extended | |
srch_key | text | | extended | |
scope | integer | | plain | |
scope_def | integer | | plain | |
parent_id | integer | | plain | |
mc | text | | extended | |
mc_broker | text | | extended | |
dot | text | | extended | |
internal_only | text | | extended | |
flag_broker | text | | extended | |
bid_typ | text | | extended | |
credit | text | | extended | |
speed | text | | extended | |
price | numeric | | main | |
deadline_hours | integer | | plain | |
deadline_days | integer | | plain | |
insp_pref | text | | extended | |
flag_cons | text | | extended | |
contract_rcvd | text | | extended | |
flag_bid | text | | extended | |
ins_lia | integer | | plain | |
ins_cargo | integer | | plain | |
ins_wcomp | integer | | plain | |
ins_req_lia | integer | | plain | |
ins_req_cargo | integer | | plain | |
ins_req_wcomp | integer | | plain | |
fed_tax_id | text | | extended | |
parent_client_id | text | | extended | |
ins_exp | date | | plain | |
phone2 | text | | extended | |
phone3 | text | | extended | |
phone4 | text | | extended | |
location_code | text | | extended | |
units | text | | extended | |
corp_parent_id | integer | | plain | |
is_domain | text | | extended | |
invoice_email | text | | extended | |
flag_lien_holder | text | | extended | |
dummy | integer | | plain | |
Indexes:
"client_profile_pkey" PRIMARY KEY, btree (id)
"client_profile_id_unique_idx" UNIQUE, btree (id)
"client_profile_c_idx" btree (cl_key)
"client_profile_f_idx" btree (fax)
"client_profile_i_c_d_idx" btree (id, co_name, dba_name)
"client_profile_l_p_idx" btree (location_code, parent_id)
"client_profile_p_i_idx" btree (parent_id, is_domain)
"client_profile_p_idx" btree (phone)
"client_profile_s_idx" btree (status)
"client_profile_s_p_idx" btree (srch_key, parent_id)
"client_profile_z_s_idx" btree (zip, status)
Foreign-key constraints:
"client_profile_corp_parent_id_client_profile_id_fk" FOREIGN KEY (corp_parent_id) REFERENCES metro.client_profile(id)
"client_profile_parent_id_client_profile_id_fk" FOREIGN KEY (parent_id) REFERENCES metro.client_profile(id)
Referenced by:
TABLE "metro.carrier_rating" CONSTRAINT "carrier_rating_client_id_client_profile_id_fk" FOREIGN KEY (client_id) REFERENCES metro.client_profile(id)
TABLE "metro.carrier_rating" CONSTRAINT "carrier_rating_rater_id_client_profile_id_fk" FOREIGN KEY (rater_id) REFERENCES metro.client_profile(id)
TABLE "metro.client_flags" CONSTRAINT "client_flags_client_id_client_profile_id_fk" FOREIGN KEY (client_id) REFERENCES metro.client_profile(id)
TABLE "metro.client_profile" CONSTRAINT "client_profile_corp_parent_id_client_profile_id_fk" FOREIGN KEY (corp_parent_id) REFERENCES metro.client_profile(id)
TABLE "metro.client_profile" CONSTRAINT "client_profile_parent_id_client_profile_id_fk" FOREIGN KEY (parent_id) REFERENCES metro.client_profile(id)
Foreign table "metro_ft.ft_client_profile"
Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
--------------------+-----------------------------+-------------------------------------------------------------------------+-------------+----------+--------------+-------------
id | integer | not null default nextval('metro_ft.ft_client_profile_id_seq'::regclass) | | plain | |
co_name | character varying(150) | | | extended | |
dba_name | character varying(60) | | | extended | |
addr1 | character varying(60) | | | extended | |
addr2 | character varying(60) | | | extended | |
city | character varying(60) | | | extended | |
state | character(12) | | | extended | |
zip | character(11) | | | extended | |
ctry | character varying(20) | | | extended | |
contact_fname | character varying(30) | | | extended | |
contact_lname | character varying(30) | | | extended | |
contact_title | character varying(30) | | | extended | |
phone | character varying(30) | | | extended | |
fax | character varying(30) | | | extended | |
email | character varying(255) | | | extended | |
bid_email | character varying(255) | | | extended | |
contact_pref | character(1) | | | extended | |
business | character varying(20) | | | extended | |
load_types | character varying(80) | | | extended | |
veh_types | character varying(100) | | | extended | |
num_veh_shipped_mo | smallint | | | plain | |
primary_locs | character varying(60) | | | extended | |
trade_assoc | character varying(60) | | | extended | |
web_site | character varying(60) | | | extended | |
icc_mc | character varying(20) | | | extended | |
ins_agent | character varying(100) | | | extended | |
liability_limits | character varying(100) | | | extended | |
damage_limits | character varying(100) | | | extended | |
deductible | character varying(60) | | | extended | |
status | character(2) | | | extended | |
misc | character varying(200) | | | extended | |
truck_num_type | character varying(100) | | | extended | |
timestamp | timestamp without time zone | | | plain | |
flag_shipper | character(1) | | | extended | |
flag_carrier | character(1) | | | extended | |
flag_priv | character(1) | | | extended | |
username | character varying(12) | | | extended | |
password | character varying(12) | | | extended | |
secret_word | character varying(60) | | | extended | |
cell_phone | character varying(15) | | | extended | |
flag_demouser | character(1) | | | extended | |
cl_key | character(9) | | | extended | |
srch_key | character(6) | | | extended | |
scope | smallint | | | plain | |
scope_def | smallint | | | plain | |
parent_id | integer | | | plain | |
mc | character varying(20) | | | extended | |
mc_broker | character varying(20) | | | extended | |
dot | character varying(20) | | | extended | |
internal_only | character(1) | | | extended | |
flag_broker | character(1) | | | extended | |
bid_typ | character(1) | | | extended | |
credit | character varying(30) | | | extended | |
speed | character varying(30) | | | extended | |
price | numeric | | | main | |
deadline_hours | integer | | | plain | |
deadline_days | integer | | | plain | |
insp_pref | character varying(30) | | | extended | |
flag_cons | character(1) | | | extended | |
contract_rcvd | character(1) | | | extended | |
flag_bid | character(1) | | | extended | |
ins_lia | integer | | | plain | |
ins_cargo | integer | | | plain | |
ins_wcomp | integer | | | plain | |
ins_req_lia | integer | | | plain | |
ins_req_cargo | integer | | | plain | |
ins_req_wcomp | integer | | | plain | |
fed_tax_id | character varying(20) | | | extended | |
parent_client_id | character varying(200) | | | extended | |
ins_exp | date | | | plain | |
phone2 | character varying(20) | | | extended | |
phone3 | character varying(20) | | | extended | |
phone4 | character varying(20) | | | extended | |
location_code | character(20) | | | extended | |
units | character(2) | | | extended | |
corp_parent_id | integer | | | plain | |
is_domain | character(1) | | | extended | |
invoice_email | character varying(255) | | | extended | |
flag_lien_holder | character(1) | | | extended | |
Server: cargotel_tcp
FDW Options: ("table" 'ft_client_profile', database 'metro', client_locale 'en_US.utf8', db_locale 'en_US.819')Informix ft_client_profile table:
----------------------- metro@chisec ----------- Press CTRL-W for Help --------
Column name Type Nulls
id serial no
co_name varchar(150,0) no
dba_name varchar(60,0) yes
addr1 varchar(60,0) yes
addr2 varchar(60,0) yes
city varchar(60,0) yes
state char(12) yes
zip char(11) yes
ctry varchar(20,0) yes
contact_fname varchar(30,0) yes
contact_lname varchar(30,0) yes
contact_title varchar(30,0) yes
phone varchar(30,0) yes
fax varchar(30,0) yes
email varchar(255) yes
bid_email varchar(255) yes
contact_pref char(1) yes
business varchar(20,0) yes
load_types varchar(80,0) yes
veh_types varchar(100,0) yes
num_veh_shipped_mo smallint yes
primary_locs varchar(60,0) yes
trade_assoc varchar(60,0) yes
web_site varchar(60,0) yes
icc_mc varchar(20,0) yes
ins_agent varchar(100,0) yes
liability_limits varchar(100,0) yes
damage_limits varchar(100,0) yes
deductible varchar(60,0) yes
status char(2) yes
misc varchar(200,0) yes
truck_num_type varchar(100,0) yes
timestamp datetime year to minute yes
flag_shipper char(1) yes
flag_carrier char(1) yes
flag_priv char(1) yes
username varchar(12,0) yes
password varchar(12,0) yes
secret_word varchar(60,0) yes
cell_phone varchar(15,0) yes
flag_demouser char(1) yes
cl_key char(9) yes
srch_key char(6) yes
scope smallint yes
scope_def smallint yes
parent_id integer yes
mc varchar(20,0) yes
mc_broker varchar(20,0) yes
dot varchar(20,0) yes
internal_only char(1) yes
flag_broker char(1) yes
bid_typ char(1) yes
credit varchar(30,0) yes
speed varchar(30,0) yes
price float yes
deadline_hours integer yes
deadline_days integer yes
insp_pref varchar(30,0) yes
flag_cons char(1) yes
contract_rcvd char(1) yes
flag_bid char(1) yes
ins_lia integer yes
ins_cargo integer yes
ins_wcomp integer yes
ins_req_lia integer yes
ins_req_cargo integer yes
ins_req_wcomp integer yes
fed_tax_id varchar(20,0) yes
parent_client_id varchar(200,0) yes
ins_exp date yes
phone2 varchar(20,0) yes
phone3 varchar(20,0) yes
phone4 varchar(20,0) yes
location_code char(20) yes
units char(2) yes
corp_parent_id integer yes
is_domain char(1) yes
invoice_email varchar(255) yes
flag_lien_holder char(1) yesJeff,
I finally found some spare time to dig into this, unfortunately this is again something hard to reproduce.
The position of the crash is here (heaptuple.c, line 103):
if (ATT_IS_PACKABLE(att[i]) &&
VARATT_CAN_MAKE_SHORT(DatumGetPointer(val)))
{
/*
* we're anticipating converting to a short varlena header, so
* adjust length and don't count any alignment
*/
data_length += VARATT_CONVERTED_SHORT_SIZE(DatumGetPointer(val));
}which suggests either a corrupted tupDesc structure somehow (where the att[] array is derived from) or an invalid Datum in val. So, to get more information on what's going wrong here, may i ask you, wether you are able to track it down to a specific tuple so that i can reproduce it with those column values?
Oh, and again, what platform and Postgres are you currently on, still Ubuntu and PG 9.4?
rossj-cargotel
commented
8 years ago Hi Bernd,
Sorry for the delay. I'm now primarily running postgres 9.4.4 on CentOS 6.7 and getting the same problem on the EC2 server running 9.4.4 on Ubuntu 14.04.
At this point in time I'm unable to retrieve even a single row from a table if the informix table has more than a two hundred or so rows, especially if the table has a lot of columns.
For example, I can select 1000 rows from this table of 2011390 rows:
postgres@cargotel [local]# \d+ metro_ft.load_flags
Foreign table "metro_ft.load_flags"
Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
------------+-----------------------------+------------------------------------------------------------------+-------------+----------+--------------+-------------
id | integer | not null default nextval('metro_ft.load_flags_id_seq'::regclass) | | plain | |
timestamp | timestamp without time zone | | | plain | |
type | character(1) | | | extended | |
load_id | integer | | | plain | |
carrier_id | integer | | | plain | |
info | character varying(255) | | | extended | |
user_id | integer | | | plain | |
Server: cargotel_tcp
FDW Options: ("table" 'load_flags', database 'metro', client_locale 'en_US.utf8', db_locale 'en_US.819')without crashing the server, albeit pretty slowly.
select * from metro_ft.load_flags order by random() limit 1000;
WARNING: opened informix connection with warnings
DETAIL: informix SQLSTATE 01I01: "Database has transactions "
id | timestamp | type | load_id | carrier_id | info | user_id
---------+---------------------+------+---------+------------+-------------------+---------
371620 | 2012-03-06 11:35:00 | n | 76133 | | 30 | 25149
1559273 | 2014-10-08 08:50:00 | l | 339078 | | | 25187
462692 | 2012-05-14 17:13:00 | c | 91297 | | bm:pm | 25142
2059017 | 2015-08-26 10:24:00 | n | 469239 | | 24 | 25203
273828 | 2011-11-11 09:19:00 | n | 56484 | | 19 | 25081
803940 | 2013-03-22 14:08:00 | n | 156022 | | 08 | 25107
...
Time: 90595.444 msIncidentally, the CentOS postgres server is now inside our lan--ping times are sub-millisecond.
rossj@717311-app1:~$ ping db4.cargotel.com
PING db4.cargotel.com (192.168.100.231) 56(84) bytes of data.
64 bytes from 321533-www2.cargotel.com (192.168.100.231): icmp_seq=1 ttl=64 time=0.124 ms
64 bytes from 321533-www2.cargotel.com (192.168.100.231): icmp_seq=2 ttl=64 time=0.123 ms
64 bytes from 321533-www2.cargotel.com (192.168.100.231): icmp_seq=3 ttl=64 time=0.118 msI can't even select 10 columns from the above referenced client_profile table, though.
postgres@cargotel [local]# select id from metro_ft.client_profile order by id limit 10;
WARNING: opened informix connection with warnings
DETAIL: informix SQLSTATE 01I01: "Database has transactions "
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
Time: 6598.113 msI think there is a real connection with the number of columns in the foreign table but the round trip time to the server doesn't seem to make a difference at all.
postgres@cargotel [local]# select * from metro_ft.load_flags order by id limit 10;
WARNING: opened informix connection with warnings
DETAIL: informix SQLSTATE 01I01: "Database has transactions "
id | timestamp | type | load_id | carrier_id | info | user_id
----+---------------------+------+---------+------------+---------+---------
1 | 2010-05-18 16:07:00 | n | 1 | | 00 | 25070
2 | 2010-05-18 16:07:00 | c | 1 | | bm:pm | 25070
3 | 2010-05-20 09:52:00 | J | 21 | | CREATED | 25073
4 | 2010-05-20 10:03:00 | J | 31 | | CREATED | 25073
5 | 2010-05-26 11:37:00 | l | 43 | | | 25070
6 | 2010-05-26 11:37:00 | l | 44 | | | 25070
7 | 2010-05-26 11:37:00 | l | 45 | | | 25070
8 | 2010-05-26 11:37:00 | S | 43 | | 0,1 | 25070
9 | 2010-05-26 11:37:00 | S | 44 | | 0,1 | 25070
10 | 2010-05-26 11:37:00 | S | 45 | | 0,1 | 25070
(10 rows)
Time: 83364.877 msI hope that helps!
Jeff
psoo
commented
8 years ago Hi Jeff,
Finally some news on this issue:
Commits 62e19825386412d4e62d39d4408d4a213429f950 and 11de42bb0e115c2825b0380cfa3b4e7dbb929503 fix some serious issues when conversion from and to date, float and smallfloat values are involved. Since they also scribbled on memory areas and your examples also contain date and float columns it's possible that they also caused your crashes.
It would be really cool if you could repeat your tests with the committed patches and let me know your findings.
Bernd
rossj-cargotel
commented
8 years ago So far so good!
I've repeated that problem query and quite a few others like it with no problems at all.
I'll continue to hammer on this over the next few days and let you know if anything else pops up.
Thank you!
Jeff
Jeff Ross rossj@cargotel.com SENDPM
On 12/15/15 2:13 PM, Bernd Helmle wrote:
Hi Jeff,
Finally some news on this issue:
Commits 62e1982 https://github.com/credativ/informix_fdw/commit/62e19825386412d4e62d39d4408d4a213429f950 and 11de42b https://github.com/credativ/informix_fdw/commit/11de42bb0e115c2825b0380cfa3b4e7dbb929503 fix some serious issues when conversion from and to date, float and smallfloat values are involved. Since they also scribbled on memory areas and your examples also contain date and float columns it's possible that they also caused your crashes.
It would be really cool if you could repeat your tests with the committed patches and let me know your findings.
Bernd
— Reply to this email directly or view it on GitHub https://github.com/credativ/informix_fdw/issues/8#issuecomment-164898062.
The contents of this e-mail and any attachments are intended solely for the use of the named addressee(s) and may contain confidential and/or privileged information. Any unauthorized use, copying, disclosure, or distribution of the contents of this e-mail is strictly prohibited by the sender and may be unlawful. If you are not the intended recipient, please notify the sender immediately and delete this e-mail.
psoo
commented
8 years ago Many thanks.
psoo
commented
8 years ago Fix seems to address described issue.
Hi Bernd! I can repeatedly crash postgres 9.4.4 with the following query:
Querying informix directly there are only 64 rows that match the where clause. This query used to run if I dropped the subselect and hardcoded the max(id).
Catching a backtrace I see this:
Indeed, there is nothing at all in /tmp. Backtrace is as follows:
Let me know if you need more information!
Jeff