postmaster core ( finally I have it )

"Mendola Gaetano" <mendola@bigfoot.com> writes:

The process killed made always the same select ( with different
id_package ):

SELECT id_publisher, publisher_name, id_package, package_name
FROM v_psl_package_info
WHERE id_package = 177;

(gdb) where
#0 0x08171fdd in RelationBuildRuleLock ()
#1 0x081722ab in RelationBuildDesc ()
#2 0x0817311c in RelationIdGetRelation ()
#3 0x08077b95 in relation_open ()
#4 0x08077d24 in heap_openrv ()
#5 0x080b1015 in addRangeTableEntry ()
#6 0x080ab6de in transformTableEntry ()
#7 0x080ab942 in transformFromClauseItem ()
#8 0x080ab321 in transformFromClause ()
#9 0x0809f639 in transformSelectStmt ()
#10 0x0809dcc4 in transformStmt ()
#11 0x0809da06 in parse_analyze ()

Hmm. RelationBuildRuleLock is concerned with reading the rule
information for a table; specifically, it's evidently trying to
read the rules for v_psl_package_info. I suspect some form of
data corruption in the pg_rewrite row(s) for this table. Do you
see any misbehavior when you do

select * from pg_rewrite where ev_class = 'v_psl_package_info'::regclass

In particular, are any of the columns NULL in these rows?

regards, tom lane

From: "Tom Lane" <tgl@sss.pgh.pa.us>

"Mendola Gaetano" <mendola@bigfoot.com> writes:

The process killed made always the same select ( with different
id_package ):

SELECT id_publisher, publisher_name, id_package, package_name
FROM v_psl_package_info
WHERE id_package = 177;

(gdb) where
#0 0x08171fdd in RelationBuildRuleLock ()
#1 0x081722ab in RelationBuildDesc ()
#2 0x0817311c in RelationIdGetRelation ()
#3 0x08077b95 in relation_open ()
#4 0x08077d24 in heap_openrv ()
#5 0x080b1015 in addRangeTableEntry ()
#6 0x080ab6de in transformTableEntry ()
#7 0x080ab942 in transformFromClauseItem ()
#8 0x080ab321 in transformFromClause ()
#9 0x0809f639 in transformSelectStmt ()
#10 0x0809dcc4 in transformStmt ()
#11 0x0809da06 in parse_analyze ()

Hmm. RelationBuildRuleLock is concerned with reading the rule
information for a table; specifically, it's evidently trying to
read the rules for v_psl_package_info. I suspect some form of
data corruption in the pg_rewrite row(s) for this table. Do you
see any misbehavior when you do

select * from pg_rewrite where ev_class = 'v_psl_package_info'::regclass

All seems good.

In particular, are any of the columns NULL in these rows?

I did:
select *
from pg_rewrite
where ev_class = 'v_psl_package_info'::regclass and
( rulename is null or
ev_class is null or
ev_attr is null or
ev_type is null or
is_instead is null or
ev_qual is null or
ev_action is null );

and I had: No rows.

v_psl_package_info is a view, shall we look in another direction ?

thank you
Gaetano

"Mendola Gaetano" <mendola@bigfoot.com> writes:

From: "Tom Lane" <tgl@sss.pgh.pa.us>

I suspect some form of
data corruption in the pg_rewrite row(s) for this table. Do you
see any misbehavior when you do

select * from pg_rewrite where ev_class = 'v_psl_package_info'::regclass

All seems good.

I really don't see any other explanation for a crash in that routine
than problems with the pg_rewrite data.

Do you get this any time you try to do something with the
v_psl_package_info view, or is it a once-in-a-while problem?
If the latter, I wonder whether it could be an issue with a
flaky disk sector ...

regards, tom lane

From: "Tom Lane" <tgl@sss.pgh.pa.us>

"Mendola Gaetano" <mendola@bigfoot.com> writes:

From: "Tom Lane" <tgl@sss.pgh.pa.us>

I suspect some form of
data corruption in the pg_rewrite row(s) for this table. Do you
see any misbehavior when you do

select * from pg_rewrite where ev_class =

'v_psl_package_info'::regclass

All seems good.

I really don't see any other explanation for a crash in that routine
than problems with the pg_rewrite data.

Do you get this any time you try to do something with the
v_psl_package_info view, or is it a once-in-a-while problem?
If the latter, I wonder whether it could be an issue with a
flaky disk sector ...

Is once-in-a-while but always at 00 minutes. This select is performed each
20 minutes and
the core happen always at 00 never at 20 and never at 40!

I have scheduled also a vacuumdb -z each 15 minutes so only at
00 these are running together.

I had another core at 21:00 today and this is the log of the vacuum
( there was that error just during the pg_rewrite vacuum!):

INFO: --Relation pg_catalog.pg_description--
INFO: Pages 12: Changed 0, Empty 0; Tup 1390: Vac 0, Keep 0, UnUsed 1.
Total CPU 0.00s/0.00u sec elapsed 0.20 sec.
INFO: --Relation pg_toast.pg_toast_16416--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: Analyzing pg_catalog.pg_description
INFO: --Relation pg_catalog.pg_group--
INFO: Pages 1: Changed 0, Empty 0; Tup 3: Vac 0, Keep 0, UnUsed 9.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_1261--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: Analyzing pg_catalog.pg_group
INFO: --Relation pg_catalog.pg_proc--
INFO: Pages 111: Changed 0, Empty 0; Tup 1822: Vac 0, Keep 0, UnUsed 272.
Total CPU 0.02s/0.00u sec elapsed 0.29 sec.
INFO: --Relation pg_toast.pg_toast_1255--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Analyzing pg_catalog.pg_proc
INFO: --Relation pg_catalog.pg_rewrite--
INFO: Pages 47: Changed 0, Empty 0; Tup 231: Vac 0, Keep 0, UnUsed 74.
Total CPU 0.00s/0.00u sec elapsed 0.07 sec.
INFO: --Relation pg_toast.pg_toast_16410--
INFO: Pages 22: Changed 0, Empty 0; Tup 77: Vac 0, Keep 0, UnUsed 33.
Total CPU 0.00s/0.00u sec elapsed 0.03 sec.
INFO: Analyzing pg_catalog.pg_rewrite
WARNING: Message from PostgreSQL backend:
The Postmaster has informed me that some other backend
died abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am
going to terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
vacuumdb: vacuum empdb failed

I think we are near the solution now.

Thank you
Gaetano

"Mendola Gaetano" <mendola@bigfoot.com> writes:

Is once-in-a-while but always at 00 minutes. This select is performed each
20 minutes and
the core happen always at 00 never at 20 and never at 40!

Now that is very interesting ... why would that be?

Could we see the definition of this view?

I had another core at 21:00 today and this is the log of the vacuum
( there was that error just during the pg_rewrite vacuum!):

I think that's just coincidence.

regards, tom lane

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

"Mendola Gaetano" <mendola@bigfoot.com> writes:

Is once-in-a-while but always at 00 minutes. This select is performed

each

20 minutes and
the core happen always at 00 never at 20 and never at 40!

Now that is very interesting ... why would that be?

Could we see the definition of this view?

Of course here it is:

CREATE OR REPLACE VIEW v_psl_package_info AS
SELECT pb.id_publisher AS id_publisher,
pb.name AS publisher_name,
pk.id_package AS id_package,
pk.name AS package_name

FROM v_packages pk JOIN v_publishers pb USING (id_publisher);

CREATE OR REPLACE VIEW v_packages AS
SELECT p.id_package AS id_package,
p.id_publisher AS id_publisher,
p.name AS name,
p.information AS information,
p.description AS description,
sp_lookup_key('package_type', p.id_package_type)
AS TYPE,
sp_lookup_key('target', p.id_target)
AS target,
p.port AS port,
p.priority AS priority,
sp_lookup_key('fec', p.id_fec)
AS fec,
p.input_group AS input_group,
p.output_group AS output_group,
CASE WHEN p.updatable
THEN 1
ELSE 0
END AS updatable,
p.checksum AS checksum,
p.version AS version,
p.start_file AS start_file,
p.view_target_group AS view_target_group,
p.target_group AS target_group,
CASE WHEN p.auto_listen
THEN 1
ELSE 0
END AS auto_listen,
CASE WHEN p.public_flag
THEN 1
ELSE 0
END AS public_flag,
p.needed_version AS needed_version,
p.logic_version AS logic_version,
p.package_size AS package_size,
ps.id_drm_process AS id_drm_process,
ps.id_cas_service AS id_cas_service,
ps.id_cas_settings AS id_cas_settings,
ps.id_drm_service AS id_drm_service

FROM packages p LEFT OUTER JOIN package_security ps USING (id_package)
ORDER BY p.id_publisher, p.name;

CREATE OR REPLACE VIEW v_publishers AS
SELECT p.id_publisher AS id_publisher,
p.login AS login,
p.password AS password,
p.ftp_password AS ftp_password,
p.name AS name,
p.address AS address,
p.city AS city,
sp_lookup_descr('country', p.id_country)
AS country,
p.zip AS zip,
p.phone_number AS phone_number,
p.fax_number AS fax_number,
p.email AS email,
p.web_site AS web_site,
p.description AS description,
v.id_pid AS id_pid,
v.id_transponder AS id_transponder,
v.transponder AS transponder,
v.pid AS pid,
v.satellite AS satellite,
v.frequency AS downlink_freq,
v.polarization AS polarization,
v.fec AS fec,
v.symbol_rate AS symbol_rate,
v.description AS txp_description,
a.multicast_ip AS multicast_ip

FROM publishers p JOIN v_pids v USING (id_pid)
JOIN addresses a USING (id_publisher)
WHERE id_publisher<>0
ORDER BY p.name;

CREATE OR REPLACE VIEW v_pids AS
SELECT p.id_pid AS id_pid,
t.id_transponder AS id_transponder,
t.name AS transponder,
p.pid AS pid,
sp_lookup_descr('orbital_ptn', t.orbital_pos)
AS satellite,
t.frequency AS frequency,
t.polarization AS polarization,
t.fec AS fec,
t.symbol_rate AS symbol_rate,
t.description AS description

FROM pids p JOIN transponders t USING (id_transponder);

and the code for the function sp_lookup_key:

CREATE OR REPLACE FUNCTION sp_lookup_key ( TEXT,INTEGER )
RETURNS TEXT AS'
DECLARE
lookup_name ALIAS FOR $1;
my_id_key ALIAS FOR $2;
my_id_lookup INTEGER;
my_key TEXT;
BEGIN

SELECT INTO my_id_lookup id_lookup
FROM v_lookup_tables
WHERE name = lookup_name::varchar;

IF NOT FOUND THEN
RETURN NULL;
END IF;

SELECT INTO my_key key
FROM lookup_tables
WHERE id_table = my_id_lookup
AND id_key = my_id_key;

IF NOT FOUND THEN
RETURN NULL;
END IF;

RETURN my_key;

END;
' LANGUAGE 'plpgsql'
WITH ( iscachable );

I had another core at 21:00 today and this is the log of the vacuum
( there was that error just during the pg_rewrite vacuum!):

I think that's just coincidence.

regards, tom lane

May be was coincidence, I'm going to avoid the vacuum and that select
concurreny and see what happen in these next days.

Just to add some others information,
this is what happens 22 Jul at 20:00
( this time was during the pg_attribute vacuum)

INFO: --Relation pg_catalog.pg_description--
INFO: Pages 12: Changed 0, Empty 0; Tup 1390: Vac 0, Keep 0, UnUsed 1.
Total CPU 0.01s/0.00u sec elapsed 0.03 sec.
INFO: --Relation pg_toast.pg_toast_16416--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Analyzing pg_catalog.pg_description
INFO: --Relation pg_catalog.pg_group--
INFO: Pages 1: Changed 0, Empty 0; Tup 3: Vac 0, Keep 0, UnUsed 9.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_1261--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Analyzing pg_catalog.pg_group
INFO: --Relation pg_catalog.pg_proc--
INFO: Pages 110: Changed 0, Empty 0; Tup 1821: Vac 0, Keep 0, UnUsed 269.
Total CPU 0.01s/0.00u sec elapsed 0.22 sec.
INFO: --Relation pg_toast.pg_toast_1255--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Analyzing pg_catalog.pg_proc
INFO: --Relation pg_catalog.pg_rewrite--
INFO: Pages 47: Changed 0, Empty 0; Tup 231: Vac 0, Keep 0, UnUsed 73.
Total CPU 0.01s/0.00u sec elapsed 0.18 sec.
INFO: --Relation pg_toast.pg_toast_16410--
INFO: Pages 22: Changed 0, Empty 0; Tup 77: Vac 0, Keep 0, UnUsed 33.
Total CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO: Analyzing pg_catalog.pg_rewrite
INFO: --Relation pg_catalog.pg_type--
INFO: Pages 10: Changed 0, Empty 0; Tup 523: Vac 0, Keep 0, UnUsed 27.
Total CPU 0.00s/0.00u sec elapsed 0.03 sec.
INFO: Analyzing pg_catalog.pg_type
INFO: --Relation pg_catalog.pg_attribute--
WARNING: Message from PostgreSQL backend:
The Postmaster has informed me that some other backend
died abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am
going to terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
vacuumdb: vacuum empdb failed

Thank you
Gaetano