Обсуждение: inherited table and rules
This is weird. I have two tables: one inherits from the other. And I
have a
rule that populates the inherited table with changes from the first.
When I
update a row in the first table, I get an ever-larger number of rows
added to
both it and the inherited table. i.e.:
update 1 yields 2 new rows
update 2 yields 6 new rows
update 3 yields 42 new rows
update 4 yields 1806 new rows
I'm clearly doing something wrong ;)
My hope was that on update, a field in the first table would be changed
(leaving the same number of total rows as prior to update). And the
inherited table would have one row added to it per update, reflecting a
"change log" of the updates.
Thanks in advance! Example code follows.
Scott
CREATE TABLE people (
usr_pkey SERIAL PRIMARY KEY,
usr_name text UNIQUE DEFAULT NULL,
color text DEFAULT NULL,
timestamp timestamp DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE people_history (
hist_pkey SERIAL NOT NULL PRIMARY KEY,
hist_tstamp timestamp DEFAULT CURRENT_TIMESTAMP
) INHERITS(people);
CREATE RULE
people_upd_history AS ON UPDATE TO people
DO INSERT INTO
people_history
SELECT * FROM people WHERE usr_pkey = old.usr_pkey;
-- populate table
INSERT INTO people (usr_name, color) VALUES ('bob', 'red');
INSERT INTO people (usr_name, color) VALUES ('carol', 'green');
INSERT INTO people (usr_name, color) VALUES ('ted', 'blue');
-- update table (1) -- 2
UPDATE people SET color = 'cyan' WHERE usr_pkey = 1;
-- update table (2) -- 6
UPDATE people SET color = 'magenta' WHERE usr_pkey = 1;
-- update table (3) -- 42
UPDATE people SET color = 'yellow' WHERE usr_pkey = 1;
-- update table (4) -- 1806
UPDATE people SET color = 'black' WHERE usr_pkey = 1;
On Tue, 22 Mar 2005, Scott Frankel wrote: > > This is weird. I have two tables: one inherits from the other. And I > have a > rule that populates the inherited table with changes from the first. > When I > update a row in the first table, I get an ever-larger number of rows > added to > both it and the inherited table. i.e.: > > update 1 yields 2 new rows > update 2 yields 6 new rows > update 3 yields 42 new rows > update 4 yields 1806 new rows > > I'm clearly doing something wrong ;) I think you need to be using ONLY (or changing the sql_inheritance GUC variable) in all the queries on people in order to not also be getting rows from people_history in the SELECT and UPDATE (and in fact changing the select and update statements to FROM ONLY people seems to work for me).
Syntax troubles.
What is the proper syntax for using FROM ONLY table_name in an UPDATE
statement?
According to the docs, In a FROM clause, I should be able to use the
ONLY keyword
preceding the table name. This throws an error:
UPDATE FROM ONLY people SET color = 'cyan' WHERE usr_pkey = 1;
What is the proper syntax for specifying FROM ONLY in the inheritance
statement?
This also throws an error:
CREATE TABLE people_history (
hist_pkey SERIAL NOT NULL PRIMARY KEY,
hist_tstamp timestamp DEFAULT CURRENT_TIMESTAMP
) INHERITS ONLY (people);
What does GUC stand for? ;)
Thanks!
Scott
On Mar 22, 2005, at 6:55 PM, Stephan Szabo wrote:
>
> On Tue, 22 Mar 2005, Scott Frankel wrote:
>
>>
>> This is weird. I have two tables: one inherits from the other. And
>> I
>> have a
>> rule that populates the inherited table with changes from the first.
>> When I
>> update a row in the first table, I get an ever-larger number of rows
>> added to
>> both it and the inherited table. i.e.:
>>
>> update 1 yields 2 new rows
>> update 2 yields 6 new rows
>> update 3 yields 42 new rows
>> update 4 yields 1806 new rows
>>
>> I'm clearly doing something wrong ;)
>
> I think you need to be using ONLY (or changing the sql_inheritance GUC
> variable) in all the queries on people in order to not also be getting
> rows from people_history in the SELECT and UPDATE (and in fact changing
> the select and update statements to FROM ONLY people seems to work for
> me).
>
Did you happen to look at the manual? http://www.postgresql.org/docs/7.4/static/sql-update.html It pretty clearly indicates that the syntax is UPDATE ONLY table, so try eliminating the FROM in your UPDATE example below. http://www.postgresql.org/docs/7.4/static/sql-createtable.html The CREATE TABLE entry also pretty clearly indicates that ONLY is not allowed in specifying inheritance. All you need is the table name, so try eliminating the ONLY from your CREATE example below. GUC is the Grand Unified Configuration scheme, which was introduced in 7.1. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Mar 22, 2005, at 11:59 PM, Scott Frankel wrote: > Syntax troubles. > > What is the proper syntax for using FROM ONLY table_name in an UPDATE > statement? > According to the docs, In a FROM clause, I should be able to use the > ONLY keyword > preceding the table name. This throws an error: > > UPDATE FROM ONLY people SET color = 'cyan' WHERE usr_pkey = 1; > > What is the proper syntax for specifying FROM ONLY in the inheritance > statement? > This also throws an error: > > CREATE TABLE people_history ( > hist_pkey SERIAL NOT NULL PRIMARY KEY, > hist_tstamp timestamp DEFAULT CURRENT_TIMESTAMP > ) INHERITS ONLY (people); > > What does GUC stand for? ;) > > Thanks! > Scott
On Tue, Mar 22, 2005 at 09:59:23PM -0800, Scott Frankel wrote: > What is the proper syntax for using FROM ONLY table_name in an UPDATE > statement? See the UPDATE documentation: http://www.postgresql.org/docs/8.0/static/sql-update.html > What is the proper syntax for specifying FROM ONLY in the inheritance > statement? What are you trying to do that a simple INHERITS won't do? > What does GUC stand for? ;) Grand Unified Configuration. http://www.postgresql.org/docs/8.0/static/runtime-config.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/
I thought that all rows in inherited tables are visible to the table
that they are inherited from (ie all rows in people_history are visible
to people).
step by step would be
> INSERT INTO people (usr_name, color) VALUES ('bob', 'red');
make one row in people
> -- update table (1) -- 2
> UPDATE people SET color = 'cyan' WHERE usr_pkey = 1;
rule fires inserting new row into people_history making 2 rows visible
in people.
not instead rule so update happens causing 2 rows updated.
> -- update table (2) -- 6
> UPDATE people SET color = 'magenta' WHERE usr_pkey = 1;
rule fires for each row updated making 2 new rows per original visible row
(ends up with 6 rows)
not instead rule so update happens causing 6 rows updated.
> -- update table (3) -- 42
> UPDATE people SET color = 'yellow' WHERE usr_pkey = 1;
6inserts * 6 updates + original 6 = 42
> -- update table (4) -- 1806
> UPDATE people SET color = 'black' WHERE usr_pkey = 1;
42*42+42 = 1806
klint.
+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : kg@kgb.une.edu.au : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---------------------------------------+-----------------+
I think you can get what you want if you change the rule definition to CREATE RULE people_upd_history AS ON UPDATE TO people DO INSERT INTO people_history SELECT * FROM only people WHERE usr_pkey = old.usr_pkey; and your updates to be only's -- update table (1) -- 2 UPDATE ONLY people SET color = 'cyan' WHERE usr_pkey = 1; -- update table (2) -- 6 UPDATE ONLY people SET color = 'magenta' WHERE usr_pkey = 1; -- update table (3) -- 42 UPDATE ONLY people SET color = 'yellow' WHERE usr_pkey = 1; -- update table (4) -- 1806 UPDATE ONLY people SET color = 'black' WHERE usr_pkey = 1; klint. +---------------------------------------+-----------------+ : Klint Gore : "Non rhyming : : EMail : kg@kgb.une.edu.au : slang - the : : Snail : A.B.R.I. : possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---------------------------------------+-----------------+
On Tue, 22 Mar 2005, Scott Frankel wrote: > Syntax troubles. > > What is the proper syntax for using FROM ONLY table_name in an UPDATE > statement? According to the docs, In a FROM clause, I should be able to > use the ONLY keyword preceding the table name. This throws an error: > > UPDATE FROM ONLY people SET color = 'cyan' WHERE usr_pkey = 1; It's actually UPDATE ONLY people (I was using the select version in my message as a shorthand, sorry). > What is the proper syntax for specifying FROM ONLY in the inheritance > statement? You don't need to do it in the inheritance clause, and I'm not sure what it would do if it were allowed. > This also throws an error: > > CREATE TABLE people_history ( > hist_pkey SERIAL NOT NULL PRIMARY KEY, > hist_tstamp timestamp DEFAULT CURRENT_TIMESTAMP > ) INHERITS ONLY (people); > > What does GUC stand for? ;) I think it's like grand unified configuration. It's the configuration variables in the conf file and SETs and so on.
Close. Thanks for the very helpful suggestions!
As I read the doco on rules and dissect the rule I've constructed, one
issue
remains: the UPDATE in my rule causes additional rows to be added to
the parent table. How is that possible? How can it be suppressed?
i.e.: My rule specifies that when the parent table is updated, the
inherited table
receives an INSERT. There is nothing that I see that explicitly calls
for a new
row to be added to the parent table.
I've tried fiddling with INSTEAD; but my attempts haven't yielded the
results
I'm looking for. (Though the rule docs are quite opaque on the subect
...)
Thanks again!
Scott
Here's what my sample code (below) yields:
cs_test=# SELECT * FROM people;
usr_pkey | usr_name | color | timestamp
----------+----------+---------+----------------------------
2 | carol | green | 2005-03-23 11:12:49.627183
3 | ted | blue | 2005-03-23 11:12:49.637483
1 | bob | black | 2005-03-23 11:12:49.616602
1 | bob | red | 2005-03-23 11:12:49.616602
1 | bob | cyan | 2005-03-23 11:12:49.616602
1 | bob | magenta | 2005-03-23 11:12:49.616602
1 | bob | yellow | 2005-03-23 11:12:49.616602
(7 rows)
cs_test=# SELECT * FROM people_history;
usr_pkey | usr_name | color | timestamp | hist_pkey
| hist_tstamp
----------+----------+---------+----------------------------
+-----------+----------------------------
1 | bob | red | 2005-03-23 11:12:49.616602 | 1
| 2005-03-23 11:13:17.04928
1 | bob | cyan | 2005-03-23 11:12:49.616602 | 2
| 2005-03-23 11:22:21.374629
1 | bob | magenta | 2005-03-23 11:12:49.616602 | 3
| 2005-03-23 11:23:49.253014
1 | bob | yellow | 2005-03-23 11:12:49.616602 | 4
| 2005-03-23 11:23:53.924315
(4 rows)
Here's what I'm looking for:
cs_test=# SELECT * FROM people;
usr_pkey | usr_name | color | timestamp
----------+----------+---------+----------------------------
2 | carol | green | 2005-03-23 11:12:49.627183
3 | ted | blue | 2005-03-23 11:12:49.637483
1 | bob | black | 2005-03-23 11:12:49.616602
(3 rows)
cs_test=# SELECT * FROM people_history;
usr_pkey | usr_name | color | timestamp | hist_pkey
| hist_tstamp
----------+----------+---------+----------------------------
+-----------+----------------------------
1 | bob | red | 2005-03-23 11:12:49.616602 | 1
| 2005-03-23 11:13:17.04928
1 | bob | cyan | 2005-03-23 11:12:49.616602 | 2
| 2005-03-23 11:22:21.374629
1 | bob | magenta | 2005-03-23 11:12:49.616602 | 3
| 2005-03-23 11:23:49.253014
1 | bob | yellow | 2005-03-23 11:12:49.616602 | 4
| 2005-03-23 11:23:53.924315
(4 rows)
sample code:
CREATE TABLE people (
usr_pkey SERIAL PRIMARY KEY,
usr_name text UNIQUE DEFAULT NULL,
color text DEFAULT NULL,
timestamp timestamp DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE people_history (
hist_pkey SERIAL NOT NULL PRIMARY KEY,
hist_tstamp timestamp DEFAULT CURRENT_TIMESTAMP
) INHERITS (people);
CREATE RULE
people_upd_history AS ON UPDATE TO people
DO INSERT INTO
people_history
SELECT * FROM ONLY people WHERE usr_pkey = old.usr_pkey;
-- populate table
INSERT INTO people (usr_name, color) VALUES ('bob', 'red');
INSERT INTO people (usr_name, color) VALUES ('carol', 'green');
INSERT INTO people (usr_name, color) VALUES ('ted', 'blue');
-- update table (1)
UPDATE ONLY people SET color = 'cyan' WHERE usr_pkey = 1;
-- update table (2)
UPDATE ONLY people SET color = 'magenta' WHERE usr_pkey = 1;
-- update table (3)
UPDATE ONLY people SET color = 'yellow' WHERE usr_pkey = 1;
-- update table (4)
UPDATE ONLY people SET color = 'black' WHERE usr_pkey = 1;
try select * from ONLY people.
also check out this query
select relname,people.* from people join pg_class on people.tableoid=pg_class.oid;
and
select relname,people.* from ONLY people join pg_class on people.tableoid=pg_class.oid;
Jim
---------- Original Message -----------
From: Scott Frankel <leknarf@pacbell.net>
To: pgsql-general@postgresql.org
Sent: Wed, 23 Mar 2005 11:48:46 -0800
Subject: Re: [GENERAL] inherited table and rules
> Close. Thanks for the very helpful suggestions!
>
> As I read the doco on rules and dissect the rule I've constructed, one
> issue
> remains: the UPDATE in my rule causes additional rows to be added to
> the parent table. How is that possible? How can it be suppressed?
>
> i.e.: My rule specifies that when the parent table is updated, the
> inherited table
> receives an INSERT. There is nothing that I see that explicitly calls
> for a new
> row to be added to the parent table.
>
> I've tried fiddling with INSTEAD; but my attempts haven't yielded the
> results
> I'm looking for. (Though the rule docs are quite opaque on the subect
> ...)
>
> Thanks again!
> Scott
>
> Here's what my sample code (below) yields:
>
> cs_test=# SELECT * FROM people;
> usr_pkey | usr_name | color | timestamp
> ----------+----------+---------+----------------------------
> 2 | carol | green | 2005-03-23 11:12:49.627183
> 3 | ted | blue | 2005-03-23 11:12:49.637483
> 1 | bob | black | 2005-03-23 11:12:49.616602
> 1 | bob | red | 2005-03-23 11:12:49.616602
> 1 | bob | cyan | 2005-03-23 11:12:49.616602
> 1 | bob | magenta | 2005-03-23 11:12:49.616602
> 1 | bob | yellow | 2005-03-23 11:12:49.616602
> (7 rows)
>
> cs_test=# SELECT * FROM people_history;
> usr_pkey | usr_name | color | timestamp | hist_pkey
> | hist_tstamp
> ----------+----------+---------+----------------------------
> +-----------+----------------------------
> 1 | bob | red | 2005-03-23 11:12:49.616602 | 1
> | 2005-03-23 11:13:17.04928
> 1 | bob | cyan | 2005-03-23 11:12:49.616602 | 2
> | 2005-03-23 11:22:21.374629
> 1 | bob | magenta | 2005-03-23 11:12:49.616602 | 3
> | 2005-03-23 11:23:49.253014
> 1 | bob | yellow | 2005-03-23 11:12:49.616602 | 4
> | 2005-03-23 11:23:53.924315
> (4 rows)
>
> Here's what I'm looking for:
>
> cs_test=# SELECT * FROM people;
> usr_pkey | usr_name | color | timestamp
> ----------+----------+---------+----------------------------
> 2 | carol | green | 2005-03-23 11:12:49.627183
> 3 | ted | blue | 2005-03-23 11:12:49.637483
> 1 | bob | black | 2005-03-23 11:12:49.616602
> (3 rows)
>
> cs_test=# SELECT * FROM people_history;
> usr_pkey | usr_name | color | timestamp | hist_pkey
> | hist_tstamp
> ----------+----------+---------+----------------------------
> +-----------+----------------------------
> 1 | bob | red | 2005-03-23 11:12:49.616602 | 1
> | 2005-03-23 11:13:17.04928
> 1 | bob | cyan | 2005-03-23 11:12:49.616602 | 2
> | 2005-03-23 11:22:21.374629
> 1 | bob | magenta | 2005-03-23 11:12:49.616602 | 3
> | 2005-03-23 11:23:49.253014
> 1 | bob | yellow | 2005-03-23 11:12:49.616602 | 4
> | 2005-03-23 11:23:53.924315
> (4 rows)
>
> sample code:
>
> CREATE TABLE people (
> usr_pkey SERIAL PRIMARY KEY,
> usr_name text UNIQUE DEFAULT NULL,
> color text DEFAULT NULL,
> timestamp timestamp DEFAULT CURRENT_TIMESTAMP
> );
>
> CREATE TABLE people_history (
> hist_pkey SERIAL NOT NULL PRIMARY KEY,
> hist_tstamp timestamp DEFAULT CURRENT_TIMESTAMP
> ) INHERITS (people);
>
> CREATE RULE
> people_upd_history AS ON UPDATE TO people
> DO INSERT INTO
> people_history
> SELECT * FROM ONLY people WHERE usr_pkey = old.usr_pkey;
>
> -- populate table
> INSERT INTO people (usr_name, color) VALUES ('bob', 'red');
> INSERT INTO people (usr_name, color) VALUES ('carol', 'green');
> INSERT INTO people (usr_name, color) VALUES ('ted', 'blue');
>
> -- update table (1)
> UPDATE ONLY people SET color = 'cyan' WHERE usr_pkey = 1;
>
> -- update table (2)
> UPDATE ONLY people SET color = 'magenta' WHERE usr_pkey = 1;
>
> -- update table (3)
> UPDATE ONLY people SET color = 'yellow' WHERE usr_pkey = 1;
>
> -- update table (4)
> UPDATE ONLY people SET color = 'black' WHERE usr_pkey = 1;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
------- End of Original Message -------
On Wed, 23 Mar 2005 11:48:46 -0800, Scott Frankel <leknarf@pacbell.net> wrote: > > Close. Thanks for the very helpful suggestions! > > As I read the doco on rules and dissect the rule I've constructed, one > issue > remains: the UPDATE in my rule causes additional rows to be added to > the parent table. How is that possible? How can it be suppressed? Rows inserted into inherited tables are visible to the parent. It's effectively the same as having a union all on the 2 tables. Using the only qualifier is how you stop the "union" happening. > Here's what my sample code (below) yields: > > cs_test=# SELECT * FROM people; you need to put the only on this query. Do you really want inheritance or do you just need an table with the same/similar structure? Maybe people_history should use like instead of inherits. klint. +---------------------------------------+-----------------+ : Klint Gore : "Non rhyming : : EMail : kg@kgb.une.edu.au : slang - the : : Snail : A.B.R.I. : possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---------------------------------------+-----------------+
On Mar 23, 2005, at 2:42 PM, Klint Gore wrote: > > Rows inserted into inherited tables are visible to the parent. It's > effectively the same as having a union all on the 2 tables. Using the > only qualifier is how you stop the "union" happening. This explains it. Thanks! Scott