Обсуждение: Segmentation fault when changing view

Vlad Seryakov (vlad@crystalballinc.com) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
Segmentation fault when changing view

Long Description
When i dropped column quantity in view package_tree_view, recreated this view, but didn't recreated the view
package_packages_tree_viewand ran SQL statement below in Example, the server died with segfault. 
Earlier i remember it complaints about missing cache object but didn; crashed.
Thank you


Database schema:

CREATE TABLE usage_rates (
   rate_id VARCHAR(16) NOT NULL CHECK(rate_id != ''),
   rate_name VARCHAR(64) NOT NULL,
   description VARCHAR(255) NULL,
   CONSTRAINT usage_rates_pk PRIMARY KEY(rate_id),
   CONSTRAINT usage_rates_un UNIQUE(rate_name)
);

CREATE TABLE prices (
   price_id INTEGER NOT NULL CHECK(price_id > 0),
   install_price NUMERIC(5,2) DEFAULT 0 NOT NULL,
   periodic_price NUMERIC(5,2) DEFAULT 0 NOT NULL,
   usage_price NUMERIC(5,2) DEFAULT 0 NOT NULL,
   usage_rate VARCHAR(16) NULL
      CONSTRAINT service_usage_rate_fk REFERENCES usage_rates(rate_id),
   termination_price NUMERIC(5,2) DEFAULT 0 NOT NULL,
   CONSTRAINT prices_pk PRIMARY KEY(price_id)
);

CREATE TABLE service_status (
   status_id VARCHAR(16) NOT NULL CHECK(status_id != ''),
   status_name VARCHAR(64) NOT NULL,
   precedence SMALLINT NOT NULL,
   description VARCHAR(255) NULL,
   CONSTRAINT service_status_pk PRIMARY KEY(status_id),
   CONSTRAINT service_status_un UNIQUE(status_name)
);

CREATE TABLE services (
   service_id INTEGER NOT NULL CHECK(service_id > 0),
   service_name VARCHAR(64) NOT NULL,
   service_status VARCHAR(16) NOT NULL
      CONSTRAINT service_status_fk REFERENCES service_status(status_id),
   service_owner INTEGER NULL
      CONSTRAINT service_owner_fk REFERENCES services(service_id),
   description VARCHAR(255) NULL,
   path VARCHAR(255) NULL,
   CONSTRAINT services_pk PRIMARY KEY(service_id),
   CONSTRAINT service_un UNIQUE(service_name),
   CONSTRAINT service_owner_ck CHECK(service_id != service_owner)
);

CREATE TABLE packages (
   package_id INTEGER NOT NULL CHECK(package_id > 0),
   package_name VARCHAR(64) NOT NULL,
   package_status VARCHAR(16) NOT NULL
      CONSTRAINT package_status_fk REFERENCES service_status(status_id),
   start_date DATETIME NOT NULL,
   stop_date DATETIME NOT NULL,
   description VARCHAR(255) NULL,
   install_price NUMERIC(5,2) NULL,
   periodic_price NUMERIC(5,2) NULL,
   termination_price NUMERIC(5,2) NULL,
   CONSTRAINT packages_pk PRIMARY KEY(package_id),
   CONSTRAINT packages_un UNIQUE(package_name)
);

CREATE TABLE package_services (
   package_id INTEGER NOT NULL REFERENCES packages(package_id),
   service_id INTEGER NOT NULL REFERENCES services(service_id),
   quantity SMALLINT DEFAULT 1 NOT NULL,
   description VARCHAR(255) NULL,
   CONSTRAINT package_servies_pk PRIMARY KEY(package_id,service_id)
);
CREATE TABLE package_packages (
   package_id INTEGER NOT NULL
     CONSTRAINT packages_pkg_fk REFERENCES packages(package_id),
   package_owner INTEGER NOT NULL
     CONSTRAINT packages_pkg_owner_fk REFERENCES packages(package_id),
   price_id INTEGER NOT NULL
     CONSTRAINT packages_price_fk REFERENCES prices(price_id),
   CONSTRAINT packages_pkg_pk PRIMARY KEY(package_id,package_owner),
   CONSTRAINT packages_pkg_ck CHECK(package_id != package_owner)
);

CREATE TABLE package_tree (
   path VARCHAR(255) NOT NULL,
   id INTEGER NOT NULL
     CONSTRAINT packages_tree_id_fk REFERENCES packages(package_id),
   owner INTEGER NULL
     CONSTRAINT packages_tree_o_fk REFERENCES packages(package_id),
   tree_level INTEGER NOT NULL,
   leaf_node CHAR(1) DEFAULT 'N' NOT NULL
     CONSTRAINT packages_leaf_ck CHECK(leaf_node IN ('Y','N')),
   path2 VARCHAR(255) NOT NULL,
   CONSTRAINT packages_tree_pk PRIMARY KEY(path)
);

DROP VIEW packages_tree_view;
CREATE VIEW packages_tree_view AS
  SELECT p.package_id,
         p.package_name,
         p.package_status,
         status_name,
         ps.service_id,
         ps.quantity,
         COALESCE(p.description,ps.description) AS description,
         t.path,
         t.owner,
         t.tree_level,
         t.leaf_node,
         s.service_name,
         s.service_status
  FROM packages p,
       service_status,
       package_tree t
       LEFT OUTER JOIN package_services ps ON t.id=ps.package_id
       LEFT OUTER JOIN services s ON ps.service_id=s.service_id
  WHERE t.id=p.package_id AND
        p.package_status=status_id;

DROP VIEW package_packages_tree_view;
CREATE VIEW package_packages_tree_view AS
  SELECT pv.*,
         pr.price_id,
         pr.install_price,
         pr.periodic_price,
         pr.usage_price,
         pr.termination_price
  FROM packages_tree_view pv
       LEFT OUTER JOIN package_packages pp
       ON pv.package_id=pp.package_id AND
          pv.owner=pp.package_owner
       LEFT OUTER JOIN prices pr ON pr.price_id=pp.price_id;

INSERT INTO service_status (status_id,status_name,precedence,description)
            VALUES('planned','Planned Service,',0,'');

INSERT INTO service_status (status_id,status_name,precedence,description)
            VALUES('available','Available to order',1,'');

INSERT INTO service_status (status_id,status_name,precedence,description)
            VALUES('closed','End of Sales',2,'');

INSERT INTO service_status (status_id,status_name,precedence,description)
            VALUES('unsupported','End of Life',2,'');

INSERT INTO "services" VALUES (93,'Big Internet','available',2010995859,'fvfdv','2010
995859/93/');
INSERT INTO "services" VALUES (64,'Big Deal','available',NULL,NULL,'64/');
INSERT INTO "services" VALUES (2010995859,'Internet','available',NULL,NULL,'2010995859/'
);
INSERT INTO "services" VALUES (2010990658,'Phone','available',64,'dcvdc','64/20109906
58/');
INSERT INTO "packages" VALUES (66,'referg','available',now(),now(),'regr',NULL,NULL,NULL);
INSERT INTO "packages" VALUES (70,'test','available',now(),now(),'regre',NULL,NULL,NULL);
INSERT INTO "packages" VALUES (65,'Internet','available',now(),now(),'ttr','4.00','5.00','6.00');
INSERT INTO "packages" VALUES (122,'Phone','available',now(),now(),'rgrege',NULL,NULL,NULL);
INSERT INTO "packages" VALUES (113,'Huge Deal','available',now(),now(),'Super huge and great deal',NULL,NULL,NULL);
INSERT INTO "package_services" VALUES (65,2010995859,1,NULL);
INSERT INTO "package_services" VALUES (70,64,1,NULL);
INSERT INTO "package_services" VALUES (122,2010990658,1,'frgr');
INSERT INTO "package_services" VALUES (122,64,1,NULL);
INSERT INTO "prices" VALUES (72,'0.00','43.00','0.00',NULL,'0.00');
INSERT INTO "prices" VALUES (75,'0.00','4.00','0.00',NULL,'0.00');
INSERT INTO "prices" VALUES (114,'0.00','0.00','0.00',NULL,'0.00');
INSERT INTO "package_packages" VALUES (65,66,72);
INSERT INTO "package_packages" VALUES (65,70,75);
INSERT INTO "package_packages" VALUES (65,113,114);
INSERT INTO "package_tree" VALUES ('/B/C/',65,113,1,'Y','/113/65/');
INSERT INTO "package_tree" VALUES ('/B/',113,NULL,0,'N','/113/');
INSERT INTO "package_tree" VALUES ('/D/',65,NULL,0,'Y','/65/');
INSERT INTO "package_tree" VALUES ('/E/',122,NULL,0,'Y','/122/');
INSERT INTO "package_tree" VALUES ('/F/G/',65,66,1,'Y','/66/65/');
INSERT INTO "package_tree" VALUES ('/F/',66,NULL,0,'N','/66/');
INSERT INTO "package_tree" VALUES ('/H/I/',65,70,1,'Y','/70/65/');
INSERT INTO "package_tree" VALUES ('/H/',70,NULL,0,'N','/70/');


Sample Code
SELECT path,
       package_name,
       package_id,
       service_id,
       service_name,
       tree_level,
       status_name,
       install_price,
       periodic_price,
       termination_price
       FROM package_packages_tree_view
       WHERE path LIKE '/E/%/%'
      ORDER BY path,service_name;


No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> Segmentation fault when changing view

I ran this script and didn't see any problem ...

In general though, whenever you drop/recreate a view you are going to
have to drop/recreate views that refer to it, too.

            regards, tom lane

Vlad Seryakov <vlad@crystalballinc.com> writes:
> Is it possible to get core file, i couldn't find how to setup this.

A crashed backend should leave a core file in $PGDATA/base/YOURDB/core

If you don't see a core file in that directory, it's possible that
the postmaster was started with "ulimit -c 0" to forbid core dumping.
(I think most Linuxen run their boot scripts with this setting.)
Restart the postmaster with "ulimit -c unlimited" to allow core dumping.
You might want to add that command to the boot script for Postgres.

            regards, tom lane

Vlad Seryakov <vlad@crystalballinc.com> writes:
> i just created new database and ran this script.
> First time the query ran okay, then i removed ps.quantity
> and re-created package_tree_view.
> After this the query crashed the server.

Hmm, I see: there's not a defense against references to
no-longer-existing tables/views when the same name has been re-used
for a new table/view.  I've fixed this.  Thanks for the report!

            regards, tom lane