[SQL] Efficient ON DELETE trigger when referential integrity is involved ?
Hello, I am trying to figure out how to handle tuple deletion efficiently when ON DELETE triggers and referential integrity are involved. The scenario is about this one: I have a MASTER and a SLAVE table, the latter referencing the former through a "FOREIGN KEY ... REFERENCES ... ON DELETE CASCADE" constraint. Besides, I have a ON DELETE trigger on the SLAVE table which updates a field in the MASTER table upon deletion. Now, there no need to update the MASTER table if the SLAVE table deletion was actually fired by the FOREIGN KEY constraint. The ON DELETE trigger updates a tuple in the MASTER table which will be deleted itself right after. This can make the deletion of a MASTER table tuple very slow, if there are a lot of related tuples in the SLAVE table. Is there any "by the book" way to handle this ? Does the triggering mechanism (either for referential integirty or user triggers) in PostgreSQL allow to circumvent this problem ? Somehow, the problem could be solved if there was a way to find out (in the ON DELETE trigger) that the deletion was fired because of referential integrity, and thus prevent the updating of the referenced tuple. Is there any way to find out ? Does the usage of BEFORE/AFTER triggers affect this ? Are the referential integrity triggers BEFORE or UPDATE triggers ? Can change "visibility" be of any help ? Thanx for your answers. Cedric D. PS: Sorry if the message appears twice. I used the wrong e-mail address for the first one. Mea Culpa
Re: [SQL]
On Tuesday 12 Nov 2002 7:22 am, [EMAIL PROTECTED] wrote: > HI, > > Can someone pls help us with the following problem? > > Any help would be appreciated. > > Is stored procedures will return muliple rows in postgresql. > If so a sample example is needed. With current versions of PG, look into returning a cursor from plpgsql. For 7.3 there are table functions. Look in the manual for examples with cursors - also perhaps check Roberto Mello's plpgsql cookbook on techdocs.postgresql.org Table functions might well be documented in the developer's version of the manual but there are definitely examples in the contrib/ directory of the source download (of 7.3). I seem to remember an example in the mailing lists too, so perhaps a check of the archives would be worthwhile. -- Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] execute a query in an other db
Hi, When I'm connected to a postgreSQL db ( e.g. myDB ), how could I execute "select * from myTable;" in an other db (e.g. myOtherDB). In other words, what is the postgreSQL equivalent of the MS SQL Server statement "USE myOtherDB SELECT * FROM myTable" ? Thanks Lilian ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Output of function into a file
Hi, I am writing a small function to manipulate records in multiple tables. Since the function cannot return more than one value, I would like to get all the outputs of the queries and append them into a text file. Is there any way to do it inside the function. I came across many postings which tell me how to use it in pgsql but not inside the function. I would be very glad if someone could help me Thanks in advance karthick v ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] CREATE VIEW (dynamically)
code << CREATE FUNCTION "requests_insert_after" () RETURNS opaque AS ' DECLARE view_name text; BEGIN view_name := ''request_'' || NEW.id; CREATE VIEW view_name AS select * from groups; return NEW; END' LANGUAGE 'plpgsql'; CREATE TRIGGER "requests_insert_after" AFTER INSERT ON "requests" FOR EACH ROW EXECUTE PROCEDURE "requests_insert_after" ();>> This code after an insertion on table "requests" give me this message : << PostgreSQL said: ERROR: parser: parse error at or near "$1" >> Why ? Thx for any help. Emmanuel Engelhart ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] parse bug
seems like an error in the sql parser to me:
# create table test (acol smallint[]);
CREATE
# insert into test (acol) values ('{ 0 }');
ERROR: pg_atoi: error in "0 ": can't parse " "
# insert into test (acol) values ('{ 0}');
INSERT 28472 1
the only difference is the trailing " " after the 0;
Regards,
Floyd Shackelford
4 Peaks Technology Group, Inc.
VOICE: 334.735.9428
FAX: 916.404.7125
EMAIL: [EMAIL PROTECTED]
ICQ #: 161371538
acta non verba
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] Efficient ON DELETE trigger when referential integrity is involved ?
Hello, I am trying to figure out how to handle tuple deletion efficiently when ON DELETE triggers and referential integrity are involved. The scenario is about this one: I have a MASTER and a SLAVE table, the latter referencing the former through a "FOREIGN KEY ... REFERENCES ... ON DELETE CASCADE" constraint. Besides, I have a ON DELETE trigger on the SLAVE table which updates a field in the MASTER table upon deletion. Now, there no need to update the MASTER table if the SLAVE table deletion was actually fired by the FOREIGN KEY constraint. The ON DELETE trigger updates a tuple in the MASTER table which will be deleted itself right after. This can make the deletion of a MASTER table tuple very slow, if there are a lot of related tuples in the SLAVE table. Is there any "by the book" way to handle this ? Does the triggering mechanism (either for referential integirty or user triggers) in PostgreSQL allow to circumvent this problem ? Somehow, the problem could be solved if there was a way to find out (in the ON DELETE trigger) that the deletion was fired because of referential integrity, and thus prevent the updating of the referenced tuple. Is there any way to find out ? Does the usage of BEFORE/AFTER triggers affect this ? Are the referential integrity triggers BEFORE or UPDATE triggers ? Can change "visibility" be of any help ? Thanx for your answers. Cedric D.
Re: [SQL] CREATE VIEW (dynamically)
> CREATE FUNCTION "requests_insert_after" () RETURNS opaque AS ' > DECLARE > view_name text; > BEGIN > view_name := ''request_'' || NEW.id; > CREATE VIEW view_name AS select * from groups; > return NEW; > END' LANGUAGE 'plpgsql'; > > CREATE TRIGGER "requests_insert_after" AFTER INSERT ON "requests" FOR EACH ROW > EXECUTE PROCEDURE "requests_insert_after" ();>> > > This code after an insertion on table "requests" give me this message : > << > PostgreSQL said: ERROR: parser: parse error at or near "$1" > >> > > Why ? > You should check the documentation of plpgsql for the section Executing dynamic queries (which also means commands like update, etc.) e.g. to drop a view within plpgsql you have to code EXECUTE ''DROP VIEW '' || quote_ident( view_name) ; Regards, Christoph ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] parse bug
On Mon, 11 Nov 2002 [EMAIL PROTECTED] wrote:
>
> seems like an error in the sql parser to me:
>
> # create table test (acol smallint[]);
> CREATE
> # insert into test (acol) values ('{ 0 }');
> ERROR: pg_atoi: error in "0 ": can't parse " "
> # insert into test (acol) values ('{ 0}');
> INSERT 28472 1
>
> the only difference is the trailing " " after the 0;
traditionally atoi ignores only leading spaces, so
insert into test (acol) values ('{ 0}');
should work too.
btw why do you need trailing spaces for??
>
>
> Regards,
>
> Floyd Shackelford
> 4 Peaks Technology Group, Inc.
> VOICE: 334.735.9428
> FAX: 916.404.7125
> EMAIL: [EMAIL PROTECTED]
> ICQ #: 161371538
>
> acta non verba
>
>
>
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly
>
==
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-10-8981112
fax:+30-10-8981877
email: [EMAIL PROTECTED]
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] execute a query in an other db
On Sat, Nov 09, 2002 at 17:51:40 +0100, LR <[EMAIL PROTECTED]> wrote: > Hi, > > When I'm connected to a postgreSQL db ( e.g. myDB ), how could I execute > "select * from myTable;" in an other db (e.g. myOtherDB). > > In other words, what is the postgreSQL equivalent of the MS SQL Server > statement "USE myOtherDB SELECT * FROM myTable" ? Stock postgresql doesn't support cross database queries. There is a contrib package which does. In the example you gave, you were combining information from two databases. If it is the case that your application will be combining data from the two databases, then you can open a separate connection to each database and use the appropiate connection for your queries. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Output of function into a file
>
> I am writing a small function to manipulate records in multiple
> tables. Since the function cannot return more than one value, I would
> like to get all the outputs of the queries and append them into a text
> file.
>
> Is there any way to do it inside the function. I came across many
> postings which tell me how to use it in pgsql but not inside the
> function.
>
You did not tell which function language you're talking about.
I'm assuming it's plpgsql.
First, did you check the cursors section?
Rather than executing a whole query at once, it is possible to set up a
cursor that encapsulates the query, and then read the query
result a few rows at a time. One reason for doing this is to avoid
memory overrun when the result contains a large number of rows.
(However, PL/pgSQL users don't normally need to worry about that, since
FOR loops automatically use a cursor internally to avoid
memory problems.) A more interesting possibility is that a function can
return a reference to a cursor that it has set up, allowing
the caller to read the rows. This provides one way of returning a rowset
from a function.
Taken from "PostgreSQL 7.2.1 Documentation Chapter 23. PL/pgSQL - SQL
Procedural Language"
Second, if this is not what you want to use,
I have not seen anything within plpgsql which could be used
to write to a file. But, what about a database table to be used as a
file?
e.g.
CREATE TABLE file_replacement(one_line TEXT);
then populate it by inserts like
INSERT INTO file_replacement VALUES('');
and to get them back in FIFO order
SELECT one_line FROM file_replacement ORDER BY oid;
Regards, Christoph
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] averaging interval values
Hi All, I have a table with one varchar field that contains time intervals. Is there any way to average or sum those intervals in a table ? Table name : test field name : t2 (varchar(40)) it contains all intervals as below t2 1:01:01 0:25:15 7:09:01 8:09:05 Is there any way to average or sum those above values using a function? i could not type cast the varchar(40) to interval type. How to change the datatype of field from varchar to interval? Thanks and Regards, Praveen ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] averaging interval values
> > 1:01:01 > 0:25:15 > 7:09:01 > 8:09:05 > > Is there any way to average or sum those above values using a > function? > > i could not type cast the varchar(40) to interval type. > > How to change the datatype of field from varchar to interval? > Try SELECT CAST(CAST (t2 AS TEXT) AS INTERVAL) FROM test; SELECT AVG(CAST(CAST (t2 AS TEXT) AS INTERVAL)) FROM test; SELECT SUM(CAST(CAST (t2 AS TEXT) AS INTERVAL)) FROM test; It works fine on my machine, I'm using PostgreSQL 7.2.1. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Efficient ON DELETE trigger when referential integrity is
On Tue, 12 Nov 2002, [iso-8859-1] Cédric Dufour (Cogito Ergo Soft) wrote: > Hello, > > I am trying to figure out how to handle tuple deletion efficiently when ON > DELETE triggers and referential integrity are involved. The scenario is > about this one: > > I have a MASTER and a SLAVE table, the latter referencing the former through > a "FOREIGN KEY ... REFERENCES ... ON DELETE CASCADE" constraint. Besides, I > have a ON DELETE trigger on the SLAVE table which updates a field in the > MASTER table upon deletion. > > Now, there no need to update the MASTER table if the SLAVE table deletion > was actually fired by the FOREIGN KEY constraint. The ON DELETE trigger > updates a tuple in the MASTER table which will be deleted itself right > after. This can make the deletion of a MASTER table tuple very slow, if > there are a lot of related tuples in the SLAVE table. > > Is there any "by the book" way to handle this ? Hmm, by the time that the on delete trigger runs after the foreign key action, I believe that the row in MASTER is already gone. Running a simple test in 7.3 seems to confirm this. Unfortunately it's still going to look for the row to update it. I can't think of a good way to tell if you're in an action that was caused by a foreign key rather than some other user trigger or rule or straight delete apart from some vague notions of really complicated workarounds which only partially help. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Permission on insert rules
Bruce Momjian wrote: Josh Berkus wrote: Luis, Just a question. I'm writing some rules to insert/update some data in my database, and I gave all the privileges on that view to the user, and only select on the tables. When that user inserts data using the view, I thought that was user postgres that will do the rest ! But I got permission denied on those tables. The idea was to create a layer, with the views, giving to that user permission on views to insert and update, and not to tables. Is this possible ? This is a known problem. I know that permissions for Functions has been addressed in 7.3. However, I am not sure about permissions for updatable views. Tom, Bruce? Views have always had their own permissions. Offcourse, but when I'm giving permissions to insert and update on views, I have to give those permissions also to the tables !! (those operations that are executed on rules) Luis Sousa smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] Permission on insert rules
Robert Treat wrote: On Fri, 2002-11-08 at 21:40, Bruce Momjian wrote: Josh Berkus wrote: Luis, Just a question. I'm writing some rules to insert/update some data in my database, and I gave all the privileges on that view to the user, and only select on the tables. When that user inserts data using the view, I thought that was user postgres that will do the rest ! But I got permission denied on those tables. The idea was to create a layer, with the views, giving to that user permission on views to insert and update, and not to tables. Is this possible ? This is a known problem. I know that permissions for Functions has been addressed in 7.3. However, I am not sure about permissions for updatable views. Tom, Bruce? Views have always had their own permissions. If the functions can fire as there creator instead of there caller, then I would think as long as the creator has insert/update views on the base table, you should be able to do updateable rules and give only permissions to the view for the caller. (Though maybe you have to use triggers rather than rules to do this?) Does that sound right? Robert Treat Is that the only way to do it ? Luis Sousa smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] Permission on insert rules
Josh Berkus wrote: Luis, Just a question. I'm writing some rules to insert/update some data in my database, and I gave all the privileges on that view to the user, and only select on the tables. When that user inserts data using the view, I thought that was user postgres that will do the rest ! But I got permission denied on those tables. The idea was to create a layer, with the views, giving to that user permission on views to insert and update, and not to tables. Is this possible ? I just checked this. It works fine in 7.2.3. I think that you are missing a step. If you want to have an updatable view, then you need to define a Rule for updating it, such as: kitchen=# create rule update_password as on update to user_password kitchen-# do instead update "user" set "password" = NEW."password" kitchen-# where user_id = OLD.user_id; See the online docs, under Server Programming, for how to use the RULES system. That's what I already made. The problem is when I do the update, I permission denied in all the tables for update and insert. The user that's making this operation only have select privilege. Any way, I'm using version 7.2.1-2 for debian. Luis Sousa smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] Permission on insert rules
Luis, > That's what I already made. The problem is when I do the update, I > permission denied in all the tables for update and insert. The user > that's making this operation only have select privilege. > Any way, I'm using version 7.2.1-2 for debian. I can't reproduce the problem, and permissions did not get fixed between 7.2.1 and 7.2.3. So I'm pretty sure that you're missing something, somewhere. Please post: 1) The table definitions for the tables being updated. 2) The view definition and permissions 3) The Rules statements defined on the view 4) A copy of your database session where your update is denied, including the exact error message received. Without that information, no futher help is available. -Josh Berkus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Inconsistent or incomplete behavior obverse in where clause
Hello, Our application development group has observed what we feel is inconsistent behavior when comparing numeric column references to constant/literal values in SQL. I would appreciate comments on the best approach to this problem that will allow for the highest portability of our application code. I have searched the archives and online docs, but so far have not found anyone addressing the problem quite this way. Assume wuActive is a numeric field ( with scale but no precision ) in the table WU: select count(wuid) from WU where wuActive = 0 --works fine select count(wuid) from WU where wuActive = '0' --works fine select count(wuid) from WU where wuActive = '0.0' --works fine select count(wuid) from WU where wuActive = 0.0 --throws the following exception: "Unable to identify an operator '=' for types 'numeric' and 'double precision' You will have to retype this query using an explicit cast" Second, assume tPct is a numeric field ( having scale of 4 and precision of 1 ) in the table T select count(tid) from T where tPct > 77 --works fine select count(tid) from T where tPct > '77' --works fine select count(tid) from T where tPct > '77.5' --works fine select count(tid) from T where tPct > 77.5 -- again throws the exception: "Unable to identify an operator '>' for types 'numeric' and 'double precision' You will have to retype this query using an explicit cast" This seems to occur regardless of connectivity drivers used (ODBC, JDBC, etc..) I am aware of the use of type casting to force the desired behavior in these situations. I have also started to go down the road of creating functions and operators to force numeric to numeric comparison operations when comparing numeric to float, but realize that this approach is fraught with pitfalls, in fact it is interesting to us to note that with an operator in place to force numeric = float comparisons to parse as numeric = numeric, we started getting the opposite behavior. Queries with 'column reference' = 0.0 worked fine, but queries with 'column reference' = 0 threw a variant of the previous exception: "Unable to identify an operator '=' for types 'numeric' and 'integer'" Overall, this behavior appears to be inconsistent and is not the same behavior I have experienced with many other DBMS's. Specifically, it seems strange that the parser does not treat values 0.0 or 77.5 as numeric(s[,p]) when comparing the values to a column reference known to be of type numeric (s,[p]). Is an unquoted number in the form of NN.N always treated as a float? If the planner could somehow recognize that the constant/ literal value was being compared to a column reference of the type numeric (s,p) and treat the value accordingly, then would operator identification no longer be a problem? We are looking to maintain a high degree of portability in our application code, and while "CAST ( expression as type )" is fairly portable, no one here feels that it is a portable as column reference = literal/constant value. If someone knows of a better approach, or can point us to documentation of build or run-time configuration that affects the query planner where this issue is concerned, it would be much appreciated. Thanks, Paul Ogden Database Administrator/Programmer Claresco Corporation (510) 549-2290 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Inconsistent or incomplete behavior obverse in where clause
Paul Ogden <[EMAIL PROTECTED]> writes: > select count(wuid) from WU where wuActive = 0.0 --throws the > following exception: > "Unable to identify an operator '=' for types 'numeric' and 'double > precision' You will have to retype this query using an explicit cast" This is fixed as of 7.3. (We still have related issues for smallint and bigint columns, unfortunately.) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Output of function into a file
in 7.3 you will be able to return multiple rows with multiple columns. 7.3 stable is going to be out soon as it in beta 5 stage currently. regds mallah. On Saturday 09 November 2002 11:00, karthick v wrote: > Hi, > > I am writing a small function to manipulate records in multiple > tables. Since the function cannot return more than one value, I would > like to get all the outputs of the queries and append them into a text > file. > > Is there any way to do it inside the function. I came across many > postings which tell me how to use it in pgsql but not inside the > function. > > I would be very glad if someone could help me > > Thanks in advance > > karthick v > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Inconsistent or incomplete behavior obverse in where
Paul, > Thanks for the reply. Much of what you say is as we expected. > I see that 7.3 has addressed the "Unable to identify an operator > '=' for types 'numeric' and 'double precision'" problem, but > I'm not sure how. Context-sensitive approach? Overloaded operator > approach? Something else ( is there )? A modification of the operators available. > If the release of 7.3 is soon, perhaps we can get by with the > band-aid approach of overloading the comparison operators > until such time as the new version is available. Production > for us is next spring, so maybe we'll be okay on this one. > This approach would certainly allow our development team to > right their code one way. 7.3 final is expected before December. -- -Josh Berkus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Postgres Date pg_dump/Import:
Tom; Not sure how to tell. I didn't explicitly specify anything like this during the ./configure step of install. Poking around in the souce directory, I pulled this out of config.cache ... configure:1243: checking whether the C compiler (gcc -O9 -funroll-loops -ffast-math -malign-double -mcpu=pentiumpro -march=pentiumpro -fomit-frame-pointer -fno-exceptions ) works Note, I didn't use the postgresql that came with Slackware; I downloaded it from the postgres web site. Martin >> i am currently running 7.2.2 on slackware 8.1 (kernel 2.4.18) > > BTW, is it possible that your postgres was built with -ffast-math > in CFLAGS? That is known to cause symptoms like this. Unfortunately > -ffast-math is in the default CFLAGS on some Linux distros. > > regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Postgres Date pg_dump/Import:
"Martin Crundall" <[EMAIL PROTECTED]> writes: > Not sure how to tell. I didn't explicitly specify anything like this > during the ./configure step of install. Poking around in the souce > directory, I pulled this out of config.cache ... > configure:1243: checking whether the C compiler (gcc -O9 -funroll-loops > -ffast-math -malign-double -mcpu=pentiumpro -march=pentiumpro ^^^ > -fomit-frame-pointer -fno-exceptions ) works Bingo: something is supplying a whole bunch of "helpful" optimization options, including -ffast-math which is what's causing your problem. Left to its own devices, configure would have only selected "-O2" as the default flags for gcc. I suspect if you try "env | grep CFLAGS" you will find that the problem is a CFLAGS environment variable that you're inheriting from somewhere. If so, you could "unset CFLAGS" and then re-run configure, rebuild, reinstall (you won't need to initdb fortunately, just reinstall the executables). If it's not coming from a CFLAGS environment variable setting then you'll need to look harder. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Postgres Date pg_dump/Import:
Tom; IT WORKED! This is what I did: env | grep CFLAGS and that produced all those flags as you predicted (-O9 -funroll-loops -ffast-math -malign-double -mcpu=pentiumpro -march=pentiumpro) unset CFLAGS make clean ./configure --enable-odbc make make install a subsequent pg_dump of the staging system's database produced NO ":60" substrings in any time stamps! Thanks much for your help! Martin p.s. interesting to note -- my dev box is actually the same distro (slackware 8.1) but running in a vmware window. there was no such CFLAGS env variable set and pg_dumps on the dev box are ok. the distro install must set the CFLAGS based on hardware (the laptop is a Compaq PIII/700, and the staging box is a P4/1.7G on an intel chipset board). Anyway, really happy this worked -- thanks again. Let me know if there's anything I can do. > "Martin Crundall" <[EMAIL PROTECTED]> writes: >> Not sure how to tell. I didn't explicitly specify anything like this >> during the ./configure step of install. Poking around in the souce >> directory, I pulled this out of config.cache ... > >> configure:1243: checking whether the C compiler (gcc -O9 >> -funroll-loops -ffast-math -malign-double -mcpu=pentiumpro >> -march=pentiumpro > ^^^ >> -fomit-frame-pointer -fno-exceptions ) works > > Bingo: something is supplying a whole bunch of "helpful" optimization > options, including -ffast-math which is what's causing your problem. > Left to its own devices, configure would have only selected "-O2" as the > default flags for gcc. > > I suspect if you try "env | grep CFLAGS" you will find that the problem > is a CFLAGS environment variable that you're inheriting from somewhere. > If so, you could "unset CFLAGS" and then re-run configure, rebuild, > reinstall (you won't need to initdb fortunately, just reinstall the > executables). > > If it's not coming from a CFLAGS environment variable setting then > you'll need to look harder. > > 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: [SQL] Permission on insert rules
This should be a test case for what Luis wants, although it works in 7.2.1 so maybe not. Luis, if this isn't what your trying to do, you'll need to post some code: create table parent (id int, name text, misc text); create view child as select id,name from parent; create rule jammasterjay as on insert to child do instead insert into parent values (new.id,new.name); insert into parent values (1,'one','wahad'); insert into parent values (2,'two','ithnain'); insert into parent values (3,'three','thalata'); select * from parent; select * from child; insert into child (4,'four'); select * from parent; create user mellymel; grant select on child to mellymel; grant insert on child to mellymel; ** reconnect as mellymel ** select * from parent; (generates error) select * from child; insert into child values (5,'five'); select * from child; (has all 5 rows) Robert Treat On Tue, 2002-11-12 at 12:29, Josh Berkus wrote: > Luis, > > > That's what I already made. The problem is when I do the update, I > > permission denied in all the tables for update and insert. The user > > that's making this operation only have select privilege. > > Any way, I'm using version 7.2.1-2 for debian. > > I can't reproduce the problem, and permissions did not get fixed > between 7.2.1 and 7.2.3. So I'm pretty sure that you're missing > something, somewhere. > > Please post: > > 1) The table definitions for the tables being updated. > 2) The view definition and permissions > 3) The Rules statements defined on the view > 4) A copy of your database session where your update is denied, > including the exact error message received. > > Without that information, no futher help is available. > > -Josh Berkus > > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Inconsistent or incomplete behavior obverse in where
Paul, > "Unable to identify an operator '=' for types 'numeric' and 'double > precision' You will have to retype this query using an explicit cast" This is due, as you surmised, to decimal values defaulting to floats. While there is little problem with an = operator for numeric and float, you would not want an implicit cast for a / operator with numeric and float. As a result, I believe that all numeric and float operators have been left undefined. > I am aware of the use of type casting to force the desired > behavior in these situations. I have also started to go down > the road of creating functions and operators to force numeric > to numeric comparison operations when comparing numeric to float, > but realize that this approach is fraught with pitfalls, in fact > it is interesting to us to note that with an operator in place > to force numeric = float comparisons to parse as numeric = numeric, > we started getting the opposite behavior. Queries with 'column > reference' = 0.0 worked fine, but queries with 'column reference' = 0 > > threw a variant of the previous exception: > > "Unable to identify an operator '=' for types 'numeric' and > 'integer'" Now, that's interesting. Why would defining a "numeric = float" have broken "numeric = integer"? There's no reason I can think of. Perhaps I will try this myself and see if I encounter the same problem, or if your team modified the numeric = integer operator by mistake. > Overall, this behavior appears to be inconsistent and is not > the same behavior I have experienced with many other DBMS's. > Specifically, it seems strange that the parser does not treat > values 0.0 or 77.5 as numeric(s[,p]) when comparing the values > to a column reference known to be of type numeric (s,[p]). > > Is an unquoted number in the form of NN.N always treated as a > float? Yes. I believe that this is from the SQL 92 spec; hopefully someone on this list with a copy of the Guide to the SQL Standard can quote it for you. > If the planner could somehow recognize that the constant/ > literal value was being compared to a column reference of the > type numeric (s,p) and treat the value accordingly, then would > operator identification no longer be a problem? It's an interesting idea, and would be wonderful if it could be made to work. However, the challenge of getting the program to correctly recognize the context for all literal values *without* making any wrong assumptions that would afffect the data could be substantial. Most other RDBMSs deal with this, not by any kind of data type context-sensitivity, but simply by supporting a large number of implicit casts. This approach can have its own perils, as I have experienced with MS SQL Server, where the average of splits for 120,000 transactions is significantly different if you accidentally let the database implicitly cast the values as Float instead of Numeric. As such, there was talk on the Hackers list at one time of *reducing* the number of implicit casts instead of increasing them. This would obviously make your particular problem even worse, but the proponents of reduction point out that implicit casts can get you into real trouble if you're not aware of them, wheras forcing explicit casts just gets you error messages. Hmmm ... in fact, I'd think the perfect solution would be a compile-time option or contrib package which allows you to enable/disable implicit casts for many data types. > We are looking to maintain a high degree of portability in our > application code, and while "CAST ( expression as type )" is > fairly portable, no one here feels that it is a portable as > column reference = literal/constant value. If someone knows > of a better approach, or can point us to documentation of build or > run-time configuration that affects the query planner where this > issue is concerned, it would be much appreciated. Hopefully someone else will respond to your message as well. I'll re-phrase one of your questions for the Hackers list: QUESTION: Is there any way we could distinguish between literals and column references when processing operators? That is, while we would *not* want to implicitly convert a float column to numeric for equality comparison, would it be possible to convert a literal value to match the column to which it is compared? Or is literal processing completed before any expressions are evaluated? -Josh Berkus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] Inconsistent or incomplete behavior obverse in where
"Josh Berkus" <[EMAIL PROTECTED]> writes: > Now, that's interesting. Why would defining a "numeric = float" have > broken "numeric = integer"? There's no reason I can think of. The problem probably is that the parser now finds two possible interpretations that look equally good to it, so it can't choose. It could coerce the integer constant to numeric (and use numeric=numeric) or to float (and use the added numeric=float operator), and there's no rule that can break the tie. In 7.3 and 7.4 we are actually going in the direction of removing cross-data-type operators, not adding them, because they tend to create too many options for the parser to choose from. 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: [SQL] Inconsistent or incomplete behavior obverse in where
Josh, Thanks for the reply. Much of what you say is as we expected. I see that 7.3 has addressed the "Unable to identify an operator '=' for types 'numeric' and 'double precision'" problem, but I'm not sure how. Context-sensitive approach? Overloaded operator approach? Something else ( is there )? If the release of 7.3 is soon, perhaps we can get by with the band-aid approach of overloading the comparison operators until such time as the new version is available. Production for us is next spring, so maybe we'll be okay on this one. This approach would certainly allow our development team to right their code one way. > > > Paul, > > > "Unable to identify an operator '=' for types 'numeric' and 'double > > precision' You will have to retype this query using an explicit cast" > > This is due, as you surmised, to decimal values defaulting to floats. > While there is little problem with an = operator for numeric and > float, you would not want an implicit cast for a / operator with > numeric and float. As a result, I believe that all numeric and float > operators have been left undefined. > > > I am aware of the use of type casting to force the desired > > behavior in these situations. I have also started to go down > > the road of creating functions and operators to force numeric > > to numeric comparison operations when comparing numeric to float, > > but realize that this approach is fraught with pitfalls, in fact > > it is interesting to us to note that with an operator in place > > to force numeric = float comparisons to parse as numeric = numeric, > > we started getting the opposite behavior. Queries with 'column > > reference' = 0.0 worked fine, but queries with 'column reference' = 0 > > > > threw a variant of the previous exception: > > > > "Unable to identify an operator '=' for types 'numeric' and > > 'integer'" > > Now, that's interesting. Why would defining a "numeric = float" have > broken "numeric = integer"? There's no reason I can think of. > Perhaps I will try this myself and see if I encounter the same > problem, or if your team modified the numeric = integer operator by > mistake. > No, we made no modifications to numeric = integer. In fact, issuing DROP OPERATOR (numeric,float8); cleared that problem right up. And brought us back to square one. > > Overall, this behavior appears to be inconsistent and is not > > the same behavior I have experienced with many other DBMS's. > > Specifically, it seems strange that the parser does not treat > > values 0.0 or 77.5 as numeric(s[,p]) when comparing the values > > to a column reference known to be of type numeric (s,[p]). > > > > Is an unquoted number in the form of NN.N always treated as a > > float? > > Yes. I believe that this is from the SQL 92 spec; hopefully someone > on this list with a copy of the Guide to the SQL Standard can quote it > for you. > > > If the planner could somehow recognize that the constant/ > > literal value was being compared to a column reference of the > > type numeric (s,p) and treat the value accordingly, then would > > operator identification no longer be a problem? > > It's an interesting idea, and would be wonderful if it could be made to > work. However, the challenge of getting the program to correctly > recognize the context for all literal values *without* making any wrong > assumptions that would afffect the data could be substantial. > > Most other RDBMSs deal with this, not by any kind of data type > context-sensitivity, but simply by supporting a large number of > implicit casts. This approach can have its own perils, as I have > experienced with MS SQL Server, where the average of splits for 120,000 > transactions is significantly different if you accidentally let the > database implicitly cast the values as Float instead of Numeric. > > As such, there was talk on the Hackers list at one time of *reducing* > the number of implicit casts instead of increasing them. This would > obviously make your particular problem even worse, but the proponents > of reduction point out that implicit casts can get you into real > trouble if you're not aware of them, wheras forcing explicit casts just > gets you error messages. > > Hmmm ... in fact, I'd think the perfect solution would be a > compile-time option or contrib package which allows you to > enable/disable implicit casts for many data types. > I think this is a great idea. We're more of the postgres user class than hacker class, so that's out of our scope to undertake, but we'd sure use it. > > We are looking to maintain a high degree of portability in our > > application code, and while "CAST ( expression as type )" is > > fairly portable, no one here feels that it is a portable as > > column reference = literal/constant value. If someone knows > > of a better approach, or can point us to documentation of build or > > run-time configuration that affects the query planner where this > > is
