[SQL] Does optimizer know about 'constant' expressions?
It seems that the optimizer does not know about (or calculate values of)
constant expressions when determining strategy. Perhaps I am doing
something silly, but:
select tmax from ping where pingtime > current_timestamp - interval '2
hour'
order by pingtime asc limit 30;
is very slow, and:
select tmax from ping where pingtime > '17-sep-2000 16:40'
order by pingtime asc limit 30;
works fine.
Is this a known issue?
-
This one is seriosly slow:
uptime=# explain verbose select tmax from ping where pingtime >
current_timestamp - interval '2 hour' ord
er by pingtime asc limit 30;
NOTICE: QUERY DUMP:
{ INDEXSCAN :startup_cost 0.00 :total_cost 53962.69 :rows 84746 :width 12
:state <> :qptargetlist ({ TARG
ETENTRY :resdom { RESDOM :resno 1 :restype 700 :restypmod -1 :resname tmax
:reskey 0 :reskeyop 0 :ressort
groupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 4 :vartype 700
:vartypmod -1 :varlevelsup 0 :
varnoold 1 :varoattno 4}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype
1184 :restypmod -1 :resname pi
ngtime :reskey 0 :reskeyop 0 :ressortgroupref 1 :resjunk true } :expr { VAR
:varno 1 :varattno 7 :vartype
1184 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 7}}) :qpqual ({
EXPR :typeOid 16 :opType op :
oper { OPER :opno 1324 :opid 1157 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 7 :vartype 1184 :var
typmod -1 :varlevelsup 0 :varnoold 1 :varoattno 7} { EXPR :typeOid 1184
:opType op :oper { OPER :opno 1
329 :opid 1190 :opresulttype 1184 } :args ({ EXPR :typeOid 1184 :opType
func :oper { FUNC :funcid 1191 :
functype 1184 :funcisindex false :funcsize 0 :func_fcache @ 0x0
:func_tlist ({ TARGETENTRY :resdom { RES
DOM :resno 1 :restype 1184 :restypmod -1 :resname \ :reskey 0
:reskeyop 0 :ressortgroupref 0 :res
junk false } :expr { VAR :varno -1 :varattno 1 :vartype 1184 :vartypmod -1
:varlevelsup 0 :varnoold -1 :
varoattno 1}}) :func_planlist <>} :args ({ CONST :consttype 25 :constlen -1
:constisnull false :constvalu
e 7 [ 7 0 0 0 110 111 119 ] :constbyval false })} { CONST :consttype 1186
:constlen 12 :constisnull fal
se :constvalue 12 [ 0 0 0 0 0 32 -68 64 0 0 0 0 ] :constbyval false
})})}) :lefttree <> :righttree <> :
extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 :indxid ( 1852428)
:indxqual (<>) :indxqualorig (
<>) :indxorderdir 1 }
NOTICE: QUERY PLAN:
Index Scan using ping_ix1 on ping (cost=0.00..53962.69 rows=84746 width=12)
EXPLAIN
-
and this one is fine:
uptime=# explain verbose select tmax from ping where pingtime >
'17-sep-2000 16:40' order by pingtime asc
limit 30;
NOTICE: QUERY DUMP:
{ INDEXSCAN :startup_cost 0.00 :total_cost 61.98 :rows 18 :width 12 :state
<> :qptargetlist ({ TARGETENTR
Y :resdom { RESDOM :resno 1 :restype 700 :restypmod -1 :resname tmax
:reskey 0 :reskeyop 0 :ressortgroupr
ef 0 :resjunk false } :expr { VAR :varno 1 :varattno 4 :vartype 700
:vartypmod -1 :varlevelsup 0 :varnoo
ld 1 :varoattno 4}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 1184
:restypmod -1 :resname pingtime
:reskey 0 :reskeyop 0 :ressortgroupref 1 :resjunk true } :expr { VAR
:varno 1 :varattno 7 :vartype 1184
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 7}}) :qpqual <>
:lefttree <> :righttree <> :extprm (
) :locprm () :initplan <> :nprm 0 :scanrelid 1 :indxid ( 1852428)
:indxqual (({ EXPR :typeOid 16 :opTyp
e op :oper { OPER :opno 1324 :opid 1157 :opresulttype 16 } :args ({ VAR
:varno 1 :varattno 1 :vartype 118
4 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 7} { CONST
:consttype 1184 :constlen 8 :constisnul
l false :constvalue 8 [ 0 0 0 0 60 114 117 65 ] :constbyval false })}))
:indxqualorig (({ EXPR :typeOid
16 :opType op :oper { OPER :opno 1324 :opid 1157 :opresulttype 16 } :args
({ VAR :varno 1 :varattno 7 :
vartype 1184 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 7} {
CONST :consttype 1184 :constlen 8
:constisnull false :constvalue 8 [ 0 0 0 0 60 114 117 65 ] :constbyval
false })})) :indxorderdir 1 }
NOTICE: QUERY PLAN:
Index Scan using ping_ix1 on ping (cost=0.00..61.98 rows=18 width=12)
EXPLAIN
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 |/
Re: [SQL] no ORDER BY in subselects?
At 15:23 20/09/00 +0200, Louis-David Mitterrand wrote: > >ERROR: parser: parse error at or near "order" > >Aren't ORDER BY clauses allowed in subselects? > It is a very very sad fact, but, no, they're not. -------- 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 |/
Re: [Fwd: Re: [SQL] no ORDER BY in subselects?]
At 09:20 20/09/00 -0700, Josh Berkus wrote: >Ooops, posted this to Phillip rather than the list, sorry Phillip ... > >Folks, > >Philip Warner wrote: >> >> At 15:23 20/09/00 +0200, Louis-David Mitterrand wrote: >> > >> >ERROR: parser: parse error at or near "order" >> > >> >Aren't ORDER BY clauses allowed in subselects? >> > >> >> It is a very very sad fact, but, no, they're not. > >H ... can't say as I've ever seen an ORDER BY in a subselect before. >Why would you want one? > The main reason I use them is to find the 'next' or 'previous' record in a list (eg. next date, next ID). eg. select , (select ID from table where id > this.id order by id asc limit 1) as next_id ... OR select , (select Start_Date from table where Start_Date > this.Start_Date Order By Start_Date asc limit 1) as End_Date >And if you do want one, Louis-David, you can always use a temporary >table as previously described. It is A LOT less clean. The fact that Dec RDB, Oracle and SQL/Server all allow it probably means that there is a reasonable user base out there who think it's a good idea. 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 |/
Re: [Fwd: Re: [SQL] no ORDER BY in subselects?]
At 11:50 21/09/00 -0400, Frank Bax wrote: >At 11:29 AM 9/21/00 +1000, you wrote: >>The main reason I use them is to find the 'next' or 'previous' record in a >>list (eg. next date, next ID). eg. >> >> select , (select ID from table where id > this.id >> order by id asc limit 1) as next_id ... > >Doesn't this give the same result (without order by): > >> select , (select min(ID) from table where id > this.id) as >next_id Yes, but I don't think PostgreSQL is smart enough to use indexes to evaluate the Min() function. Also, min/max does not work quite so well with a slightly more complex example: select , (select ID from table where date_field > this.date_field order by date_field asc limit 1) as next_id ... (ie. if the date_field and id are not correlated, but you want the id corresponding to the next date). 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 |/
[SQL] Re: [HACKERS] RFC - change of behaviour of pg_get_userbyid & pg_get_viewdef?
At 19:22 24/09/00 -0500, Dominic J. Eidson wrote: > >In these cases, is NULL = 0? - What if it returns the UID for "root" >(typically UID 0)... I think an error message should/would be better in >this case. > No NULL is NULL, a special value that usually means 'nothing found'. -------- 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 |/
[SQL] Change of behaviour of pg_get_userbyid & pg_get_viewdef - do it?
Based on the lack of reaction on GENERAL and SQL, I am inclined to go ahead with the changes below at least as far as returning NULL instead of 'Not a View' or 'unknown (UID=)' (as per Tom's request), if noone objects... At 13:15 24/09/00 +1000, Philip Warner wrote: > >Two routines do eccentric things when they can't find required supporting >data: > >pg_get_userbyid > >returns 'unknown (UID=)' when the UID does not exist. > >pg_get_viewdef > >returns 'Not a view' when passed a non-existant or non-view table >it also signals errors when the underlying metadata can not be found. > >The proposal is to return NULL in the above cases - in the final case, >probably also generate a NOTICE. > >Does anybody have a problem with this? Think it's a bad idea etc? > 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 |/
Re: [SQL] how can I mirror a postgres database
7.1 will have replication curtesy of PostgreSQL Inc: http://www.pgsql.com/press/Pgsql_Inc_Press-5.pdf (it is being freely donated to the source tree, according to their statement) 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 |/
Re: [SQL] Can I use subselect as a function parameter?
At 14:42 11/10/00 +0100, Oliver Elphick wrote: > > CONSTRAINT ean CHECK ( > CASE WHEN eancode IS NULL OR brand IS NULL > THEN 't' > ELSE ean_checkdigit( > (SELECT ean_prefix > FROM brandname, product > WHERE brandname.id = product.brand > ), eancode) > END > ) >); > > >Can this be made to work at all (in 7.0.2)? > The short answer is that subselect in CHECK is not currently supported, and is not planned for 7.1. There was a thread about subselect in CHECK not so long ago (see 'checking number of entries' on or around the 29/9). There was apparently also an earlier discussion in which the meaning were not deemed to be clear. I think the most recent discussion at least resolved the meaning, but not the locking issues. -------- 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 |/
Re: [SQL]
At 13:36 30/10/00 +0100, Lengyel Ferenc wrote: >Hello everybody! >I need some help with a simple query. >I've got a problem with getting a maximum value from a very large table >(1000+ rows): Unfortunately PGSQL does not use indexes for min & max. One approach that should work is: select col1 from TABLE_A WHERE COL2 = 1 order by col2, col1 desc limit 1; and you will need an index on (col2, col1). -------- 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 |/
Re: [SQL] how to continue a transaction after an error?
At 14:08 13/11/00 -0600, Ross J. Reedstrom wrote: >On Mon, Nov 13, 2000 at 09:41:04PM +0200, Cristi Petrescu-Prahova wrote: >> Hello, >> >> I would like to insert a bunch of rows in a table in a transaction. Some of >> the insertions will fail due to constraints violation. When this happens, >> Postgres automatically ends the transaction and rolls back all the previous >> inserts. I would like to continue the transaction and issue the >> commit/rollback command myself. >> >> How to do it? >> Is there any setting I am missing? >> Is it possible at all? > Not possible; the error handling in PGSQL is a bit of a mess (not necessarily a fault of PG), and it's not possible (currently) to rollback single statements inside a larger transaction. This feature has to come, but *not* AFAICT in the next release. >But seriously, this comes up from time to time. PostgreSQL is a little >stricter than most DBMSs with regards to transactional semantics, but >there are good reasons for this, involving tradeoffs of locking, MVCC, >"autocommit" mode, etc. Not to mention the fact that we did not support sub-transactions. But I think WAL does this for us, so we can hope for the feature RSN. >When you start a transaction, >you're telling the backend "treat all of these statements as one, big, >all or nothing event." This is actually contrary to the standard. Statements are atomic, and a failed statement should not abort the TX: The execution of all SQL-statements other than SQL-control statements is atomic with respect to recovery. Such an SQL-statement is called an atomic SQL-statement. ... An SQL-transaction cannot be explicitly terminated within an atomic execution context. If the execution of an atomic SQL-statement is unsuccessful, then the changes to SQL-data or schemas made by the SQL-statement are canceled. >If you want (need, if you're using large objects) transactions, you >really need to think about your transaction boundries. Don't just wrap >your whole frontend in one big, long lived transaction Totally agree; transactions will keep locks. Release them as soon as the business rules and application design says that you can. Note that commit-time constraints may make the commit fail; in this case PG will force a rollback, but it *should* allow corrective action and another attempt at a commit. >close and reopen >your transaction for those inserts that are allowed to fail. This is very good advice for PGSQL, but bad advice for general DB programming. At the end of the day, the database is the final arbiter of valid data (through triggers, constraints etc that implement business rules). Since we don't want to duplicate all of the rules from the database within the application, we need to rely on the database telling us that the last operation failed so that we can *choose* to rollback or choose to change the processing. A classic example would be processing batches of data - open a file, read a line, insert it, if the data is bad, insert it into a list of exceptions, keep reading file; this needs to be done in one TX since after commit we delete the file, and we don't want partially loaded batches if the machine crashes. I admit this example demonstrates my age, but this kind of processing is still necessary. 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 |/
Re: [SQL] how to continue a transaction after an error?
At 22:23 13/11/00 -0800, Stephan Szabo wrote: >Admittedly, having an SQLSTATE style error code would help once we had >that so you could actually figure out what the error was. Yep, that would be nice. >> Totally agree; transactions will keep locks. Release them as soon as the >> business rules and application design says that you can. Note that >> commit-time constraints may make the commit fail; in this case PG will >> force a rollback, but it *should* allow corrective action and another >> attempt at a commit. > >This I disagree with for commit time constraints unless stuff was changed >between the draft I have and final wording: > "When a is executed, > all constraints are effectively checked and, if any constraint > is not satisfied, then an exception condition is raised and the > transaction is terminated by an implicit ." Just checked the SQL99 stuff, and you are quite right - commit it terminal no matter what. >If you're committing then you're saying >you're done and that you want the transaction to go away. Not only that, but trying to unravel a constraint failure at commit-time would (except in trivial cases) be almost impossible. Best thing is to rollback. >If you just >want to check deferred constraints, there's set constraints mode. True. >I could >almost see certain recoverable internal state things being worth not doing >a rollback for, but not constraints. Not true, eg, for FK constraints. The solution may be simple and the application needs the option to fix it. Also, eg, the triggered data *could* be useful in reporting the error (or fixing it in code), so an implied rollback is less than ideal. Finally, custom 'CHECK' constraints could be designed for exactly this purpose (I have done this in DBs before). 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 |/
Re: [SQL] Subselect in join?
At 18:24 29/11/00 -0600, Kenn Thompson wrote: > >In english- is it not possible to to a subselect in a FROM clause? > In 7.1. ---- 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 |/
Re: [SQL] pg_dump error
At 22:13 15/01/01 +0200, Johann Spies wrote: > >Can not create pgdump_oid table. Explanation from backend: 'ERROR: >cannot create pgdump_oid > >Is there a way to repair this? > If there was an earlier crash while running pg_dump, the table may already exist. If so, try deleting it. Also, can you confirm that the username under which you run pg_dump has the rights to create tables? -------- 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 |/
Re: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
At 18:14 27/03/01 -0500, Tom Lane wrote: >Forest Wilkinson <[EMAIL PROTECTED]> writes: >> session1<< create function nextid( varchar(32)) returns int8 as ' >> session1<< select * from idseq where name = $1::text for update; >> session1<< update idseq set id = id + 1 where name = $1::text; >> session1<< select id from idseq where name = $1::text; >> session1<< ' language 'sql'; >> [ doesn't work as expected in parallel transactions ] > >What I find is that at the final >SELECT, the function can see both the tuple outdated by the other >transaction AND the new tuple it has inserted. Surely we should distinguish between real new tuples, and new tuple versions? I don't think it's ever reasonable behaviour to see two versions of the same row. >(You can demonstrate >that by doing select count(id) instead of select id.) Whichever one >happens to be visited first is the one that gets returned by the >function, and that's generally the older one in this example. > >MVCC seems to be operating as designed here, more or less. The outdated >tuple is inserted by a known-committed transaction, and deleted by a >transaction that's also committed, but one that committed *since the >start of the current transaction*. So its effects should not be visible >to the SELECT, and therefore the tuple should be visible. The anomalous >behavior is not really in the final SELECT, but in the earlier commands >that were able to see the effects of a transaction committed later than >the start of the second session's transaction. Looking at the docs, I see that 'SERIALIZABLE' has the same visibility rules as 'READ COMMITTED', which is very confusing. I expect that a Read Committed TX should see committed changes for a TX that commits during the first TX (although this may need to be limited to TXs started before the first TX, but I'm not sure). If this is not the case, then we never get non-repeatable reads, AFAICT: P2 (Non-repeatable read): SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted. which is one of the differences between SERIALIZABLE and READ-COMMITTED. >The workaround for Forest is to make the final SELECT be a SELECT FOR >UPDATE, so that it's playing by the same rules as the earlier commands. Eek. Does this seem good to you? I would expect that SELECT and SELECT...FOR UPDATE should return the same result set. >But I wonder whether we ought to rethink the MVCC rules so that that's >not necessary. I have no idea how we might change the rules though. Disallowing visibility of two versions of the same row would help. 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 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
At 09:58 28/03/01 -0800, Mikheev, Vadim wrote: > >Reported problem is caused by bug (only one tuple version must be >returned by SELECT) and this is way to fix it. > I assume this is not possible in 7.1? ---- 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 |/ ---(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: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
At 19:14 29/03/01 -0800, Mikheev, Vadim wrote: >> >Reported problem is caused by bug (only one tuple version must be >> >returned by SELECT) and this is way to fix it. >> > >> >> I assume this is not possible in 7.1? > >Just looked in heapam.c - I can fix it in two hours. >The question is - should we do this now? >Comments? It's a bug; how confident are you of the fix? -------- 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 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
At 13:16 30/03/01 +0900, Hiroshi Inoue wrote: >Philip Warner wrote: >> >> At 19:14 29/03/01 -0800, Mikheev, Vadim wrote: >> >> >Reported problem is caused by bug (only one tuple version must be >> >> >returned by SELECT) and this is way to fix it. >> >> > >> >> >> >> I assume this is not possible in 7.1? >> > >> >Just looked in heapam.c - I can fix it in two hours. >> >The question is - should we do this now? >> >Comments? >> >> It's a bug; how confident are you of the fix? >> > >I doubt if it's a bug of SELECT. No idea where the bug is, but SELECT should never return two versions of the *same* row. >'Well what >'concurrent UPDATE then SELECT FOR UPDATE + >SELECT' return ? No idea, maybe Vadim or Tom can help? 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 |/ ---(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] Re: Restriction by grouping problem.
A way to do this is: Select GETFILE From (Select Min(DATETIME),SID From LOGS Group by SID) as MDT, LOGS L Where L.SID = MDT.SID And L.DATETIME = MDT.DATETIME But this fails if there are more than one row for a given SID/DATETIME pair (doe you have a unique index on them?). Alternatively, you could try: Select (Select GETFILE From LOGS L Where L.SID = S.SID Order By DATETIME Asc Limit 1) as GETFILE From (Select Distinct SID From LOGS) as S ...the FROM statement would be a lot nicer if your had a table of SIDs, or at least had a good way of generating the list of SIDs. [Not sure if I got the PG dialect right] At 18:03 26/07/01 -0400, Jeff Barrett wrote: > >Logs table has a primary key of logid (int4) and serveral columns, of which >I am deling with sid (text), getfile (text), and datetime (int4). Now a >select getfile, datetime, logid from logs where sid = onevalue; would return >a set of rows for that sid, the row I want is for the one with the smallest >aka min(datetime) and I want this for every row in the table. -------- 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 |/ ---(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] Re: Restriction by grouping problem.
At 11:47 27/07/01 +1000, Philip Warner wrote: > >...the FROM statement would be a lot nicer if your had a table of SIDs, or >at least had a good way of generating the list of SIDs. > A final (& possibly best) option, is: Select MDT.GETFILE, [anything else from 'best' record] From (Select Distinct SID From LOGS) as S, (Select * From LOGS L Where L.SID = S.SID Order By DATETIME Asc Limit 1) as MDT But unfortunately PG 7.1 does not allow this - the second select can not reference the first select. I have not tried it in 7.1.2. -------- 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 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Re: Restriction by grouping problem.
At 17:19 27/07/01 -0700, Josh Berkus wrote: >Phillip, > >> What it effectively does is executes the second SELECT for each row >> of the >> first select, a bit like a column-select expression, but it allows >> access >> to all rows. > >Ah! I knew that there had to be a simple way to do what you want to do >in PGSQL: The first two examples I sent do it 'simply' in PG. Unfortunately the example you give will only return one row, and since Jeff wanted one gettime for each SID, we'd need to modify both your and my first solution to: Select SID,Min(GETFILE) From (Select Min(DATETIME),SID From LOGS Group by SID) as MDT, LOGS L Where L.SID = MDT.SID And L.DATETIME = MDT.DATETIME This will work, but will not solve the larger problem of 'give me all the attrs of of the record with the least datetime for a given SID'. Jeff does not need this, but it is a common problem. Simply using min/max works for one attr, but fails to provide a consistent view of multiple attrs. PG has no elegant single-statement solution to this problem. The SQL solution I use in other systems is, as I mentioned earlier: Select [any list of attrs from the 'best' record] From (Select Distinct SID From LOGS) as S, -- Get the grouping attrs (Select * From LOGS L Where L.SID = S.SID -- For each group do a 'row-select' Order By DATETIME Asc Limit 1) as MDT The second select can be thought of as analagous to a column-select-expression, but allowing access to all attrs of the resulting row. If you want f1,f2,and f3 from LOGS, then a similar result would be achieved (inelegantly) by: Select (Select F1 From LOGS L Where L.SID = S.SID Order By DATETIME Asc Limit 1) as F1, (Select F2 From LOGS L Where L.SID = S.SID Order By DATETIME Asc Limit 1) as F2, (Select F3 From LOGS L Where L.SID = S.SID Order By DATETIME Asc Limit 1) as F3 From (Select Distinct SID From LOGS) as S, -- Get the grouping attrs Assuming DATETIME is unique then F1, F2, F3 will all come from the same row and you will have a consistent record. I have not checked, but I'd guess that PG will create a cross block with four entries, whereas the original syntax above should just use 2 entries. Like I said, it's just an optimizer hint. 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 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] EXIST / NOT EXIST
At 09:01 AM 3/12/2002 +0100, Rachel.Vaudron wrote: I would like to know if the keyword EXIST can be used with PostgreSQL ? EXISTS is supported (ie. trailing 'S'). -------- Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(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] Performance Problems
At 09:56 AM 12/12/2002 -0300, Héctor Iturre wrote: I 've perform severals VACUMM on the database with poor results. Have you done an ANALYZE? Which version of PG are you using? Can you send output from VACUUM VERBOSE ? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Help on (sub)-select
At 10:21 AM 20/12/2002 +, Gary Stainburn wrote: nymr=# select r.*, s.tally from roster r, nymr-# (select count(*) as tally from roster_staff where nymr(# rsdate = '2002-01-01' and rsgid = 11 and rsgsid = 2) as s nymr-# where rodate = '2002-01-01' and rogid = 11 and rogsid = 2; rodate | rogid | rogsid | rorequired | rooptional | tally +---++++--- 2002-01-01 |11 | 2 | 0 | 1 | 2 (1 row) Try something like: select r.*, count(*) from roster r, roster_staff s where rsdate = rodate and rsgid = rogid and rsgsid = rogid group by r.* or select r.*, (select count(*) from roster_staff s where rsdate = rodate and rsgid = rogid and rsgsid = rogid ) roster r May not be exactly right, but you should get the idea -------- Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Help on (sub)-select
At 11:48 AM 20/12/2002 +, Gary Stainburn wrote: > select r.*, count(*) from roster r, roster_staff s > where rsdate = rodate and rsgid = rogid and rsgsid = rogid > group by r.* This one came up with a parser error near '*' but I don't understand it enough to debug it. Replace the two occurrences of r.* with the list of fields in r that you want to group the count by. -------- Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] create index on function - why?
At 11:46 AM 15/02/2002 -0500, Tom Lane wrote: A functional index is sort of like a hidden, precomputed column added to your table. One of the things I keep running into is the desire for a mixed functional/data index. I know the workaround is to build a function that combines all fields, but would it be hard to allow mixed indexes: create table users(id integer, last_name text); create index users_id_name on users(id,lower(name)); ? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] from PG_DUMP to CVS
At 08:04 PM 26/08/2004, Riccardo G. Facchini wrote: Does somebody know of a script that does this job? No, but a very useful idea. Sounds like another dump format to me -- so long as a well-defined structure that is likely to remain invariant over versions can be used. A client uses a trivial script to dump functions, but not tables, triggers or anything else. The simplest format might be to use the database names as a level 1 dir, schema names at level 2, and entry names at level 3, then create files for the 'definition', 'data' etc. It would be a little confusing when ACLs and constraints were dumped (eg. they would not appear under the tables). But it would be consistent, at least. Of course it would be impossible to restore from such a beast since we would be throwing away ordering -- I think. -------- Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(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
Re: [SQL] from PG_DUMP to CVS
At 08:43 PM 26/08/2004, Riccardo G. Facchini wrote: If you know of something even similar to what I'm looking for, let me know. My thinking is to modify pg_dump to add a new output format, but I'd like to get some more feedback from others first, including yourself. Does what I specified before satisfy all your needs? -------- Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(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] from PG_DUMP to CVS
At 02:38 PM 27/08/2004, Josh Berkus wrote: If it's Perl, I'd be interested in contributing. I've long needed something like this myself. My thinking at this stage is to try to get pg_dump/restore to produce the output directly. Something like: some-dbname/create.sql some-dbname/drop.sql some-dbname/econding.sql ... some-dbname/some-schema/TABLE/sometable/create.sql some-dbname/some-schema/TABLE/sometable/drop.sql ... some-dbname/some-schema/CONSTRAINT/someconstraint/create.sql some-dbname/some-schema/CONSTRAINT/someconstraint/create.sql ... some-dbname/some-schema/ACL/some-table.sql some-dbname/some-schema/ACL/some-function(int).sql ... etc. This would be easy. Question is, how useful would it be? -------- Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] from PG_DUMP to CVS
At 02:02 AM 28/08/2004, Josh Berkus wrote:
some-dbname/some-schema/TABLES/sometable.sql
some-dbname/some-schema/VIEWS/someview.sql
some-dbname/some-schema/FUNCTIONS/somefunction-param{codes}.sql
some-dbname/some-schema/TYPES/sometype.sql
some-dbname/some-schema/OPERATORS/OPsomeoperator.sql
In this last, all dependant objects of, for example, a table (rules,
triggers,
indexes, etc. ) would be rolled up into one file. It's this last version
that I personally favor.
Not sure I like it from the PoV of easily seeing what has changed.
Similarly:
some-dbname/some-schema/TABLES/sometable/create.sql
some-dbname/some-schema/TABLES/sometable/indexes.sql
some-dbname/some-schema/TABLES/sometable/constraints.sql
combines all indexes into one file. It also has the disadvantage if being
impossible to construct from an existing dump file.
I'd like to be able to construct the structure from the information stored
in a dump file, without parsing SQL. OTOH, it might be nice to add some
more information to the dump file.
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330 3172 | ___ |
Http://www.rhyme.com.au |/ \|
|----
PGP key available upon request, | /
and from pgp.mit.edu:11371 |/
---(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
Re: [SQL] SQL question
At 12:07 17/07/00 +1000, Carolyn Lu Wong wrote: >I have table with the following definition: > > create table table1( > account_no int4, > start_date_tme datetime > > ); > >The table may contain null values for start_date_time. > >When I run the following SQL query, it fails: > > select * from table1 > where start_date_time::date >= '01/01/2000'::date > and start_date_time::date <= '01/01/2001'::date; > >I get error message 'Unable to convert null datetime to date. > >It's fine if I run the same SQL query with added condition as follows: > > select * from table1 >> where account_no = 1 > and start_date_time::date >= '01/01/2000'::date > and start_date_time::date <= '01/01/2001'::date; > >Is this a bug? Or there's an logical explaination for this? The most logical explanation is that there are no null values in start_date_time when account_no = 1. Try select count(*) from table1 where account_no = 1 and start_date_time is null; and see if you get 0. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.C.N. 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 |/
Re: [SQL] SQL question
At 13:41 17/07/00 +1000, Carolyn Lu Wong wrote: >> Try >> select count(*) from table1 where account_no = 1 and start_date_time is >> null; >> >> and see if you get 0. > >Yes, i get 0 from running the above query, but it fails if i re-arrange >the where clause to: > > select * from table1 > where start_date_time::date >= '01/01/2000'::date > and start_date_time::date <= '01/01/2001'::date > and account_no = 1; > >with the same error message. > I think that there is no guarantee of the order of evaluation of the components of a predicate, but that putting 'account_no=1' early in the statement means the row is excluded before it needs to evaluate the rest of the statement. I don't think you should rely on this behaviour - it might be classified as "it's a feature, not a bug". Maybe. You probably need to tell me what NULLs in the start_date mean. If, eg, they mean 'not started', then you could create a view: create view started_things as select * from table1 where not start_date is null; then use: select * from started_things where start_date_time::date >= '01/01/2000'::date and start_date_time::date <= '01/01/2001'::date and account_no = 1; AFAICT, this will still use nice indexes etc, but I could be wrong. Another alternative would be to define a 'coalesce' function (I don't think PG has one), which takes an arbitrary number of arguments and returns the first non-null one. You could then say "where coalesce(start_date_time, '1/1/1500')::date >= '01/01/2000'::date" etc, but then I think you will lose the effectiveness of indexes. Maybe someone else has a better idea... Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.C.N. 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 |/
Re: [SQL] SQL question
At 02:26 17/07/00 -0400, Tom Lane wrote: > >Well before my time, I guess --- as long as I've been paying attention, >the function manager's approach was to call the routine first and *then* >insert a NULL result ... if the routine hadn't crashed first. That's >about as braindead a choice as I can think of, but that's what it did. > Out of curiosity, what does it do now? -------- Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.C.N. 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 |/
[SQL] Strange strategy with COALESCE?
I get unexpected query strategy when using coalesce. Good: - explain update zzz set b = (select x.newVal from zzz_xref x where x.oldVal = zzz.b); NOTICE: QUERY PLAN: Seq Scan on zzz (cost=0.00..20.00 rows=1000 width=10) SubPlan -> Index Scan using zzz_xref_ix2 on zzz_xref x (cost=0.00..726.82 rows=983 width=4) Bad (just by adding a COALESCE call): - explain update zzz set b = coalesce((select x.newVal from zzz_xref x where x.oldVal = zzz.b),b); NOTICE: QUERY PLAN: Seq Scan on zzz (cost=0.00..20.00 rows=1000 width=10) SubPlan -> Index Scan using zzz_xref_ix2 on zzz_xref x (cost=0.00..726.82 rows=983 width=4) -> Seq Scan on zzz_xref x (cost=0.00..1757.80 rows=98304 width=4) Does this make sense? P.S. There are indexes on both oldVal and newVal in zzz_xref, and table zzz has one column (b). ---- Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.C.N. 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 |/
Re: [SQL] Functions too slow, even with iscachable?
At 17:11 7/08/00 +0800, Ang Chin Han wrote: > >BTW, pg_dump doesn't seem to preserve the iscachable attribute. Bug? > Don't know about the rest of your message, but this seems to be a bug. I'll look into it some more... -------- Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.C.N. 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 |/
Re: [SQL] Functions too slow, even with iscachable?
At 17:11 7/08/00 +0800, Ang Chin Han wrote:
>
>BTW, pg_dump doesn't seem to preserve the iscachable attribute. Bug?
>
Here is a patch for 7.0.2 sources which adds support for ischachable to
pg_dump.
-
diff -Naur pg_dump/pg_dump.c zzz/pg_dump.c
--- pg_dump/pg_dump.c Fri Apr 14 11:34:24 2000
+++ zzz/pg_dump.c Mon Aug 7 21:51:21 2000
@@ -1456,13 +1456,15 @@
int i_proretset;
int i_prosrc;
int i_probin;
+ int i_iscachable;
int i_usename;
/* find all user-defined funcs */
appendPQExpBuffer(query,
"SELECT pg_proc.oid, proname, prolang, pronargs, prorettype, "
- "proretset, proargtypes, prosrc, probin,
usename "
+ "proretset, proargtypes, prosrc, probin,
+usename, "
+ "proiscachable "
"from pg_proc, pg_user "
"where pg_proc.oid > '%u'::oid and proowner =
usesysid",
g_last_builtin_oid);
@@ -1492,6 +1494,7 @@
i_proretset = PQfnumber(res, "proretset");
i_prosrc = PQfnumber(res, "prosrc");
i_probin = PQfnumber(res, "probin");
+ i_iscachable = PQfnumber(res, "proiscachable");
i_usename = PQfnumber(res, "usename");
for (i = 0; i < ntups; i++)
@@ -1507,6 +1510,7 @@
finfo[i].nargs = atoi(PQgetvalue(res, i, i_pronargs));
finfo[i].lang = atoi(PQgetvalue(res, i, i_prolang));
finfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
+ finfo[i].iscachable = (strcmp(PQgetvalue(res, i, i_iscachable),"t") ==
+0);
if (finfo[i].nargs < 0 || finfo[i].nargs > FUNC_MAX_ARGS)
{
fprintf(stderr, "failed sanity check: %s has %d args\n",
@@ -2663,11 +2667,18 @@
(j > 0) ? "," : "",
fmtId(typname, false));
}
- appendPQExpBuffer(q, " ) RETURNS %s%s AS '%s' LANGUAGE '%s';\n",
+ appendPQExpBuffer(q, " ) RETURNS %s%s AS '%s' LANGUAGE '%s'",
(finfo[i].retset) ? " SETOF " : "",
fmtId(findTypeByOid(tinfo, numTypes, finfo[i].prorettype), false),
func_def, func_lang);
+ if (finfo[i].iscachable) /* OR in new attrs here */
+ {
+ appendPQExpBuffer(q, " WITH (iscachable)");
+ }
+
+ appendPQExpBuffer(q, ";\n");
+
fputs(q->data, fout);
/*** Dump Function Comments ***/
diff -Naur pg_dump/pg_dump.h zzz/pg_dump.h
--- pg_dump/pg_dump.h Thu Apr 13 03:16:15 2000
+++ zzz/pg_dump.h Mon Aug 7 21:49:05 2000
@@ -61,6 +61,7 @@
char *prosrc;
char *probin;
char *usename;
+ int iscachable; /* Attr */
int dumped; /* 1 if already dumped */
} FuncInfo;
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.C.N. 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 |/
[SQL] Subselect and limit/order?
Just wondering why subselect expressions can not have a limit/order clause, eg. select id,(select id from tbl where id > ? order by id limit 1) as nextid from tbl where id = ?; is quite usefull. It can obviously be done in two select statements, but I was just wondering if it's an oversight or a planner problem? ---- Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.C.N. 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 |/
Re: [SQL] Functions too slow, even with iscachable?
At 10:58 7/08/00 -0400, Tom Lane wrote: > >Looks like we need to teach the optimizer that expressions involving >params can be treated like simple params for the purposes of >optimization. > So long as the function is cacheable... -------- Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.C.N. 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 |/
Re: [SQL] Subselect and limit/order?
At 11:46 7/08/00 -0400, Tom Lane wrote: >Philip Warner <[EMAIL PROTECTED]> writes: >> Just wondering why subselect expressions can not have a limit/order clause, > >We could ignore the spec and implement this as an extension, but I'd >want to see some fairly compelling arguments why it's a good idea. >(In other words, why do you know better than the designers of SQL?) Well, it's not *just* me. Both Dec/RDB and SQL/Server implement ORDER BY in subqueries. SQL/Server insists on a limit statement in this case, whereas Dec/Rdb just dies if more than one row is returned. It is a very useful thing when you are scanning though sequential records that are time based, or have ID's that have 'holes' in the sequence. eg. in the case of time-based data, you can derive durations. But it can be done by writing a plpgsql function, so it's not a big issue. >> It can obviously be done in two select statements, but I was just wondering >> if it's an oversight or a planner problem? > >I'd say mostly an executor problem, actually. Nobody's figured out >where the executor would need to be hacked to support tuple-limits >applied elsewhere than the top level of a select. Wouldn't is also have a fair impact on the planner? Or does it always assume that subselects only return one row? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.C.N. 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 |/
Re: [SQL] Subselect and limit/order?
At 11:46 7/08/00 -0400, Tom Lane wrote: > >I'd say mostly an executor problem, actually. Nobody's figured out >where the executor would need to be hacked to support tuple-limits >applied elsewhere than the top level of a select. Shouldn't the optimizer be able to get some benefit (in colun select expressions at least) by being able to do an implied 'limit 2'? -------- Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.C.N. 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 |/
[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 |/
Re: [SQL] Operator Precedence problem?
At 08:53 12/08/00 -0500, John McKown wrote: >Every language that I've ever used (other than APL) has the precedence of >"or" being less than "and". So I would always expect the "and" clauses to >be evaluated first, then the "or". Just like in math, where in an >equation, I expect that the multiplication (and) is done before the >addition (or). Unless modified by parentheses just as in your second >example. You're quite right; I think I must have been punch drunk from beating my head against another problem. Thanks. -------- 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 |/
