(B
(B
(BHi All,
(B
(BI have a problem with a before update
(Btrigger that I was wondering if someone might be able to give me some help
(Bwith.
(B
(BDB version is 7.2. Here is a cut down
(Bversion of an already simple trigger and function that is returning the
(Berror:
(B
(B
-
(B From:
(B Iain
(B To: [EMAIL PROTECTED]
(B Sent: Thursday, November 20, 2003
(B 11:37 AM
(B Subject: fmgr_info: function
(B 15238119: cache lookup failed
(B
(B Hi All,
(B
(B I have a problem with a before update
(B trigger that I was wondering if someone
Hi Colin,
You have done a nice job of demonstrating the documented behaviour of
sequences.
Sequences are designed for speed and high concurrency. They do not guarantee
contiguous numbers and are not included in any transaction as I understand
it.
regards
Iain
- Original Message -
From
t value for the
id (ir didn't use the default) but still used (and therefore incremented)
the sequence.
Regards
Iain
- Original Message -
From: "Burr, Colin" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, November 26, 2003 8:37 AM
Subject: [SQL] B
ms that the problem only applies to checks.
Now, I am wondering if there is a clever work around to this? It escapes me
for now anyway.
Regards
Iain
- Original Message -
From: "Karel Zak" <[EMAIL PROTECTED]>
To: "Christoph Haller" <[EMAIL PROTECTED]>
Cc: &q
insert into test1 values ('2002/03/22');
insert into test1 values ('2002/03/32');
insert into test1 values ('200/03/22');
insert into test1 values ('2002/ 3/22');
insert into test1 values ('2002/03/2');
insert into test1 values ('2002/03/ 2'
were added
in a different order. It may be that using different dump options could get
around this though - such as generating the dump as insert statements.
If you come up wth any interesting information as you progress with this,
I'd be happy to hear about it.
Regards
Iain
- Original
much about Postgres' SQL cache, but it is well known in Oracle
circles that using bind variables is is a critical part of system design,
not just for security, but for performance and scalability. I suspect that
the same issues apply more or less to postgres.
Correct me if I'm wrong, ple
(B
(B
(BHi All,
(B
(BI found this interesting and thought I'd
(Boffer it up for comment.
(B
(BI have the following delete:
(B
(Bdelete from tableB where id not in (select
(Bid from tableA);
(B
(BThere are about 100,000 rows in table A,
(Band about 500,000 in table B. id is ind
Hi Tom,
(B
(B> You didn't say what PG version you are using, but I'd venture to bet
(B> it is pre-7.4.
(B
(BSorry I didn't give the version number, here is the output from select
(Bversion():
(B
(BPostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC 2.95.3
(B
(BThis is the first time that
(B
(B
(BDB Version 7,4.
(B
(BI had a look through the docs, but the
(Binformation on table functions (those returning SETOF something) seems to have
(Bgone missing from the 7.4 docs. I found something under 7.3, but it didn't
(Banswer my question, which is...
(B
(BI've written a lit
Hi,
I don't think thatyou gave enough
information to get any direct help, for example, what are these
"sub-selects"?
I often see performance problems arising
from procedural code that puts selects that don't use indexes inside loops.
That's a plain programming issue, and understanding yo
, and for specific
situations such as you mentioned.
HTH
Iain
---(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
howabout:
select sum(case when c = '*' then 0 else 1 end) as count_not_star from
tablename
If you want to process all records but only count thouse without a * in c
then this will do the trick.
regards
iain
- Original Message -
From: "Abdul Wahab Dahalan" &l
ollecting information and trying to isolate the problem.
It would be a good idea to give all the relevant version numbers too
regards
iain
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscr
f the select
and test them using "ANALYSE SELECT ..." .I'm using 7.4.1.
The result from the select is the same, even if you don't make an index on
the function result.It's just a performance consideration. If the index
isn't used, then you don't need to create
Sorry, did I write "ANALYSE SELECT "?
It's supposed to be "EXPLAIN [ANALYSE] [VERBOSE] SELECT ..."
- Original Message -
From: "Daniel Henrique Alves Lima" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, March 09, 2004 1:21 PM
Subject: Re: [SQL] Simple SQL question
> Tha
If you have a lot of tips, you could create a unique indexed tip number
column. Select the highest tip number using:
select tip_number from tips order by tip_number desc limit 1;
Then generate a random number and select using that tip_number.
Of course, you would have to allow for the possibilit
> > IF foo IS NULL
> > THEN ...
> > ELSIF foo
> > THEN ...
> > ELSE ...
> > END IF;
>
> here the foo expression woll be executed twice
if you can use an immutable or stable function then the overhead would be
minimal as the system knows that it doe
Hi,
(B
(Bfor my 2c worth, performance is the least important of the things you need
(Bto consider regarding use of surrogate keys.
(B
(BI use surrogate keys for all situations except the simplest code/description
(Btables, and this is only when the code has no meaning to the application. If
r have happened anyway.
(B
(BThis is a great topic though, I find it really interesting.
(B
(BRegards
(BIain
(B
(B
(B
(B
(B- Original Message -
(BFrom: "Josh Berkus" <[EMAIL PROTECTED]>
(BTo: "Iain" <[EMAIL PROTECTED]>; "Markus Bertheau"
ata (or quality data).
On the whole, I think that there are more important indicators of quality
(or lack of it) in your database design than the prevalence (or lack) of
numeric ID style keys. Personally, I've grown to appreciate the id approach
over the years, but my mind is always
or new knowlege in their patients records,
as applying the knowlege gained from research (done by researchers) to treat
individual patients.
Karsten might beg to differ, I don't know, but the "long flaky text" comment
triggered some old (and fond) memories ;-)
Ch
intern or researcher, but I imagine
it would be best to have the doctor do it at the time.
Just some vague ideas anyway. This may of course be much more work than
anyone wants to get into... I don't have much experience with text searching
systems, but something reasonably sophistic
some information
is in the admin section and some is in the documentation of the client
programs (pg_dump and pg_restore are client programs iirc).
Also, consider upgrading to 7.4 to ensure you get the best support.
Good luck,
Iain
- Original Message -
From: "Worik" <[
(B# select 9223372036854775807 = 9223372036854775807::float;
(B ?column?
(B--
(B t
(B(1 row)
(B
(B# select 9223372036854775807 = 9223372036854775807.0::float;
(B ?column?
(B--
(B t
(B(1 row)
(B
(B# select 9223372036854775807 = 9223372036854775807.::numeric;
(B ?co
, I can't remember last time I designed a database
that used float data...
regards
Iain
---(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
(B
(B
(BHi All,
(B
(BI've come across a situation where I'd
(Blike to use some kind of "out-of-transaction trigger" to do some processing
(Bafter changes to some tables, but without extending the duration of the main
(Btransaction. Of course, it's important that the processing be compl
e a client program to create a session and actually do the listening,
that is if I havn't missed anything else...
Thanks again,
Iain
- Original Message -
From: "Mike Rylander" <[EMAIL PROTECTED]>
To: "Iain" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROT
name of the notification to determine what processing to
activate.
Thanks,
Iain
- Original Message -
From: "Greg Stark" <[EMAIL PROTECTED]>
To: "Iain" <[EMAIL PROTECTED]>
Cc: "Mike Rylander" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sen
Using the the sub-select is one way, but there is another way.
(B
(BIf the function can be declared as strict or immutable the you can call it
(Bas many times as you like in a single transaction and it will only be
(Bevaluated once. As far as I know this does work as advertised.
(B
(BCheck
offending lines and restore using the --use-list option.
If you used the custom format you have a lot of options for extracting
individual table data and stuff like that.
Good luck,
Iain
- Original Message -
From: "Niall Linden" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
I seem to recall it was mine. I made the mistake of assuming it wasn't
concurrency safe and was gently corrected by one of the community. I
think it might have been a Tim/Tam Lane. Wonder what happened to him? :-)
M tim tams
rgds
Homer
---(end of broadcast)
Hi,
(B
(BJust a quick question out of curiosity, I was just wondering if this is
(Bsupposed to be valid sql:
(B
(Bselect count(*) as cnt
(Bfrom sometable
(Bgroup by somecolumn
(Bhaving cnt > 1
(B
(BThis isn't valid in pg (7.4.6), but this is:
(B
(Bselect count(*)
(Bfrom sometable
(Bg
OK, thanks. That seems to make sense.
(B
(Bregards
(BIain
(B- Original Message -
(BFrom: "Tom Lane" <[EMAIL PROTECTED]>
(BTo: "Iain" <[EMAIL PROTECTED]>
(BCc: <[EMAIL PROTECTED]>
(BSent: Thursday, December 02, 2004 11:54 AM
(BSubject: Re:
or,
# update table set birthdate = to_date('2000-01-01','-MM-DD');
I'm not sure which is better though I tend to do it this way.
regards
Iain
- Original Message -
From: "Achilleus Mantzios" <[EMAIL PROTECTED]>
To: "Pablo Digonzelli&quo
27;t you change the key in the emp table
when that key represents the employee's department?". It seems like a
handbook case to me but maybe I missed something.
regards
Iain
I can't figure out an efficient way to do this. Basically I had a typical
3-tier relationship:
(Employee ->
hi,
I'm not familiar with iso2709 but there is a program called Octopus that may do what
you want. It's open source software and can be found at octopus.enhydra.org -
worth a try anyway.
Regards
Iain
- Original Message -
From:
Matteo
Braidotti
To:
Hi Ivan,
Sorry, I can't remember all you said in
earlier posts, but I was wondering, your log file says:
> HINT: Rebuild the index with REINDEX.
Did you do that, and did it solve the
problem?
regards
Iain
- Original Message -
From:
pginfo
To: Tom Lane
Cc
(B
(B
(BHi all,
(B
(BTo take a simple example here is what I
(Bwanted to do:
(B
(B select 1 as one, 2 as two,
(Bone + two as three;
(B
(Bbut it doesn't work. I've checked it out
(Bthough, and I found that I can do this:
(B
(B select one, two, one + two
(Bfrom (select 1 a
40 matches
Mail list logo
