Re: [SQL] Rules aren't doing what I expect
Mark Volpe <[EMAIL PROTECTED]> writes: > When I try this out, however, the rule seems to use the original > value, rather than the "corrected" value. Queries added by non-INSTEAD rules are always performed before the initially-given query, so you're right, the rule will see the unmodified value. I'd suggest folding the log-entry-making into your trigger, actually. If you have a trigger anyway then the insert into t1_log is only one more line in that trigger procedure... regards, tom lane
Re: [SQL] Problem with SQL query (eats swap)
[EMAIL PROTECTED] (Mailing List Expander) writes: > select count(*) from attachments a where a.id in (select m.id from > mail m where m.date < now()-62); > but ran out of swap. The problem is that "now()-62" leaks memory to the tune of a few dozen bytes per evaluation. In existing releases that memory won't be reclaimed till end of query. (This problem is fixed for 7.1, but that won't help you today.) Since the inner select is re-executed for each iteration of the outer select, you have a lot of executions of the inner WHERE clause, and so even a small leak is a problem. regards, tom lane
Re: [SQL] Rules aren't doing what I expect
Tom Lane wrote: > > Queries added by non-INSTEAD rules are always performed before the > initially-given query, so you're right, the rule will see the unmodified > value. > > I'd suggest folding the log-entry-making into your trigger, actually. > If you have a trigger anyway then the insert into t1_log is only one > more line in that trigger procedure... > > regards, tom lane Thanks for the explanation, Tom. I left out part of my story though. :) I would like normal users to be able to modify t1 but not t1_log, and doing what you said would require INSERT permission on t1_log. So what I did was go ahead and allow INSERT permission, but create before and after triggers on t1_log that check the inserted values against reality... but that brings up another question - If I do an UPDATE on t1, it calls a trigger which eventually does: INSERT INTO t1_log VALUES(OLD.a, NEW.a); If t1_log has before and after triggers, the before trigger will always see the old row in t1, and the after trigger will always see the new data, right? At least that's what I'm seeing. The "visibility of data changes" document was kinda confusing... Mark
[SQL] Trying to Creat a Rule
Hello, I am trying to create a RULE that updates the size of a class when a student registers for that class and the register(module) table is updated, and subtract 1 from the given class size when a student drops a course. Actually, there is a class table and a module table. The class table's primary key is id, the module table consists of every module that a student can register for a given year containing a class id. I think I have the INSERT RULE down, but am not sure about the UPDATE RULE: CREATE RULE module_update AS ON UPDATE TO module.a_q1 DO SET class.size to class.size-1 WHERE class.id=old.a_q1 AND set class.size to class.size+1 WHERE class.id=new.a_q1; I am really not so sure about the 'and' part, but I need to do both updates on class, using different WHERE criteria. TIF, Kurt
[SQL] [Fwd: I will be at Linux World]
-- Zot O'Connor http://www.ZotConsulting.com http://www.WhiteKnightHackers.com I will be at Linux World in San Jose next week, Aug 15th, and 16th. This is a generic spam/post to see if anyone I know will be there, who wants to meet with me. I will also be in Atlanta the last week of September. I am sending this since I hate going to an event, only to determine 2 weeks later an old friend, net friend, or friend of a friend was at the same show, town, or area. Even sometime people I work with :) Last time I did this I did determine that some folks I had not seen in a year were going to be in a booth across the Hall. I might not have met them. Anyway, please make sure you respond to me, not any of the lists I am crossposting to (especially since I will like not check my folders before I go, so please change any prefixes as well :) Apologies (mostly insincere) for the Spam. -- Zot O'Connor http://www.ZotConsulting.com http://www.WhiteKnightHackers.com
[SQL] non-cachable 'C' language functions
(using postgresql 6.5.2) I have created a set of postgres extension functions in C (which use SPI to perform queries), and added them to my database with something like this: CREATE FUNCTION my_next_uid(text) RETURNS text AS '/usr/lib/pgsql/my_uids.so' LANGUAGE 'c'; My functions are designed to behave like nextval() and friends, except that they operate on a varchar field in a predetermined table, rather than a database sequence. For example, my_next_uid() should always return a unique value, incrementing the current value in said table each time it is called. So far, my functions appear to work correctly. However, looking at their entries in the pg_proc table, I see that their "proiscachable" fields are set to true. This worries me, because my understanding is that postgres will re-use values returned by cachable functions, which is undesirable. (In order for my_next_uid() to be useful, it must retrieve a new value each time it is used.) Is my understanding correct? What should I do about it? The postgresql 6.5.2 docs for CREATE FUNCTION don't tell me how to make my functions non-cachable. Regards, Forest Wilkinson
[SQL] Week of the Year?
I'm probably staring right at it. (One of the difficulties with RTFMing, is having too many docs!) Is there anything in the API that produces the week of the year, from 1 to 52 or 53 depending on the week of the year, and the days that are in that week? Many thanks. -dlj.
Re: [SQL] non-cachable 'C' language functions
Forest Wilkinson <[EMAIL PROTECTED]> writes: > Is my understanding correct? What should I do about it? The postgresql > 6.5.2 docs for CREATE FUNCTION don't tell me how to make my functions > non-cachable. 6.5 doesn't pay any attention to proiscachable, AFAIR. 7.0 does, but it defaults to assuming proiscachable = FALSE; you have to say "with (iscachable)" in CREATE FUNCTION to get the other behavior. There is a problem in both versions that WHERE clauses containing no variables (table fields) will be assumed to be constants even if they contain non-cachable function calls :-(. Thus, for example, select * from foo where random() < 0.5 doesn't work as desired. I plan to fix this for 7.1. Offhand I don't see a use for a nextval-like function in WHERE, so you're probably safe with both 6.5 and 7.0. regards, tom lane
Re: [SQL] Week of the Year?
Got it: -U with date. -dlj. - Original Message - From: "David Lloyd-Jones" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, August 11, 2000 9:27 PM Subject: [SQL] Week of the Year? > I'm probably staring right at it. (One of the difficulties with RTFMing, is > having too many docs!) > > Is there anything in the API that produces the week of the year, from 1 to > 52 or 53 depending on the week of the year, and the days that are in that > week? > > Many thanks. > > -dlj. > > > >
Re: [SQL] Week of the Year?
Try using the function date_part such as:
select date_part('week',now());
"and the days that are in that week" I guess want to answer a question
such as:
Given a date, what is first date in that same week, and what is the last
date in that week. There are a couple of approaches to this. My first was:
select
to_date(date_part('year',now()),'')+(7*date_part('week',now()));
and the above +6 to the the last day of the week. Another approach for
this same question is much simplier (if the question is indeed what you
are asking)
select now()-date_part('dow',now());
This last select gives the Sunday for the current week. To get the
Saturday, simply:
select now()-date_part('dow',now())+6;
Of course, replace the now() with whatever contains the date or timestamp.
John McKown
> I'm probably staring right at it. (One of the difficulties with RTFMing, is
> having too many docs!)
>
> Is there anything in the API that produces the week of the year, from 1 to
> 52 or 53 depending on the week of the year, and the days that are in that
> week?
>
> Many thanks.
>
> -dlj.
>
>
>
[SQL] Operator Precedence problem?
I have a peculiar problem that I can't reproduce on a trivial database: select product, priority, count(*) as completed from issue where product = 'DIS' and create_date < '1-Aug-2000' and finish_date >= '1-Aug-2000' or finish_date is null group by product, priority; This produces a list of all products - not just 'DIS'. If I put the last two clauses in parnthesis, then it works as expected: select product, priority, count(*) as completed from issue where product = 'DIS' and create_date < '1-Aug-2000' and (finish_date >= '1-Aug-2000' or finish_date is null) group by product, priority; Which makes me think that the precedence of 'or' is not what I expected. Is this a feature? If so, the fact that I get precisely the opposite behaviour in simple test databases must be a bug, I think. Any help or explanation would be appreciated... Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
