COPY FROM fails to trigger rules

[Bastian Blank]

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iEYEARECAAYFAkC57MYACgkQnw66O/MvCNFoowCgjaowH5YWuNlNt/vc5bUdOHaO
VVkAoICGkZgbJIjdrwPwbxyYXnILE0Zd
=EFTg
-----END PGP SIGNATURE-----

[Jan Wieck]

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. #
#================================================= = Ja******@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org