Re: postmaster core ( finally I have it ) [ vacuum pg_rewrite ] - Mailing list pgsql-hackers

"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,
CASEWHEN p.auto_listen             THEN 1             ELSE 0        END                     AS auto_listen,        CASE
WHENp.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
ASaddress,        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
ASid_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_ipAS multicast_ip
 
 FROM publishers p JOIN v_pids v USING (id_pid)                   JOIN addresses a USING (id_publisher) WHERE
id_publisher<>0ORDER 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
abnormallyand possibly corrupted shared memory.       I have rolled back the current transaction and am       going to
terminateyour 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
whileprocessing the request.
 
connection to server was lost
vacuumdb: vacuum  empdb failed




Thank you
Gaetano