[SQL] how many rows? [was Re: fetching rows]
"Robert B. Easter" wrote: > > > you can't do that with a cursor, but you can use they mysql-ism called a > > limit clause. for example, to fetch rows 26-50 from that query, you'd > > do: > > > > select * from films limit 25,26; > > > > or > > > > select * from files limit 25 offset 26; I did know it was possible but did not know how to do, thanks (It was not my question but I'm glad to see the solution:) and how can I know how many rows are returned by the query? -- Arnaud ( http://www.ressource-toi.org )
[SQL] Like seems to fail !
Hello, I have a strange problem with 'like' behaviour when I upgrade from 6.5 to 7.0 Here is my table : create table persistent_config ( key varchar primary key, typevarchar(12), value varchar not null ) and its content : base=# select * from persistent_config; key | type |value +--+-- /test/info | | avalue /try/info | | avalue /test/key | | changedvalue (3 rows) And I try this 'like' command in psql : base=# select * from persistent_config where key like '/test%'; key | type |value +--+-- /test/info | | avalue /test/key | | changedvalue (2 rows) Result is ok ! By now, this other one : base=# select * from persistent_config where key like '/%'; key | type | value -+--+--- (0 rows) There is really something wrong ! All current content should have been returned. Is '/' caracter a new 'escape' caracter since version 6.5 In fact, my query was working well on postgresql 6.5 and since I upgraded to 7.0, this simple command failed. Is it a bug, or I missed something ? Regards -- Yves Martin yma, Lausanne
Re: [SQL] Re: Requests for Development
KuroiNeko wrote: > > I wonder if there couldn't borrowed some code from Interbase which has > > full featured stored procedures - at least it was told to me that it has > > ... > > Well, I have some hands-on experience with IB, don't know whether this is > perfectly relevant, but here goes > Indeed, stored procedures in IB can do what's called `returning record > sets' in this thread. This is helpfull when tuples restriction is based > upon condition that is not easy/possible to formulate in SQL (where > clause). On the other hand, IB has two different ways to call an SP: > execute procedure for `singleton' SPs and select for those returning > multiple tuples. > However, IB supports only its own SP language. It's pretty much complete > and well thought and implemented, but if you want an SP in PERL, you're out > of luck. > What I'd really like to see is `pre-compiled' SPs in PGSQL. IB has this > feature (SPs are converted to BLR when DDL statement is executed), not sure > about PGSQL. I've noticed that language-specific errors in SPs are only > reported by PGSQL when SP is executed, so I suggest that interpreter (eg > for PL/PGSQL) is called each time. Not entirely true. PL/Tcl has "spi_exec" as well as "spi_prepare/spi_execp". A function is only sourced into the interpreter once per session (backend lifetime) and has a global upvar called GB where it could store prepared plans at it's first call. Since version 8.0 Tcl uses a bytecode compiler and will not interpret the real source text again and again. PL/pgSQL parses the entire function body at first call (per backend). But the SPI querystrings for all the statements aren't parsed at that time. It uses SPI_prepare() only for expressions and queries that actually get executed, so that a huge function that is called only once in a backend, erroring out at the first IF, will not parse most of it's queries. This is surely a win for performance, but it makes it difficult to develop. This will change a little in the future, but I do delay those changes because I think the changes when tuple sets get supported will be huge anyway and complicating the code now wouldn't help. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Re: [SQL] Requests for Development
Josh Berkus wrote: > Tom, Bruce, Jan, etc.: > > [...] > > The rest of these requests apply to 7.2: > > 2. Stored Procedure functionality, i.e. outputting a full recordset from > a function (or new structure, if functions are hard to adapt) based on > the last SELECT statement passed to the function. An alternative would > be to develop parameterized views, which might be the easiest path. That's one of my favorite requests, and I'd be glad to have a chance to start on it. Unfortunately the basic support in the parser and other parts of the core engine isn't completely planned yet, otherwise PL/pgSQL and PL/Tcl would've had this from the very beginning. > 3. Slightly more informative syntax error messages - frankly, just > grabbing a little more text around the word or punctuation that > triggered the error would be enormously helpful (I can't tell you the > number of times I've gotten "Error at or near ')'" in a huge DDL > statement. That's a general problem of a lex/yacc parser and I'm not sure how to force it to be a little more explanative. Maybe we have a chance to grab something from the lex input buffer, but IIRC that's unsafe because nobody knows how much of that is already eaten into yacc tokens. > 4. Use of named in addition to ordinal variables in PL/PGSQL functions > (e.g. $account_type, $period instead of $1, $2). Another general problem in the core engine. Dunno if we'll have named arguments in the near future. In the meantime, PL/pgSQL functions can use ALIAS to define the names for arguments at the very top (it's a precompile time only thing, so there is little to no performance impact). And PL/Tcl functions could easily do a "set account_type $1" as well, so I don't see a real problem for the readability of the functions body. To put the ball back into your yard, I'd like to make a request too. There seem to be alot people using PL/pgSQL and/or PL/Tcl extensively. OTOH there are newbies again and again asking for a good tutorial, programming examples and so on. Writing a good tutorial doesn't require a good backend developer, IMHO an experienced SQL-programmer would be the better guy anyway. During the past 4 years I've heard over and over that people would like to contribute their $0.05 if they only could code in C. That's an area where nobody needs any C experience. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Re: [SQL] Requests for Development
Jan, > To put the ball back into your yard, I'd like to > make a > request too. There seem to be alot people using > PL/pgSQL > and/or PL/Tcl extensively. OTOH there are newbies > again and > again asking for a good tutorial, programming > examples and so > on. Writing a good tutorial doesn't require a good > backend > developer, IMHO an experienced SQL-programmer > would be the > better guy anyway. During the past 4 years I've > heard over > and over that people would like to contribute their > $0.05 if > they only could code in C. That's an area where > nobody needs > any C experience. Point taken. Hmmm... when we finish the current project, I ought to have more than a few dozen PL/PGSQL functions as examples. I can definitely talk to my help writer about dressing those up into an educational "chapter". It'll cost me a little more than $0.05, but is only my fair contribution. Look for something in february-march. -Josh Berkus
Re: [SQL] Re: Requests for Development
> PL/pgSQL parses the entire function body at first call (per > backend). But the SPI querystrings for all the statements > aren't parsed at that time. It uses SPI_prepare() only for > expressions and queries that actually get executed, so that a > huge function that is called only once in a backend, erroring > out at the first IF, will not parse most of it's queries. > This is surely a win for performance, but it makes it > difficult to develop. Thanks for the explanation. Although, I can't see how this improves performance, I'll keep this in my mind when designing PL/PGSQL SPs. -- Sniper's rifle is an extension of his eye. He kills with his injurious vision. JM
Re: [SQL] Requests for Development
* Jan Wieck <[EMAIL PROTECTED]> [001117 08:26]: > > triggered the error would be enormously helpful (I can't tell you the > > number of times I've gotten "Error at or near ')'" in a huge DDL > > statement. > > That's a general problem of a lex/yacc parser and I'm not > sure how to force it to be a little more explanative. Maybe > we have a chance to grab something from the lex input buffer, > but IIRC that's unsafe because nobody knows how much of that > is already eaten into yacc tokens. I was reading the O'Reilly Lex & YACC book over the weekend, and they have some tricks that should make this easier. If someone wants to look into it LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Re: [SQL] Requests for Development
On Wed, 15 Nov 2000, Jan Wieck wrote: > > To put the ball back into your yard, I'd like to make a > request too. There seem to be alot people using PL/pgSQL > and/or PL/Tcl extensively. OTOH there are newbies again and > again asking for a good tutorial, programming examples and so > on. Writing a good tutorial doesn't require a good backend > developer, IMHO an experienced SQL-programmer would be the > better guy anyway. During the past 4 years I've heard over > and over that people would like to contribute their $0.05 if > they only could code in C. That's an area where nobody needs > any C experience. I have this on the way. I started creating such document a couple months ago when I was porting stuff from Oracle to PostgreSQL and stumbled on the few examples on the documentation. I'd be glad to finish it up, add more things to it and then put it somewhere for review, comments, suggestions, additions, etc. Part of this document will be on how to port Oracle PL/SQL to Postgres' PL/SQL and PL/Tcl. - Roberto Mello Utah State University - Computer Science USU Free Software and GNU/Linux Club - http://linux.usu.edu Linux para quem fala Portugues- http://linux.brasileiro.net Linux Registered User #96240
Re: [SQL] Requests for Development
On Fri, Nov 17, 2000 at 10:06:17AM -0600, Roberto Mello wrote: > > I have this on the way. I started creating such document a > couple months ago when I was porting stuff from Oracle to PostgreSQL and > stumbled on the few examples on the documentation. I'd be glad to finish > it up, add more things to it and then put it somewhere for review, > comments, suggestions, additions, etc. Don't worry too much about final polish: "release early, release often!" > Part of this document will be on how to port Oracle PL/SQL to > Postgres' PL/SQL and PL/Tcl. Excellent. Now we need someone to do the MySQL version... Ross -- Open source code is like a natural resource, it's the result of providing food and sunshine to programmers, and then staying out of their way. [...] [It] is not going away because it has utility for both the developers and users independent of economic motivations. Jim Flynn, Sunnyvale, Calif.
[SQL] Why is this doing a seq scan?
I'm trying to find the nearest locations to a certain point using 2 tables. One contains the address and zipcodes of the locations and is about 2000 rows, the other contains zipcodes and lat, lon values and has about 1.4M rows. I've got indexes on the zip column of both tables and I just need to pull the lat and lon out of the zips table for each zipcode that happens to be in the ATMs table. There are only about 2000 rows in ATMs, and since both the atms.zip and zips.zip are indexed, I'm not sure why a seq scan is being performed. The select is taking anywhere from 30secs to 1min. it's running on a linux box w/2 pIII/700s and a raid ..so the machine shouldn't be slowing me down. I think it's the seq scan but I can't seem to get rid of it. => explain select ( point(32.85, -94.55) <@> point(y.lat, y.lon) ) as distance, x.zip, y.zip, y.lat, y.lon from atms x, zips y where x.zip = y.zip order by 1 limit 3; NOTICE: QUERY PLAN: Sort (cost=39164156.66..39164156.66 rows=32338349 width=40) -> Nested Loop (cost=0.00..30401394.25 rows=32338349 width=40) -> Seq Scan on zips y (cost=0.00..29558.49 rows=1401749 width=28) -> Index Scan using atms_zip on atms x (cost=0.00..21.38 rows=23 width=12) Any idea on how to speed this up? Thanks, Bryan
Re: [SQL] Requests for Development
Roberto - > > I have this on the way. I started creating such document a > > couple months ago when I was porting stuff from Oracle to PostgreSQL and > > stumbled on the few examples on the documentation. I'd be glad to finish > > it up, add more things to it and then put it somewhere for review, > > comments, suggestions, additions, etc. > > Don't worry too much about final polish: "release early, release often!" To further that ... let me put my ex-professional copy-editor skills at your disposal. Post the text, I'll help clean it up! -Josh -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Why is this doing a seq scan?
Hmm. Have you VACUUM ANALYZED the tables? If so, what do you get from these queries: select attname,attdisbursion,s.* from pg_statistic s, pg_attribute a, pg_class c where starelid = c.oid and attrelid = c.oid and staattnum = attnum and relname = 'zips'; select attname,attdisbursion,s.* from pg_statistic s, pg_attribute a, pg_class c where starelid = c.oid and attrelid = c.oid and staattnum = attnum and relname = 'atms'; Also it would be useful to see the full declarations of the tables and their indexes; I'm wondering what datatype the zip columns are, for example. regards, tom lane
RE: [SQL] Why is this doing a seq scan?
> Hmm. Have you VACUUM ANALYZED the tables? If so, what do > you get from > these queries: Tom, thanks for the reply, and here is all the info you asked for. > select attname,attdisbursion,s.* > from pg_statistic s, pg_attribute a, pg_class c > where starelid = c.oid and attrelid = c.oid and staattnum = attnum > and relname = 'zips'; attname |attdisbursion|starelid|staattnum|staop|stanullfrac|stacommonfrac|stacommonv al|staloval |stahival -+-++-+-+---+-+- ---+--+ zip | -1| 93920|1| 1066| 0| 7.13394e-07|01226 |00401 |Y1A6A1 state| 0.165522| 93920|2| 1066| 0| 0.346728|ON |AB|YT city | 0.00729095| 93920|3| 1066| 0| 0.0322854|TORONTO | |ZWOLLE lat | 0.00326189| 93920|4| 672| 0| 0.0153651|51.05 |-123.176 |79.989 lon | 0.00326061| 93920|5| 672| 0| 0.0153594|-114.08 |-176.31005|144.445 bestbound| 0.997491| 93920|6| 672| 0.998605| 0.00107366|2 |2 |98 (6 rows) > select attname,attdisbursion,s.* > from pg_statistic s, pg_attribute a, pg_class c > where starelid = c.oid and attrelid = c.oid and staattnum = attnum > and relname = 'atms'; attname |attdisbursion|starelid|staattnum|staop|stanullfrac|stacommonfrac|stacommonv al |staloval |stahival +-++-+-+---+ -+---+--+ terminal| -1| 50904|1| 97| 0| 0.000433463|6000 |55|9433 district|0.0679035| 50904|2| 664| 0| 0.192024|ARCO California|ARCO Arizona |Western New York name| 0.000261431| 50904|3| 664| 0| 0.00130039|Gateway Center |11th & Conger |Zionsville address | 0.000261431| 50904|4| 664| 0| 0.00130039|215 Tecumseh Rd|"402 E Yakima Ave, Suite 1400"|Windham Mall city| 0.00522279| 50904|5| 664| 0| 0.0238405|Seattle|Aberdeen |Zionsville state |0.0687854| 50904|6| 1058| 0| 0.193758|CA |AK|WA zip | 0.000614214| 50904|7| 664| 0| 0.00303424|92392 | |99901 access | 0.385091| 50904|8| 664| 0| 0.579974|WU | |WU function| 0.396416| 50904|9| 664| 0| 0.589944|FF |CD|FF location| 0.414461| 50904| 10| 664| 0| 0.605548|BR |BR|Rem language| 0.431861| 50904| 11| 664| 0| 0.620286|E | |E restricted_hours| 0.886758| 50904| 12| 664| 0| 0.939749|FALSE |FALSE |TRUE seasonal| 0.994812| 50904| 13| 664| 0| 0.997399|FALSE |FALSE |TRUE stamps | 0.621877| 50904| 14| 664| 0| 0.746857|FALSE |FALSE |TRUE (14 rows) > Also it would be useful to see the full declarations of the tables > and their indexes; I'm wondering what datatype the zip columns are, > for example. Table= atms +--+--+- --+ | Field | Type| Length| +--+--+- --+ | terminal | int4 | 4 | | district | text | var | | name | text | var | | address | text | var | | city | text | var | | state| char() | 2 | | zip | text | var | | access | text | var | | function | text | var | | location | text | var | | language | text | var | | restricted_hours | text
Re: [SQL] Why is this doing a seq scan?
"Ingram, Bryan" <[EMAIL PROTECTED]> writes: >> Also it would be useful to see the full declarations of the tables >> and their indexes; I'm wondering what datatype the zip columns are, >> for example. > Table= atms > | zip | text | > Table= zips > | zip | varchar()| Ah, there's your problem --- the planner is not very smart about optimizing cross-datatype comparisons. Make these columns both text, or both varchar, and I'll bet you get a more intelligent plan. Current sources (7.1-to-be) are a little smarter than 7.0 about cross-data-type joins, but they still don't get this case right. I have a TODO item about that, but I dunno if it'll get done before 7.1 ... regards, tom lane
Re: [SQL] Why is this doing a seq scan?
I said: > Ah, there's your problem --- the planner is not very smart about > optimizing cross-datatype comparisons. Make these columns both text, > or both varchar, and I'll bet you get a more intelligent plan. After a little further thought, I realize that the planner may be handicapped by not realizing it can do a merge or hash join across datatypes, but even without that problem, this is not going to be a fast query. What you've got is select ... from atms x, zips y where x.zip = y.zip order by 1 limit 3; and there just isn't any way to process this without forming the full join product --- ie, the thing will sit there and form a join tuple for *every* valid combination of ATM and ZIP in your database, and then compute the distance to the target point for every one of those ATMs, and then sort that result, and finally give you only the top three rows. A smarter kind of join isn't going to help all that much; to make this fast, you need to be filtering using the really selective condition (distance to the target point) *before* you do the join. If you are sufficiently interested in the speed of this query to want to maintain a specialized index for it, I'd suggest looking at an r-tree index on the location data, and then using a WHERE condition on the r-tree index to prefilter the rows before you join. r-trees only work on boxes and polygons AFAICT --- what would work nicely is to store a "box" of very small dimensions surrounding the location of each ATM, index that column, and then use a WHERE test for overlap between that box column and a box surrounding the target point out to as far as you think is likely to be interesting. This gives you back a fairly small number of candidate ATMs for which you compute the exact distance to the target, sort, and limit. Not sure that you need to join to zips at all if you do it this way. regards, tom lane
Re: [SQL] how many rows? [was Re: fetching rows]
On Friday 17 November 2000 04:01, Arnaud Vandyck wrote: > "Robert B. Easter" wrote: > > > you can't do that with a cursor, but you can use they mysql-ism called > > > a limit clause. for example, to fetch rows 26-50 from that query, > > > you'd do: > > > > > > select * from films limit 25,26; > > > > > > or > > > > > > select * from files limit 25 offset 26; > > I did know it was possible but did not know how to do, thanks (It was > not my question but I'm glad to see the solution:) > > and how can I know how many rows are returned by the query? > I don't know exactly. I don't know of any way to find the total number of rows in a cursor. If you really need to know, you'll have to run a count(*) first, then make the cursor using the same select almost. Once you get the count(*), you can then use MOVE and FETCH to get the page you want. If someone has done it a better way, I'd like to hear how. However, sometimes it is possible to cache a count(*) value somewhere in the database so it doesn't have to be found everytime - it depends on your database and what the select is if you can store the count in advance somehow. > -- > Arnaud > ( http://www.ressource-toi.org ) -- Robert B. Easter [EMAIL PROTECTED] - - CompTechNews Message Board http://www.comptechnews.com/ - - CompTechServ Tech Services http://www.comptechserv.com/ - -- http://www.comptechnews.com/~reaster/
Re: [SQL] how many rows? [was Re: fetching rows]]
"Robert B. Easter" wrote: > If someone has done it a better way, I'd like to hear how. However, > sometimes it is possible to cache a count(*) value somewhere in the database > so it doesn't have to be found everytime - it depends on your database and > what the select is if you can store the count in advance somehow. I don't know how it was implemented (source code is available), but the PostgreSQL driver for AOLserver (a kick-butt web server for database-backed websites) has a function that does that. All I do after a SQL statement is: set rowcount [ns_pg ntuples] in my Tcl code and there it is. The driver is available at either http://www.aolserver.com or http://www.openacs.org (the latter has a more elaborate version of the driver). -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
