Re: postmaster core ( finally I have it ) [ vacuum pg_rewrite ]

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> "Mendola Gaetano" <mendola(at)bigfoot(dot)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