Skip site navigation (1) Skip section navigation (2)

COPY FROM fails to trigger rules

From: Bastian Blank <bastian(at)waldi(dot)eu(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: COPY FROM fails to trigger rules
Date: 2004-05-30 14:16:38
Message-ID: 20040530141638.GA16049@wavehammer.waldi.eu.org (view raw or flat)
Thread:
Lists: pgsql-general
I'm using postgresql 7.4.2 and COPY FROM don't trigger INSERT rules.

Definitions: 
| CREATE TABLE log.package_status (
|     version integer NOT NULL,
|     architecture integer NOT NULL,
|     distribution integer NOT NULL,
|     status_old integer,
|     time timestamp NOT NULL,
|     UNIQUE (version, architecture, distribution, time)
| );
| 
| CREATE TABLE package.status (
|     version integer NOT NULL,
|     architecture integer NOT NULL,
|     distribution integer NOT NULL,
|     status integer,
|     UNIQUE (version, architecture, distribution)
| );
| 
| ALTER TABLE ONLY package.status
|     ADD CONSTRAINT version FOREIGN KEY (version) REFERENCES package.version
|     ON DELETE CASCADE;
| 
| ALTER TABLE ONLY package.status
|     ADD CONSTRAINT architecture FOREIGN KEY (architecture) REFERENCES def.architecture;
| 
| ALTER TABLE ONLY package.status
|     ADD CONSTRAINT distribution FOREIGN KEY (distribution) REFERENCES def.distribution;
| 
| ALTER TABLE ONLY package.status
|     ADD CONSTRAINT status FOREIGN KEY (status) REFERENCES def.package_status;
| 
| CREATE INDEX architecture_distribution_index ON package.status (architecture, distribution);
| 
| CREATE RULE status_insert AS ON INSERT TO package.status
|     DO INSERT INTO log.package_status (version, architecture, distribution, time)
|     VALUES (NEW.version, NEW.architecture, NEW.distribution, current_timestamp);
| 
| CREATE RULE status_update AS ON UPDATE TO package.status
|     WHERE NEW.status <> OLD.status
|     DO INSERT INTO log.package_status (version, architecture, distribution, status_old, time)
|     VALUES (NEW.version, NEW.architecture, NEW.distribution, OLD.status, current_timestamp);

The data is inserted via the following call into an mostly empty database:
| COPY package.status (version, architecture, distribution, status) FROM STDIN

It was created from a template which contains a few functions and
operators.

After all data is commited I get the following, it is reproducable:
| multibuild=> SELECT  count(*) from package.status;
|  count
| -------
|  15130
| (1 row)
| 
| multibuild=> SELECT  count(*) from log.package_status;
|  count
| -------
|      0
| (1 row)

Tests with INSERT always triggers the rule.

Bastian

-- 
... The prejudices people feel about each other disappear when they get
to know each other.
		-- Kirk, "Elaan of Troyius", stardate 4372.5

Responses

pgsql-general by date

Next:From: Jurgen DefurneDate: 2004-05-30 14:56:38
Subject: Re: This is another testmail
Previous:From: Chris GamacheDate: 2004-05-30 14:02:28
Subject: Re: turn WAL off.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group