Re: [BUGS] attislocal value changed with the dump
elein <[EMAIL PROTECTED]> writes:
> This is the repro with the sql file below.
I looked into this, and the answer is you're doing it to yourself;
you shouldn't be explicitly re-specifying the defaults for the child
columns.
> create table answer_numeric (
> avalue numeric
> ) inherits (answer_values) ;
> alter table answer_numeric alter column avid set default
> nextval('answer_values_avid_seq');
It's unnecessary to have that "alter column set default" command,
because avid will have inherited the default expression from the parent
anyway. The reason that setting it changes pg_dump's output is that
what you are setting is not quite right: the actual default expression
in the parent is
nextval('public.answer_values_avid_seq')
Since that's different, pg_dump concludes that the child's default is
non-inherited and emits a redefinition of the column.
The reason I didn't see the same behavior in CVS tip is that now that
we use regclass literals for nextval() arguments, the changed default
still lists out the same way as the parent's default, and so pg_dump
thinks it's an inherited default.
It strikes me that there is still a risk here, which is that because
listing of regclass values is search-path-sensitive, pg_dump could
come to the wrong conclusion about the inheritance of a default when
the child is in a different schema than the parent. We could probably
fix that by comparing adbin strings instead of the reverse-compiled
expressions to decide if a child default matches its parent or not.
Alternatively, maybe we should add explicit inheritance information
to pg_attrdef. There's already a proposal to do that for constraints...
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[BUGS] create OR REPLACE rule bug
I believe this is a bug: -- OR REPLACE present create or replace rule "mastertab_jan05_update" as on update to mastertab where (NEW.datecol < '20050101' or NEW.datecol >= '20050201') and (OLD.datecol >= '20050101' and OLD.datecol < '20050201') do instead ( -- multiple commands delete from mastertab_jan05 where id = OLD.id; insert into mastertab (id, datecol) values (OLD.id, NEW.datecol); ); => ERROR: syntax error at end of input at character 255 -- OR REPLACE not present create rule "mastertab_jan05_update" as on update to mastertab where (NEW.datecol < '20050101' or NEW.datecol >= '20050201') and (OLD.datecol >= '20050101' and OLD.datecol < '20050201') do instead ( -- multiple commands delete from mastertab_jan05 where id = OLD.id; insert into mastertab (id, datecol) values (OLD.id, NEW.datecol); ); => Query OK, 0 rows affected (0,00 sec) -- OR REPLACE present create or replace rule "mastertab_jan05_update" as on update to mastertab where (NEW.datecol < '20050101' or NEW.datecol >= '20050201') and (OLD.datecol >= '20050101' and OLD.datecol < '20050201') do instead ( -- single command, no semicolon delete from mastertab_jan05 where id = OLD.id ); => Query OK, 0 rows affected (0,00 sec) -- OR REPLACE present create or replace rule "mastertab_jan05_update" as on update to mastertab where (NEW.datecol < '20050101' or NEW.datecol >= '20050201') and (OLD.datecol >= '20050101' and OLD.datecol < '20050201') do instead ( -- single command, semicolon present delete from mastertab_jan05 where id = OLD.id; ); => ERROR: syntax error at end of input at character 255 Ie, the "OR REPLACE" token is broken in that is doesn't work with multiple commands (semicolons?) /Mikael ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] create OR REPLACE rule bug
Forget that, is seems as a EMS PostgreSQL Manager bug (no problem creating the
rule when executed from the pgAdmin III Query tool)
However, the rule does not work as expected (but I have been warned, see
http://archives.postgresql.org/pgsql-general/2005-10/msg01902.php)
insert into parttest.mastertab (id, datecol)
values (nextval('mastertab_id_seq'), '20050101');
insert into parttest.mastertab (id, datecol)
values (nextval('mastertab_id_seq'), '20050201');
SELECT p.relname, m.id, m.datecol
FROM mastertab m, pg_class p
WHERE m.tableoid = p.oid
ORDER BY id;
relname|id|datecol
mastertab_jan05|1|2005-01-01
mastertab_feb05|2|2005-02-01
update mastertab set datecol = '20050228' where id = 1;
SELECT p.relname, m.id, m.datecol
FROM mastertab m, pg_class p
WHERE m.tableoid = p.oid
ORDER BY id;
relname|id|datecol
mastertab_feb05|2|2005-02-01
(row with id 1 was deleted but not re-inserted)
/Mikael
-Original Message-
From: Mikael Carneholm
Sent: den 5 november 2005 23:05
To: 'pgsql-bugs@postgresql.org'
Subject: create OR REPLACE rule bug
I believe this is a bug:
-- OR REPLACE present
create or replace rule "mastertab_jan05_update" as
on update to mastertab where
(NEW.datecol < '20050101' or NEW.datecol >= '20050201')
and
(OLD.datecol >= '20050101' and OLD.datecol < '20050201')
do instead (
-- multiple commands
delete from mastertab_jan05 where id = OLD.id;
insert into mastertab (id, datecol) values (OLD.id, NEW.datecol);
);
=> ERROR: syntax error at end of input at character 255
-- OR REPLACE not present
create rule "mastertab_jan05_update" as
on update to mastertab where
(NEW.datecol < '20050101' or NEW.datecol >= '20050201')
and
(OLD.datecol >= '20050101' and OLD.datecol < '20050201')
do instead (
-- multiple commands
delete from mastertab_jan05 where id = OLD.id;
insert into mastertab (id, datecol) values (OLD.id, NEW.datecol);
);
=> Query OK, 0 rows affected (0,00 sec)
-- OR REPLACE present
create or replace rule "mastertab_jan05_update" as
on update to mastertab where
(NEW.datecol < '20050101' or NEW.datecol >= '20050201')
and
(OLD.datecol >= '20050101' and OLD.datecol < '20050201')
do instead (
-- single command, no semicolon
delete from mastertab_jan05 where id = OLD.id
);
=> Query OK, 0 rows affected (0,00 sec)
-- OR REPLACE present
create or replace rule "mastertab_jan05_update" as
on update to mastertab where
(NEW.datecol < '20050101' or NEW.datecol >= '20050201')
and
(OLD.datecol >= '20050101' and OLD.datecol < '20050201')
do instead (
-- single command, semicolon present
delete from mastertab_jan05 where id = OLD.id;
);
=> ERROR: syntax error at end of input at character 255
Ie, the "OR REPLACE" token is broken in that is doesn't work with multiple
commands (semicolons?)
/Mikael
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] create OR REPLACE rule bug
"Mikael Carneholm" <[EMAIL PROTECTED]> writes: > Ie, the "OR REPLACE" token is broken in that is doesn't work with > multiple commands (semicolons?) None of these examples fail for me, in any PG version back to 7.3. I speculate that the problem is in whatever client-side software you are using (which you didn't say, nor did you mention which PG version this is; tut tut). regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] create OR REPLACE rule bug
"Mikael Carneholm" <[EMAIL PROTECTED]> writes: > However, the rule does not work as expected (but I have been warned, see > http://archives.postgresql.org/pgsql-general/2005-10/msg01902.php) Once you do the DELETE, there is no OLD row anymore, so there is nothing for the INSERT to do. You might have better luck implementing this stuff as triggers. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
