[SQL] PostgreSQL downloads
Does anyone where there is a web site or ftp site where i can download the latest release of PostgreSQL? I have been trying to use the ftp://ftp.postgresql.org but it doesnt work properly. Many thanks in advance Miguel ---(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] PostgreSQL downloads
Hi - http://www.us.postgresql.org/sites.html has a whole list of them... On Mon, 1 Oct 2001, [iso-8859-1] Miguel González wrote: > Does anyone where there is a web site or ftp site where i can download the > latest release of PostgreSQL? I have been trying to use the > ftp://ftp.postgresql.org but it doesnt work properly. > > Many thanks in advance > > Miguel > > > ---(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 > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] On Differing Optimizer Choices ( Again)
Hi, this is a very silly question, but how do I run PL/pgSQL function from SQL? I created a function test(varchar, varchar) and I am trying to invoke it with parameters 'hello', 'world' using an SQL statement. I tried selects and executes - nothing works. Please help, Oleg Mark kirkwood wrote: > Dear all, > > Tom's comments on my previous posting encouraged me think some more about > this... and now I believe got to the heart of what I was attempting to get > accross before. > > I have a fresh and hopefully clear example. > > Ok lets start with a small table called 'dim0' that has a unique key called > 'd0key' ( 1 unique values ). Add to this a large table called 'fact2', > which has 1000 of these 'd0key' values. There are 3000 duplicates for each > value uniformly distributed throughout it. ( total of 300 rows ). > > Consider the query : > > SELECT >f.d0key, >count(f.val) > FROM fact2 f > WHERE f.d0key BETWEEN 270 AND 350 > GROUP BY f.d0key > > which has execution plan : > > Aggregate (cost=0.00..102500.80 rows=2592 width=8) > -> Group (cost=0.00..102436.00 rows=25920 width=8) > -> Index Scan using fact2_pk on fact2 f (cost=0.00..102371.20 > rows=25920 width=8) > > If we use 351 instead of 350 we get a sequential scan. > > Now examine a similar query, but with 'dim0' joined : > > SELECT >f.d0key, >count(f.val) > FROM dim0 d0, > fact2 f > WHERE d0.d0key = f.d0key > AND f.d0key BETWEEN 270 AND 350 > GROUP BY f.d0key > > this has plan : > > Aggregate (cost=0.00..103127.60 rows=2592 width=12) > -> Group (cost=0.00..103062.80 rows=25920 width=12) > -> Merge Join (cost=0.00..102998.00 rows=25920 width=12) > -> Index Scan using dim0_pk on dim0 d0 (cost=0.00..213.00 > rows=1 width=4) > -> Index Scan using fact2_pk on fact2 f (cost=0.00..102371.20 > rows=25920 width=8) > > No surprises there > > (If we use 351, again we get a sequential scan used instead ). > > So far this is all as one would expect. However suppose we substitute > 'd0.d0key' in the 'AND' clause instead of 'f.d0key' to obtain : > > SELECT >f.d0key, >count(f.val) > FROM dim0 d0, > fact2 f > WHERE d0.d0key = f.d0key > AND d0.d0key BETWEEN 270 AND 350 > GROUP BY f.d0key > > Suddenly the plan is : > > Aggregate (cost=103530.27..104293.15 rows=2624 width=12) > -> Group (cost=103530.27..104227.54 rows=26244 width=12) > -> Merge Join (cost=103530.27..104161.93 rows=26244 width=12) > -> Index Scan using dim0_pk on dim0 d0 (cost=0.00..213.00 > rows=1 width=4) > -> Sort (cost=103530.27..103530.27 rows=26244 width=8) > -> Seq Scan on fact2 f (cost=0.00..101604.00 rows=26244 > width=8) > > Now this is interesting, I would have expected an index scan to be still > used... This behavour was what I was seeing ( in disguised form ) in the > queries of the previous posting. > > ( In fact to encourage an index scan changing 350 down to 313 is required ) > > I wonder how 7.1.x behaves when faced with this situation?... a build of an > extra 7.1.3 database I reveals the corrosponding plan for this query is > (note that for 7.1.3 the magic number for index-> sequential scan is 369 > instead of 350 but bear with me) : > > Aggregate (cost=0.00..118850.17 rows=2970 width=12) > -> Group (cost=0.00..118775.91 rows=29703 width=12) > -> Nested Loop (cost=0.00..118701.66 rows=29703 width=12) > -> Index Scan using dim0_pk on dim0 d0 (cost=0.00..67.99 > rows=99 width=4) > -> Index Scan using fact2_pk on fact2 f (cost=0.00..1194.45 > rows=300 width=8) > > So that this version is using an index scan for this query ( in fact will > keep using one until after d0key=445 - so in some sense a behavour opposite > to 7.2dev is being exibited) > > Now the obvious question to ask here is "why are you are griping about using > a seq scan...? ". Timing the queries reveals that the index scan is > considerably faster : specifically 10s against 60s. Additionally 7.1.3 > performs the above query in 10s also - and even "out" at the "extreme end" > using d0.d0key=445 the elapsed time is just 15s . > > Why am I pointing this out ? - well I hope that "field testing" the optimizer > will occasionally provide food for thought ! > > regards > > Mark > > P.s : (I am using 7.2 dev 2001-08-17 and all parameters are default apart > from shared_buffers and sort_mem) > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Calling Functions
Oleg,
> Hi,
> this is a very silly question, but how do I run PL/pgSQL function
> from SQL?
> I created a function test(varchar, varchar) and I am trying to invoke
> it with
> parameters 'hello', 'world' using an SQL statement. I tried selects
> and executes
> - nothing works.
> Please help,
SELECT test('hello','world');
If you aren't getting the result you expect, then you have made a
mistake in your function name, parameters, return type, or a syntax
error in the function itself.
For example, if you do in fact have the function:
CREATE FUNCTION test (VARCHAR, VARCHAR) RETURNS VARCHAR AS '
DECLARE
And if
SELECT test('hello','world');
returns
Parser Error at or near ';'
then the database is telling you that you have a bad function, not that
it doesn't understand the SELECT.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2533
and non-profit organizations. San Francisco
---(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] using SQL to evaluate arbitrary expressions?
Hi, we need to process user-defined integrity conditions, which are stored as strings at application level, e. g. "person.age - 2 > 18". We would like to substitute the variable names within these expressions by their values at trigger time. So we have expressions containing constants only, e. g. "29 - 2 > 18". We are glad to have found out that we can evaluate such expressions simply by calling them with SELECT, e. g. "SELECT 29 - 2 > 18" returns true. We think that it would be very nice to benefit from the pg database system parser. But can we rely on this functionality in the future? Is this standard SQL? Thank you, Markus ---(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] using SQL to evaluate arbitrary expressions?
Markus Wagner <[EMAIL PROTECTED]> writes: > We are glad to have found out that we can evaluate such expressions > simply by calling them with SELECT, e. g. "SELECT 29 - 2 > 18" returns true. > We think that it would be very nice to benefit from the pg database system > parser. But can we rely on this functionality in the future? Is this > standard SQL? Well, it's not standard SQL: SQL92 requires a FROM clause in SELECT. But I can assure you it won't disappear from Postgres; it's too darn useful. The customary workaround, as seen in systems like Oracle, is to have a dummy table containing exactly one row; Oracle calls the dummy table DUAL. (Anyone know why? Seems like SINGLE would be more sensical...) Then you can do SELECT 29 - 2 > 18 FROM dual; The fact that your expression doesn't actually *use* any values from the one table row doesn't bother the system; you get one evaluation of the expression, you're happy, and so is the SQL standard. My advice is to just write "SELECT 29 - 2 > 18", because it's clearer in intent, simpler, and some tiny fraction faster than specifying a useless table reference. But you can take comfort in the fact that there's a simple workaround if you're ever forced to use a DBMS that won't accept this syntax. 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])
