Re: [SQL] SQL standards in Mysql
On Saturday 23 February 2008 07:50, Tom Lane wrote: >Hmm ... while ... > so I'm disinclined to throw the first > stone ... Meanwhile, Throw cones, not stones. http://cfx.kymi.com/lotsacones.jpg These things/projectiles hurt not so much. And it's fun ! BR, -- Aarni Ruuhimäki ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] postgresql function not accepting null values inselect statement
I have tried this, but it is showing following error: ERROR: syntax error at or near "DISTINCT" SQL state: 42601 Thanks, Jyoti -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of johnf Sent: Friday, February 22, 2008 10:01 PM To: [email protected] Subject: Re: [SQL] postgresql function not accepting null values inselect statement On Friday 22 February 2008 01:35:47 am Bart Degryse wrote: > Can you try this... > > CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer) > RETURNS SETOF t_functionaries AS > $BODY$ > DECLARE > rec t_functionaries%ROWTYPE; > BEGIN > FOR rec IN ( > SELECT f.functionaryid, f.category, f.description > FROM functionaries f > WHERE f.statecd IS NOT DISTINCT FROM p_statecd) > LOOP > return next rec; > END LOOP; > return; > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > a newbie question. Could you explain why yours works? I don't understand how it works if p_statecd = NULL -- John Fabiani ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] autovacuum not freeing up unused space on 8.3.0
It appears (and I am open to correction) that autovacuum is not operating correctly in 8.3.0. I have a vanilla installation where autovacuum is enabled, and is running with all the default settings. I have a table which is continually having rows added to it (~50/sec). For the sake of this example I am limiting it to 2 rows, which means that I am continually having to remove rows (100 at a time) as I get to 2. When I get to 2 rows for the first time the table disk size (using pg_total_relation_size) is around 5MB. Since the autovacuum only kicks in after a while I would expect it to get a little bigger (maybe 6-7MB) and then level out as I am cycling through recovered rows. However the table disk size continues increasing basically linearly and when I stopped it it was approaching 40MB and heading up. During that time I was running ANALYZE VERBOSE periodically and I could see the dead rows increase and then drop down as the autovacuum kicked in - the autovacuum worker process was running. It didn't seem to free any space though. In fact a VACUUM FULL at this point didn't help a whole lot either. I ran the same test but using manual VACUUMs every 60 seconds and the table size leveled out at 6.6MB so it appears like a normal vacuum is working. I changed the normal VACUUM to have the same delay parameters (20ms) as the autovacuum and it still worked. So it appears to me like the autovacuum is not freeing up dead rows correctly. I turned on logging for autovacuum and ran the same test and saw the following messages: LOG: automatic vacuum of table "metadb.test.transactions": index scans: 1 pages: 0 removed, 254 remain tuples: 4082 removed, 19957 remain system usage: CPU 0.02s/0.02u sec elapsed 1.11 sec LOG: automatic vacuum of table "metadb.test.transactions": index scans: 1 pages: 0 removed, 271 remain tuples: 5045 removed, 19954 remain system usage: CPU 0.03s/0.03u sec elapsed 1.54 sec ERROR: canceling autovacuum task CONTEXT: automatic vacuum of table "metadb.test.transactions" At this point I had deleted 32800 rows as can be seen from the query below, although the logs only indicated that around 1 rows had been freed up. select min(transaction_key),max(transaction_key) from test.transactions; min | max ---+--- 32801 | 52750 Is there anything I have missed as far as setting this up is concerned, anything I could try? I would really rather use autovacuum than manage the vacuums of a whole lot of tables by hand... Thanks Stuart PS. Running on NetBSD 3 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] autovacuum not freeing up unused space on 8.3.0
It seems like pgsql-general would be the right list for this so I am going to post it there rather, sorry for the noise... It appears (and I am open to correction) that autovacuum is not operating correctly in 8.3.0. I have a vanilla installation where autovacuum is enabled, and is running with all the default settings. I have a table which is continually having rows added to it (~50/sec). For the sake of this example I am limiting it to 2 rows, which means that I am continually having to remove rows (100 at a time) as I get to 2. When I get to 2 rows for the first time the table disk size (using pg_total_relation_size) is around 5MB. Since the autovacuum only kicks in after a while I would expect it to get a little bigger (maybe 6-7MB) and then level out as I am cycling through recovered rows. However the table disk size continues increasing basically linearly and when I stopped it it was approaching 40MB and heading up. During that time I was running ANALYZE VERBOSE periodically and I could see the dead rows increase and then drop down as the autovacuum kicked in - the autovacuum worker process was running. It didn't seem to free any space though. In fact a VACUUM FULL at this point didn't help a whole lot either. I ran the same test but using manual VACUUMs every 60 seconds and the table size leveled out at 6.6MB so it appears like a normal vacuum is working. I changed the normal VACUUM to have the same delay parameters (20ms) as the autovacuum and it still worked. So it appears to me like the autovacuum is not freeing up dead rows correctly. I turned on logging for autovacuum and ran the same test and saw the following messages: LOG: automatic vacuum of table "metadb.test.transactions": index scans: 1 pages: 0 removed, 254 remain tuples: 4082 removed, 19957 remain system usage: CPU 0.02s/0.02u sec elapsed 1.11 sec LOG: automatic vacuum of table "metadb.test.transactions": index scans: 1 pages: 0 removed, 271 remain tuples: 5045 removed, 19954 remain system usage: CPU 0.03s/0.03u sec elapsed 1.54 sec ERROR: canceling autovacuum task CONTEXT: automatic vacuum of table "metadb.test.transactions" At this point I had deleted 32800 rows as can be seen from the query below, although the logs only indicated that around 1 rows had been freed up. select min(transaction_key),max(transaction_key) from test.transactions; min | max ---+--- 32801 | 52750 Is there anything I have missed as far as setting this up is concerned, anything I could try? I would really rather use autovacuum than manage the vacuums of a whole lot of tables by hand... Thanks Stuart PS. Running on NetBSD 3 ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] postgresql function not accepting null values inselect statement
http://www.postgresql.org/docs/current/static/functions-comparison.html This document states this: Lets assume: A = NULL B = 10 C = NULL SELECT 1 WHERE A = B returns no rows SELECT 1 WHERE A = C returns no rows (even though both A and C are NULL) SELECT 1 WHERE A IS NOT DISTINCT FROM C returns 1 row. essentially the third SQL statement works because it is equivalent to this: SELECT 1 WHERE (A IS NULL AND C IS NULL) OR (A = C) *Robins* On Fri, Feb 22, 2008 at 10:00 PM, johnf <[EMAIL PROTECTED]> wrote: > On Friday 22 February 2008 01:35:47 am Bart Degryse wrote: > > Can you try this... > > > > CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer) > > RETURNS SETOF t_functionaries AS > > $BODY$ > > DECLARE > > rec t_functionaries%ROWTYPE; > > BEGIN > > FOR rec IN ( > > SELECT f.functionaryid, f.category, f.description > > FROM functionaries f > > WHERE f.statecd IS NOT DISTINCT FROM p_statecd) > > LOOP > > return next rec; > > END LOOP; > > return; > > END; > > $BODY$ > > LANGUAGE 'plpgsql' VOLATILE; > > > a newbie question. Could you explain why yours works? I don't understand > how > it works if p_statecd = NULL > > > -- > John Fabiani > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings >
Re: [SQL] postgresql function not accepting null values inselect statement
> > What version of PostGreSQL are you using ? > Are you sure there was no typing error ? This SQL should work in the most > recent version of PG.( at least version 8.1 onwards) > > *Robins* > > > On Mon, Feb 25, 2008 at 10:50 AM, Jyoti Seth <[EMAIL PROTECTED]> > wrote: > > > I have tried this, but it is showing following error: > > ERROR: syntax error at or near "DISTINCT" > > SQL state: 42601 > > > > Thanks, > > Jyoti > > > > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto: > > [EMAIL PROTECTED] > > On Behalf Of johnf > > Sent: Friday, February 22, 2008 10:01 PM > > To: [email protected] > > Subject: Re: [SQL] postgresql function not accepting null values > > inselect > > statement > > > > On Friday 22 February 2008 01:35:47 am Bart Degryse wrote: > > > Can you try this... > > > > > > CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer) > > > RETURNS SETOF t_functionaries AS > > > $BODY$ > > > DECLARE > > > rec t_functionaries%ROWTYPE; > > > BEGIN > > > FOR rec IN ( > > > SELECT f.functionaryid, f.category, f.description > > > FROM functionaries f > > > WHERE f.statecd IS NOT DISTINCT FROM p_statecd) > > > LOOP > > > return next rec; > > > END LOOP; > > > return; > > > END; > > > $BODY$ > > > LANGUAGE 'plpgsql' VOLATILE; > > > > > a newbie question. Could you explain why yours works? I don't > > understand > > how > > it works if p_statecd = NULL > > > > > > -- > > John Fabiani > > > > ---(end of broadcast)--- > > TIP 5: don't forget to increase your free space map settings > > > > > > ---(end of broadcast)--- > > TIP 4: Have you searched our list archives? > > > > http://archives.postgresql.org > > > >
