[BUGS] createdb failure on version 7.3.3 with Solaris 9
Hi All I am running on Solaris 9 - Jumbo Kernel Patch 06 - Full Distribution (sparc) GNU gcc 3.3 GNU make 3.8 GNU readline 4.3 The correct settings in /etc/system are made and the server restarted (I have 2 other instances of postgresql running - 7.1 on a Solaris 8 server) The install of version 7.3.3 procedes without any errors using:- configure --prefix=/usr/local/postgresql make install Starting postmaster is OK - no errors in logfile When trying to create a testdb to check the install worked I get the following error :- ld.so.1: /usr/local/postgresql/bin/psql: fatal: libgcc_s.so.1: open failed: No such file or directory Killed createdb: database creation failed Has anyone else seen this ? can any one help ? Many Thanks in advance Rupert ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] Another bug with views
... and here is another bug I ran into while trying to investigate my earlier problem with views a little more: testdb=# drop rule skip_test; ERROR: parser: parse error at or near ";" at character 20 testdb=# \h drop rule Command: DROP RULE Description: remove a rewrite rule Syntax: DROP RULE name [, ...] testdb=# drop rule skip_test on test_view; DROP RULE First of all did you guys have to change the syntax in 7.3, and make it incompatible with all the existing sql code out there? :-( I see that the rule names are no longer required to be globally unique... but still it would be nice if it could understand the old syntax and work the old way as long as there is only one matching rule... Secondly, even if it has to be this way, it looks like the help entry is out of date... Dima ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] Another bug with views
Dmitry Tkach <[EMAIL PROTECTED]> writes: > Secondly, even if it has to be this way, it looks like the help entry is > out of date... No, but your psql evidently is. I get regression=# \h drop rule Command: DROP RULE Description: remove a rewrite rule Syntax: DROP RULE name ON relation [ CASCADE | RESTRICT ] from 7.3 psql. Yours is evidently 7.2. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] INSTEAD rule bug?
Here is a problem a ran into: testdb=# create table test (x int); CREATE TABLE testdb=# create table test_view as select * from test; SELECT testdb=# create rule insert_test as on insert to test_view do instead insert into test values (new.*); CREATE RULE testdb=# create rule skip_test as on insert to test_view where x is null do instead nothing; CREATE RULE testdb=# insert into test_view values (null); INSERT 17259 1 testdb=# select * from test; x --- (1 row) According to the last rule the insert should not have happened, right? How come it got ignored? Thanks! Dima ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] INSTEAD rule bug?
Dmitry Tkach <[EMAIL PROTECTED]> writes: > testdb=# create table test_view as select * from test; > SELECT That is not a view, it's only a static copy of the original table. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] INSTEAD rule bug?
Tom Lane wrote: Dmitry Tkach <[EMAIL PROTECTED]> writes: testdb=# create table test_view as select * from test; SELECT That is not a view, it's only a static copy of the original table. regards, tom lane I know... That was a typo in my sql :-) But for this example it doesn't matter - that view/table is only needed to illustrate the rules behaviour on insert. You can just replace 'table' with 'view' in that statement - the behaviour of the insert is exactly the same anyway. Dima ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] [BUGS] INSTEAD rule bug?
Dmitry Tkach <[EMAIL PROTECTED]> writes: > I know... That was a typo in my sql :-) > But for this example it doesn't matter - that view/table is only needed > to illustrate the rules behaviour on insert. Oh, I see what you're on about. Sorry, a "DO INSTEAD NOTHING" only suppresses the original command, it does not suppress other rules. I think what you want is to make the insert_test rule conditional on x being not null. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [BUGS] INSTEAD rule bug?
Tom Lane wrote: Oh, I see what you're on about. Sorry, a "DO INSTEAD NOTHING" only suppresses the original command, it does not suppress other rules. I think what you want is to make the insert_test rule conditional on x being not null. Yeah... that's what I was afraid of :-( The problem is that in the 'real life' situation the condition is a lot more complicated than this simple is null test... I hate having to duplicate it, and I hate even more having to evaluate it twice on every insert :-( Dima ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] [BUGS] INSTEAD rule bug?
Dmitry Tkach <[EMAIL PROTECTED]> writes: > The problem is that in the 'real life' situation the condition is a lot > more complicated than this simple is null test... I hate having to > duplicate it, and I hate even more having to evaluate it twice on every > insert :-( Why evaluate it twice? The DO INSTEAD NOTHING rule should be unconditional. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] [BUGS] INSTEAD rule bug?
Tom Lane wrote:
Dmitry Tkach <[EMAIL PROTECTED]> writes:
The problem is that in the 'real life' situation the condition is a lot
more complicated than this simple is null test... I hate having to
duplicate it, and I hate even more having to evaluate it twice on every
insert :-(
Why evaluate it twice? The DO INSTEAD NOTHING rule should be
unconditional.
Right. But the problem is I don't want to discard the invalid entries
completely...
So, it would have to be *three* rules, not just two - like:
create rule skip_null as on insert to test_view where x is null do instead
insert into invalid_entries ('NULL DATA', new.*);
create rule insert_test as on insert to test_view where is is not null
do instead
insert into test values (new.*);
create rule dummy_insert as on insert to test_view do instead nothing;
... so x is null ends up being evaluated twice...
Dima
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [BUGS] INSTEAD rule bug?
Oh, I see what you're on about. Sorry, a "DO INSTEAD NOTHING" only suppresses the original command, it does not suppress other rules. I think what you want is to make the insert_test rule conditional on x being not null. regards, tom lane Ok... What's wrong with this one then (three rules are conditional - if x is null or y is null, the entry is rejected, if x is not null and y is not null, it is inserted, the fourth rull is a dummy to prevent the planner from complaining about not being able to insert into a view): testdb=# create table test (x int not null, y int not null); CREATE TABLE testdb=# create view test_view as select * from test; CREATE VIEW testdb=# create rule reject_x as on insert to test_view where new.x is null do instead testdb=# create table test_reject (x int, y int, reason text); CREATE TABLE testdb=# create rule reject_x as on insert to test_view where x is null do instead insert into test_reject values (new.*, 'NULL x'); CREATE RULE testdb=# create rule reject_y as on insert to test_view where y is null do instead insert into test_reject values (new.*, 'NULL y'); CREATE RULE testdb=# create rule insert_test as on insert to test_view where x is not null and y is not null do instead insert into test values (new.*); CREATE RULE testdb=# create rule insert_dummy as on insert to test_view do instead nothing; CREATE RULE testdb=# insert into test values (null, null); ERROR: ExecInsert: Fail to add null value in not null attribute x Now, why does this fail? The only rule that attempts to insert anything into test has 'x is not null and y is not null' as the qualifier. What's wrong? Thanks! Dima ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [BUGS] INSTEAD rule bug?
Dmitry Tkach <[EMAIL PROTECTED]> writes: > Ok... What's wrong with this one then > testdb=# insert into test values (null, null); > ERROR: ExecInsert: Fail to add null value in not null attribute x Try inserting into test_view ... regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [BUGS] INSTEAD rule bug?
Tom Lane wrote: Dmitry Tkach <[EMAIL PROTECTED]> writes: Ok... What's wrong with this one then testdb=# insert into test values (null, null); ERROR: ExecInsert: Fail to add null value in not null attribute x Try inserting into test_view ... regards, tom lane Damn! Sorry about that Actually, believe it or not, I do actually have a similar problem on 7.2.4... I was just trying to reproduce it with a simple example, and screwed up the insert statement... :-( Sorry... I just checked this example on 7.2, and it works fine... But what the hell is my problem then??? I swear, I do insert into the view there :-) It's a really huge view, looking at a whole bunch of different tables... I'd hate having to post the whole thing... Dima ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [BUGS] INSTEAD rule bug?
Dmitry Tkach <[EMAIL PROTECTED]> writes: > But what the hell is my problem then??? I swear, I do insert into the > view there :-) > It's a really huge view, looking at a whole bunch of different tables... > I'd hate having to post the whole thing... All I can guess is a bug (or pilot error) that's triggered by the more complex view. I think you'll just have to try to whittle down the failure to something you can post. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [BUGS] INSTEAD rule bug?
Aha! I got it. This generates the 'cannot insert null...' error: create table test (x int not null, y int not null); create table test_reject (x int, y int, reason text); create view test_view as select * from test; create rule reject_x as on insert to test_view where new.x is null do instead insert into test_reject values (new.*, 'NULL x'); create rule reject_y as on insert to test_view where new.y is null do instead insert into test_reject values (new.*, 'NULL y'); create rule insert_test as on insert to test_view where new.x is not null and new.y is not null do instead ( insert into test select new.* union select new.*; ); create rule insert_dummy as on insert to test_view do instead nothing; -- insert into test_reject values (new.*, -- case when new.x is null then 'NULL x' else 'NULL y' end); insert into test_view values (null, null); It looks like the UNION in the 'not null' rule is the problem. If I change it to just insert ... select (without the union), or to two inserts, then it works. But union always fails, even if I add a 'where false' to the end, so that it only returns one row... Dima Tom Lane wrote: Dmitry Tkach <[EMAIL PROTECTED]> writes: But what the hell is my problem then??? I swear, I do insert into the view there :-) It's a really huge view, looking at a whole bunch of different tables... I'd hate having to post the whole thing... All I can guess is a bug (or pilot error) that's triggered by the more complex view. I think you'll just have to try to whittle down the failure to something you can post. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] [BUGS] INSTEAD rule bug?
Dmitry Tkach <[EMAIL PROTECTED]> writes: > create rule insert_test as on insert to test_view where new.x is not > null and new.y is not null do instead > ( >insert into test >select new.* union >select new.*; > ); Mmm. In CVS tip that throws ERROR: UNION/INTERSECT/EXCEPT member statement may not refer to other relations of same query level which was a check added as a result of this discussion thread: http://archives.postgresql.org/pgsql-general/2003-02/msg00693.php I am sure you are running into some misbehavior associated with the fact that the rule transformation generates a bogusly-structured SQL query, and 7.2 isn't noticing. I'd like to support this case someday, but it's not clear how... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] [BUGS] INSTEAD rule bug?
Tom Lane wrote: Dmitry Tkach <[EMAIL PROTECTED]> writes: create rule insert_test as on insert to test_view where new.x is not null and new.y is not null do instead ( insert into test select new.* union select new.*; ); Mmm. In CVS tip that throws ERROR: UNION/INTERSECT/EXCEPT member statement may not refer to other relations of same query level Actually, I just used that new.* as an example (if I understand this error message correctly, that's what it refers to, right?) Something like insert into test select null,null union select 1,2 where false has the same problem... and it doesn't refer to any relations. which was a check added as a result of this discussion thread: http://archives.postgresql.org/pgsql-general/2003-02/msg00693.php I'll take a look at that thread, thanks! I am sure you are running into some misbehavior associated with the fact that the rule transformation generates a bogusly-structured SQL query, and 7.2 isn't noticing. Not just 7.2... I was testing this in 7.3 - it has the same problem Dima I'd like to support this case someday, but it's not clear how... I don't know if it helps, but somehow if I do insert into test select * from (select null,null union select 1,2 where false) as dummy ... that works fine. Thanks! Dima ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [BUGS] INSTEAD rule bug?
Dmitry Tkach <[EMAIL PROTECTED]> writes: > Something like > insert into test > select null,null union select 1,2 where false > has the same problem... and it doesn't refer to any relations. But that's parsed as insert into test (select null,null) union (select 1,2 where false) so I'd expect it to bomb if test has NOT NULL constraints. > Not just 7.2... I was testing this in 7.3 - it has the same problem Yeah, the change is post-7.3. > insert into test select * from (select null,null union select 1,2 where > false) as dummy > ... that works fine. I get ERROR: ExecInsert: Fail to add null value in not null attribute x which is what I'd expect. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [BUGS] INSTEAD rule bug?
Tom Lane wrote: Dmitry Tkach <[EMAIL PROTECTED]> writes: Something like insert into test select null,null union select 1,2 where false has the same problem... and it doesn't refer to any relations. But that's parsed as insert into test (select null,null) union (select 1,2 where false) so I'd expect it to bomb if test has NOT NULL constraints. Sure, but it is inside the rule that has 'where x is not null and y is not null' on it as a qualifier, so with my test example it should just never get executed in the first place. Not just 7.2... I was testing this in 7.3 - it has the same problem Yeah, the change is post-7.3. insert into test select * from (select null,null union select 1,2 where false) as dummy ... that works fine. I get ERROR: ExecInsert: Fail to add null value in not null attribute x which is what I'd expect. Really? In 7.3? That's weird... Here is what I am getting exactly: testdb=# drop table test cascade; NOTICE: Drop cascades to rule insert_test on view test_view NOTICE: Drop cascades to rule _RETURN on view test_view NOTICE: Drop cascades to view test_view DROP TABLE testdb=# drop table test_reject cascade; DROP TABLE testdb=# testdb=# create table test (x int not null, y int not null); CREATE TABLE testdb=# create table test_reject (x int, y int, reason text); CREATE TABLE testdb=# testdb=# create view test_view as select * from test; CREATE VIEW testdb=# testdb=# create rule reject_x as on insert to test_view where new.x is null do instead insert into test_reject values (new.*, 'NULL x'); CREATE RULE testdb=# testdb=# create rule reject_y as on insert to test_view where new.y is null do instead insert into test_reject values (new.*, 'NULL y'); CREATE RULE testdb=# testdb=# create rule insert_test as on insert to test_view where new.x is not null and new.y is not null do instead testdb-# ( testdb(#insert into test select * from testdb(#(select null,null union select 1,2 where false) as dummy testdb(# ); CREATE RULE testdb=# testdb=# create rule dummy_insert as on insert to test_view do instead nothing; CREATE RULE testdb=# testdb=# testdb=# insert into test_view values (null, null); INSERT 17648 1 testdb=# select * from test; x | y ---+--- (0 rows) testdb=# select * from test_reject; x | y | reason ---+---+ | | NULL x | | NULL y (2 rows) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] [BUGS] INSTEAD rule bug?
Dmitry Tkach <[EMAIL PROTECTED]> writes: > Sure, but it is inside the rule that has 'where x is not null and y is > not null' on it as a qualifier, so > with my test example it should just never get executed in the first place. You're confusing rules with triggers. The INSERT *will* get executed; the rule's qualifier gets moved to the WHERE of the INSERT...SELECT, and the way you get no effect is for the qual to fail on every row the SELECT generates. One way to think about the problem (though I'm not sure this is right in detail) is that there's no place to hang a top-level WHERE on a UNION. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [BUGS] INSTEAD rule bug?
Tom Lane wrote: Dmitry Tkach <[EMAIL PROTECTED]> writes: Sure, but it is inside the rule that has 'where x is not null and y is not null' on it as a qualifier, so with my test example it should just never get executed in the first place. You're confusing rules with triggers. The INSERT *will* get executed; the rule's qualifier gets moved to the WHERE of the INSERT...SELECT, and the way you get no effect is for the qual to fail on every row the SELECT generates. One way to think about the problem (though I'm not sure this is right in detail) is that there's no place to hang a top-level WHERE on a UNION. Ok. If so, should UNION not be disallowed entirely inside (at least conditional) rules, regadless of whether it has those 'cross-from' references or not? What you are saying makes sense to me (and I have already rewritten that rule, and it is working now)... but it's unfortunate that I had to spend half a day trying to figure out why the damn thing doesn't work... (even worse really - I've written that rule a while ago, and it already made it into the production database before anyone noticed that it did not really work) :-( It would have saved a lot of trouble if it just complained about that union thing right away and refuse to create the rule... On a different note, I think there *is* a way to add a where clause to the union - that's exactly what I did in that last example - by converting it into a subselect... Can that not be done automatically for conditional rules? (I doubt that would be very useful though... since it's no longer possible to use old and new there... I can't really think of any useful application of a union inside a rule, except for my obscure 'select 1,2' example :-) Dima ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
