Re: COPY FROM fails to trigger rules - Mailing list pgsql-general

On 5/30/2004 10:16 AM, Bastian Blank wrote:
> I'm using postgresql 7.4.2 and COPY FROM don't trigger INSERT rules.

Right, and it never did. COPY does not pass the query rewrite engine.


Jan

>
> 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
>


--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #