Re: [SQL] Equivalent to sql%rowcount or @@rowcount
On Friday 25 July 2003 07:04, vijaykumar M wrote: > Hi All! > is it possible to get in sql number of rows affected by the sql last insert, > > update or delete statement?? > > for eg, > > oracle - sql%rowcount. > sqlserver select @@rowcount. You can do this sort of thing inside a plpgsql function - see ch 19.5.5 "Obtaining result status" in the programmers manual. GET DIAGNOSTICS my_var = ROW_COUNT; Oh - and _please_ don't post html-only messages to the list. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] [OT] Frontend recommendations
Hey postgresql gurus, I was just wondering, what kind of frontend do you recommend in 'bills (windows...)' environment? Is it better to use a tool like Qt from trolltech or is it better to use something like Omnis or Access??? What are you opinions best regards, Ries van Twisk ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problem using Subselect results
Quoting Dmitry Tkach <[EMAIL PROTECTED]>: > What about: > > CREATE VIEW my_view AS SELECT b,c from > (SELECT a, b FROM table1 WHERE b=1) as my_ab, > (SELECT a, c FROM table2) as my_ac WHERE my_ac.a=my_ab.a > > This looks like what you are trying to do, and doesn't use that > 'subsubselect' you were trying to avoid... I assume that with this statement postgresql will compute both subselects, do a cross join on both results an then reduce them to those who match the condition my_ac.a=my_ab.a, right? What I was trying to do is reduce the results to a minimum before joining them. It's not only two or three tables and some of them will grow big, so joining them first and reducing them later may not be such a good idea. My first try (which does not work, because I'm trying to access results of subselects on the same hierarchy level): CREATE VIEW public.aufwaende_test AS SELECT aufwaende.aufwandsid, aufwaende.auftragsid, aufwaende.taetigkeitsid, my_taetigkeiten.taetigkeit, aufwaende.mitarbeiterid, my_mitarbeiter.vorname, my_mitarbeiter.nachname, my_mitarbeiter.pknummer, aufwaende.dauer, aufwaende.datum, my_auftraege.aufgabenid, my_aufgaben.aufgabe, my_auftraege.systemid, my_systeme.system, my_systeme.kundenid, my_kunden.kunde, aufwaende.status FROM aufwaende, (SELECT auftraege_complete.systemid, auftraege_complete.aufgabenid, auftraege_complete.updatenr FROM auftraege_complete WHERE (auftraege_complete.updatenr = (SELECT max(auftraege_complete.updatenr) AS max FROM auftraege_complete WHERE ((auftraege_complete.auftragsid = aufwaende.auftragsid) my_auftraege, (SELECT aufgaben_complete.bezeichnung AS aufgabe FROM aufgaben_complete WHERE (aufgaben_complete.updatenr = (SELECT max(aufgaben_complete.updatenr) AS max FROM aufgaben_complete WHERE ((aufgaben_complete.aufgabenid = auftraege_complete.aufgabenid) AND (aufgaben_complete.updatenr < my_auftraege.updatenr) my_aufgaben, (SELECT taetigkeiten_complete.bezeichnung AS taetigkeit FROM taetigkeiten_complete WHERE (taetigkeiten_complete.updatenr = (SELECT max (taetigkeiten_complete.updatenr) AS max FROM taetigkeiten_complete WHERE ((taetigkeiten_complete.taetigkeitsid = aufwaende.taetigkeitsid) AND (taetigkeiten_complete.updatenr < my_auftraege.updatenr) my_taetigkeiten, (SELECT systeme_complete.name AS system, systeme_complete.kundenid, systeme_complete.systemid AS sys_sysid FROM systeme_complete WHERE (systeme_complete.updatenr = (SELECT max(systeme_complete.updatenr) AS max FROM systeme_complete WHERE ((systeme_complete.systemid = auftraege_complete.systemid) AND (systeme_complete.updatenr < my_auftraege.updatenr) my_systeme, (SELECT kunden_complete.name AS kunde FROM kunden_complete WHERE (kunden_complete.updatenr = (SELECT max(kunden_complete.updatenr) AS max FROM kunden_complete WHERE ((kunden_complete.kundenid = my_systeme.kundenid) AND (kunden_complete.updatenr < aufwaende.updatenr) my_kunden, (SELECT mitarbeiter_complete.nachname, mitarbeiter_complete.vorname, mitarbeiter_complete.pknummer FROM mitarbeiter_complete WHERE (mitarbeiter_complete.updatenr = (SELECT max(mitarbeiter_complete.updatenr) AS max FROM mitarbeiter_complete WHERE ((mitarbeiter_complete.mitarbeiterid = aufwaende.mitarbeiterid) AND (mitarbeiter_complete.updatenr < my_auftraege.updatenr) my_mitarbeiter; as you can see most of them use my_auftraege.updatenr as one condition, and the subselect on kunden_complete uses results from the my_systeme subselect (my_systeme.kundenid) Now I see two possibilities - join the early and reduce them later - create a hierarchy so that (sub...)selects which rely on the result of another select include this select-statement as a (sub...)subselect. Any better Ideas? TIA, Oliver > > BTW, what is special to the second-level subselect, compared to the first > level one? Why are you trying to avoid one, but not the other? > > I mean, I could understand, if you (like me) just hated subselects > alltogether (then you would have converted your query into a join), but it > looks like you don't... > > Dima > > > > > >But (now) I believe it's not possible to refer to a subselect's resultset on > > >the same level of hierarchy - which sounds rather meaningful - because you > > >couldn't tell which of them was being processsed first. > > > >So I'll have to get my SELECT statement into some kind of hierarchy, which > > >makes things a bit more complicated (with twentysomething SELECT > statements) > > > >Thanks, > >Oliver > > > > > >Quoting Christoph Haller <[EMAIL PROTECTED]>: > > > > > >>Does this match your intentions: > >> CREATE VIEW my_view AS SELECT b,c FROM > >> (SELECT b,c FROM table2, (SELECT a, b FROM table1 WHERE b=3D1) my_ab > >>WHERE table3.a=3Dmy_ab.a) my_c; > >>I assume the reference table3.a is a typo. > >> > >>Regards, Christoph > >> > >> > >> > > > > > > > >>>I want to use the result of a subselect as condition in another one. > >>> > >>>table1:
Re: [SQL] [OT] Frontend recommendations
On Friday 25 July 2003 09:18, [EMAIL PROTECTED] wrote: > Hey postgresql gurus, > > I was just wondering, what kind of frontend do you recommend in 'bills > (windows...)' environment? > Is it better to use a tool like Qt from trolltech or is it better to use > something like Omnis or Access??? I've always felt there's nowt wrong with Access as a way of building forms/reports etc. -- Richard Huxton Archonet Ltd ---(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] [OT] Frontend recommendations
On Fri, 25 Jul 2003 [EMAIL PROTECTED] wrote: > Hey postgresql gurus, > > I was just wondering, what kind of frontend do you recommend in 'bills > (windows...)' environment? > Is it better to use a tool like Qt from trolltech or is it better to use > something like Omnis or Access??? > > What are you opinions > It all depends what you need to do. From using Qt for graphical frontend. I have found the Sql bits to be extremly slow. So the plan is currently to stop using them and use the pqxx drivers. Change is not easy however! (Thats under Linux...) If you need a quick gui try pgaccess and the like. If you want to writea full gui pick you favoute language and there should be some postgres drivers (if all else fails there is always ODBC) somthing like perl or python may be nice and platform independant Peter Childs > > best regards, > Ries van Twisk > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Problem using Subselect results
[EMAIL PROTECTED] writes: > Quoting Dmitry Tkach <[EMAIL PROTECTED]>: >> CREATE VIEW my_view AS SELECT b,c from >> (SELECT a, b FROM table1 WHERE b=1) as my_ab, >> (SELECT a, c FROM table2) as my_ac WHERE my_ac.a=my_ab.a > I assume that with this statement postgresql will compute both subselects, do > a cross join on both results an then reduce them to those who match the > condition my_ac.a=my_ab.a, right? No, it's smarter than that. I tried the experiment in 7.3 and CVS tip, using some tables from the regression database: regression=# create view my_view as select b,c from regression-# (select unique1,unique2 from tenk1 where unique2=1) as regression-# my_ab(a,b), regression-# (select unique1,unique2 from onek) as my_ac(a,c) regression-# where my_ac.a = my_ab.a; CREATE VIEW regression=# explain select * from my_view; QUERY PLAN - Nested Loop (cost=0.00..24.47 rows=1 width=16) -> Index Scan using tenk1_unique2 on tenk1 (cost=0.00..3.01 rows=1 width=8) Index Cond: (unique2 = 1) -> Index Scan using onek_unique1 on onek (cost=0.00..21.40 rows=5 width=8) Index Cond: (onek.unique1 = "outer".unique1) (5 rows) regression=# Looks like a fairly decent plan to me. It's certainly not letting the sub-select structure get in its way. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problem using Subselect results
[EMAIL PROTECTED] wrote: Quoting Dmitry Tkach <[EMAIL PROTECTED]>: What about: CREATE VIEW my_view AS SELECT b,c from (SELECT a, b FROM table1 WHERE b=1) as my_ab, (SELECT a, c FROM table2) as my_ac WHERE my_ac.a=my_ab.a This looks like what you are trying to do, and doesn't use that 'subsubselect' you were trying to avoid... I assume that with this statement postgresql will compute both subselects, do a cross join on both results an then reduce them to those who match the condition my_ac.a=my_ab.a, right? I don't think so... Not totally sure, but I believe, that, at least in this case, the query plan will be equivalent to a join... What I was trying to do is reduce the results to a minimum before joining them. It's not only two or three tables and some of them will grow big, so joining them first and reducing them later may not be such a good idea. I am not sure I understand what you mean by 'reducing'. It seems to me that you could make your query a lot simpler by converting it into a join, and I don't see anything you are buying by those subselects Also, you may want to get rid of max(), and replace those things with 'select column from table order by column desc limit 1'. This should be a lot quicker (provided that you have an index on that column). Dima My first try (which does not work, because I'm trying to access results of subselects on the same hierarchy level): CREATE VIEW public.aufwaende_test AS SELECT aufwaende.aufwandsid, aufwaende.auftragsid, aufwaende.taetigkeitsid, my_taetigkeiten.taetigkeit, aufwaende.mitarbeiterid, my_mitarbeiter.vorname, my_mitarbeiter.nachname, my_mitarbeiter.pknummer, aufwaende.dauer, aufwaende.datum, my_auftraege.aufgabenid, my_aufgaben.aufgabe, my_auftraege.systemid, my_systeme.system, my_systeme.kundenid, my_kunden.kunde, aufwaende.status FROM aufwaende, (SELECT auftraege_complete.systemid, auftraege_complete.aufgabenid, auftraege_complete.updatenr FROM auftraege_complete WHERE (auftraege_complete.updatenr = (SELECT max(auftraege_complete.updatenr) AS max FROM auftraege_complete WHERE ((auftraege_complete.auftragsid = aufwaende.auftragsid) my_auftraege, (SELECT aufgaben_complete.bezeichnung AS aufgabe FROM aufgaben_complete WHERE (aufgaben_complete.updatenr = (SELECT max(aufgaben_complete.updatenr) AS max FROM aufgaben_complete WHERE ((aufgaben_complete.aufgabenid = auftraege_complete.aufgabenid) AND (aufgaben_complete.updatenr < my_auftraege.updatenr) my_aufgaben, (SELECT taetigkeiten_complete.bezeichnung AS taetigkeit FROM taetigkeiten_complete WHERE (taetigkeiten_complete.updatenr = (SELECT max (taetigkeiten_complete.updatenr) AS max FROM taetigkeiten_complete WHERE ((taetigkeiten_complete.taetigkeitsid = aufwaende.taetigkeitsid) AND (taetigkeiten_complete.updatenr < my_auftraege.updatenr) my_taetigkeiten, (SELECT systeme_complete.name AS system, systeme_complete.kundenid, systeme_complete.systemid AS sys_sysid FROM systeme_complete WHERE (systeme_complete.updatenr = (SELECT max(systeme_complete.updatenr) AS max FROM systeme_complete WHERE ((systeme_complete.systemid = auftraege_complete.systemid) AND (systeme_complete.updatenr < my_auftraege.updatenr) my_systeme, (SELECT kunden_complete.name AS kunde FROM kunden_complete WHERE (kunden_complete.updatenr = (SELECT max(kunden_complete.updatenr) AS max FROM kunden_complete WHERE ((kunden_complete.kundenid = my_systeme.kundenid) AND (kunden_complete.updatenr < aufwaende.updatenr) my_kunden, (SELECT mitarbeiter_complete.nachname, mitarbeiter_complete.vorname, mitarbeiter_complete.pknummer FROM mitarbeiter_complete WHERE (mitarbeiter_complete.updatenr = (SELECT max(mitarbeiter_complete.updatenr) AS max FROM mitarbeiter_complete WHERE ((mitarbeiter_complete.mitarbeiterid = aufwaende.mitarbeiterid) AND (mitarbeiter_complete.updatenr < my_auftraege.updatenr) my_mitarbeiter; as you can see most of them use my_auftraege.updatenr as one condition, and the subselect on kunden_complete uses results from the my_systeme subselect (my_systeme.kundenid) Now I see two possibilities - join the early and reduce them later - create a hierarchy so that (sub...)selects which rely on the result of another select include this select-statement as a (sub...)subselect. Any better Ideas? TIA, Oliver BTW, what is special to the second-level subselect, compared to the first level one? Why are you trying to avoid one, but not the other? I mean, I could understand, if you (like me) just hated subselects alltogether (then you would have converted your query into a join), but it looks like you don't... Dima But (now) I believe it's not possible to refer to a subselect's resultset on the same level of hierarchy - which sounds rather meaningful - because you couldn't tell which of them was being processsed first. So I'll have to get my SELECT statement into some kind of hierarchy, which makes things a
[SQL] locks and variable substitution
Is it possible to do variable substitution with explicit locks in plpgsql?
Something along the lines of
...
declare
big_string alias for $1;
excl_table text;
begin
--assuming big_string starts with something like table=table1!
excl_table := substr(big_string,7,(position(''!'' in big_string)-7));
lock table excl_table in exclusive mode;
...
Thanks very much.
g.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] ERROR: DefineIndex: index function must be marked iscachable
Dnia 2003-07-25 21:09, Użytkownik Elielson Fontanezi napisał: Hi all! What can I do in this case? I could not found anything about iscachable. postgres$ cat in.sql create index bt_proposta_f01 on proposta using btree (func_cod_secretaria(nr_proponente)); postgres$ psql -d escola -f in.sql psql:in.sql:2: ERROR: DefineIndex: index function must be marked iscachable postgres$ You should follow the error. Your function func_cod_secretaria has to be declared as cacheable. (Look into documentation - sql commands / create function). IMMUTABLE function should help. This function has also for some nr_proponente returns always the same value (look at IMMUTABLE description) Regards, Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] locks and variable substitution
On Fri, 2003-07-25 at 11:49, [EMAIL PROTECTED] wrote: > > lock table excl_table in exclusive mode; That probably wont work, but this will: EXECUTE ''LOCK TABLE '' || quote_ident(excl_table) || '' IN EXCLUSIVE MODE''; Eric ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Odd problems with create rule
Hi all - I'm having a strange problem creating a rule on a view. I've done this with no problem in other databases... I'm wondering if somehow there was something omitted or some other type of prolem with the database creation. Or, I could have no idea what I'm talking about. Any guesses? dlm=# create or replace rule addenda_insert_rule as dlm-# on insert to addenda do instead dlm(# insert into documents ( dlm(# project_id, dlm(# doc_num, dlm(# description, dlm(# date, dlm(# people_id, dlm(# parent, dlm(# document_type, dlm(# state, dlm(# machines_id, dlm(# phases_id ) dlm(# values ( dlm(# new.projects._id, dlm(# new.doc_num, dlm(# new.description, dlm(# new.date, dlm(# new.people_id, dlm(# new.parent, dlm(# new.document_type, dlm(# new.state, dlm(# new.machines_id, dlm(# new.phases_id ); ERROR: Namespace "*NEW*" does not exist dlm=# Thanks. -j -- Jamie Lawrence[EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Odd problems with create rule
Dnia 2003-07-25 23:38, Użytkownik Jamie Lawrence napisał: Hi all - I'm having a strange problem creating a rule on a view. I've done this with no problem in other databases... I'm wondering if somehow there was something omitted or some other type of prolem with the database creation. Or, I could have no idea what I'm talking about. Any guesses? dlm=# create or replace rule addenda_insert_rule as dlm-# on insert to addenda do instead dlm(# insert into documents ( dlm(# project_id, dlm(# doc_num, dlm(# description, dlm(# date, dlm(# people_id, dlm(# parent, dlm(# document_type, dlm(# state, dlm(# machines_id, dlm(# phases_id ) dlm(# values ( dlm(# new.projects._id, ^ dlm(# new.doc_num, dlm(# new.description, dlm(# new.date, dlm(# new.people_id, dlm(# new.parent, dlm(# new.document_type, dlm(# new.state, dlm(# new.machines_id, dlm(# new.phases_id ); ERROR: Namespace "*NEW*" does not exist dlm=# Too many dots? Shouldn't it be "new.project_id"? Regards, Tomasz Myrta ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Odd problems with create rule
On Fri, 25 Jul 2003, Tomasz Myrta wrote: > Dnia 2003-07-25 23:38, U?ytkownik Jamie Lawrence napisa?: > >dlm(# values ( > >dlm(# new.projects._id, > ^ > >dlm(# new.doc_num, > >dlm(# new.description, > >dlm(# new.date, > >dlm(# new.people_id, > >dlm(# new.parent, > >dlm(# new.document_type, > >dlm(# new.state, > >dlm(# new.machines_id, > >dlm(# new.phases_id ); > >ERROR: Namespace "*NEW*" does not exist > >dlm=# > > Too many dots? > Shouldn't it be "new.project_id"? Oh, god. I'm a moron, or at least should have my vision checked. Thank you. -j > Regards, > Tomasz Myrta -- Jamie Lawrence[EMAIL PROTECTED] Americans will tolerate just about anything as long as you don't stop traffic. - Dan Rather ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Query analyse
Good morning! First of all, my envoronment is: Linux netlab142.prodam 2.4.8-26mdk #1 Sun Sep 23 17:06:39 CEST 2001 i686 unknown pg_ctl (PostgreSQL) 7.2.1 I would like some suggestions on how to speed up a query. Both of the queries below are identical except that one of them use the trunc function. You can see that the TRUNC function rise hardly up the query response time in the second query. That shouldn´t be happen. Only because a trunc function? What can I be in that case? What does it happen? Sure, there are indexes: CREATE INDEX idx_proposta_2 ON proposta USING btree (in_situacao_proposta); CREATE INDEX idx_proposta_4 ON proposta USING btree (nr_proponente); And pa.nr_proponente is fk and op.nr_proponte is pk. These are the queries: 1o. That is ok. DEBUG: query: select pa.nr_projeto, pa.dc_denom_projeto, pa.nr_proponente, pa.dc_coordenador, op.dc_proponente from proposta pa inner join orgao_proponente op on (pa.nr_proponente = op.nr_proponente) where pa.in_situacao_proposta <> 'E' ORDER BY 1 DESC; DEBUG: QUERY STATISTICS! system usage stats:! 0.015904 elapsed 0.00 user 0.02 system sec! [0.01 user 0.02 sys total]! 0/0 [0/0] filesystem blocks in/out! 143/42 [353/172] page faults/reclaims, 0 [0] swaps! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent! 0/0 [0/0] voluntary/involuntary context switches! postgres usage stats:! Shared blocks: 88 read, 0 written, buffer hit rate = 89.19%! Local blocks: 0 read, 0 written, buffer hit rate = 0.00%! Direct blocks: 0 read, 0 written 2o. But I need to use the trunc function: DEBUG: query: select pa.nr_projeto, pa.dc_denom_projeto, pa.nr_proponente, pa.dc_coordenador, op.dc_proponente from proposta pa inner join orgao_proponente op on (trunc(pa.nr_proponente/10,0)*10 = op.nr_proponente) where pa.in_situacao_proposta <> 'E' ORDER BY 1 DESC; DEBUG: QUERY STATISTICS! system usage stats:! 104.665005 elapsed 10.09 user 0.42 system sec! [10.10 user 0.42 sys total]! 0/0 [0/0] filesystem blocks in/out! 141/50 [352/180] page faults/reclaims, 0 [0] swaps! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent! 0/0 [0/0] voluntary/involuntary context switches! postgres usage stats:! Shared blocks: 7408 read, 0 written, buffer hit rate = 13.23%! Local blocks: 0 read, 0 written, buffer hit rate = 0.00%! Direct blocks: 0 read, 0 written
Re: [SQL] [GENERAL] Query analyse
On Fri, 25 Jul 2003, Elielson Fontanezi wrote: > First of all, my envoronment is: > Linux netlab142.prodam 2.4.8-26mdk #1 Sun Sep 23 17:06:39 CEST 2001 > i686 unknown > pg_ctl (PostgreSQL) 7.2.1 > > I would like some suggestions on how to speed up a query. > > Both of the queries below are identical except that one of them use the > trunc function. > > You can see that the TRUNC function rise hardly up the query response > time in the second query. > That shouldn´t be happen. Only because a trunc function? > > What can I be in that case? > What does it happen? What does explain show for the two queries and what are the table schemas? You're probably ending up with different plans since in one case it has a plain column reference and in the other it has a marginally complicated expression in the join condition. As something to try, perhaps make a function that returns trunc($1/10.0)*10 and index on that function for the column and see if that changes the plan you get. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] Query analyse
The first query is able to use the index on nr_proponente, because the condition involves that column directly, the second query is not, because the index only contains the values of nt_proponente, not results of trunc(..)/ Try replacing that condition with something like pa.nr_proponente BETWEEN op.nr_proponente AND op.nr_proponente + 0.1 I hope, it helps... Dima Elielson Fontanezi wrote: Good morning! First of all, my envoronment is: Linux netlab142.prodam 2.4.8-26mdk #1 Sun Sep 23 17:06:39 CEST 2001 i686 unknown pg_ctl (PostgreSQL) 7.2.1 I would like some suggestions on how to speed up a query. Both of the queries below are identical except that one of them use the *trunc* function. You can see that the TRUNC function rise hardly up the query response time in the second query. That shouldn´t be happen. Only because a trunc function? What can I be in that case? What does it happen? Sure, there are indexes: CREATE INDEX idx_proposta_2 ON proposta USING btree (in_situacao_proposta); CREATE INDEX idx_proposta_4 ON proposta USING btree (nr_proponente); And pa.nr_proponente is fk and op.nr_proponte is pk. These are the queries: 1o. That is ok. DEBUG: query: select pa.nr_projeto, pa.dc_denom_projeto, pa.nr_proponente, pa.dc_coordenador, op.dc_proponente from proposta pa inner join orgao_proponente op on (pa.nr_proponente = op.nr_proponente) where pa.in_situacao_proposta <> 'E' ORDER BY 1 DESC; DEBUG: QUERY STATISTICS ! system usage stats: ! 0.015904 elapsed 0.00 user 0.02 system sec ! [0.01 user 0.02 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 143/42 [353/172] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 0/0 [0/0] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 88 read, 0 written, buffer hit rate = 89.19 % ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written 2o. But I need to use the trunc function: DEBUG: query: select pa.nr_projeto, pa.dc_denom_projeto, pa.nr_proponente, pa.dc_coordenador, op.dc_proponente from proposta pa inner join orgao_proponente op on (trunc(pa.nr_proponente/10,0)*10 = op.nr_proponente) where pa.in_situacao_proposta <> 'E' ORDER BY 1 DESC; DEBUG: QUERY STATISTICS ! system usage stats: ! 104.665005 elapsed 10.09 user 0.42 system sec ! [10.10 user 0.42 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 141/50 [352/180] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 0/0 [0/0] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 7408 read, 0 written, buffer hit rate = 13.23 % ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written ---(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] ERROR: DefineIndex: index function must be marked iscachable
Hi all! What can I do in this case? I could not found anything about iscachable. postgres$ cat in.sqlcreate index bt_proposta_f01 on propostausing btree (func_cod_secretaria(nr_proponente)); postgres$ psql -d escola -f in.sqlpsql:in.sql:2: ERROR: DefineIndex: index function must be marked iscachablepostgres$ >\\\!/< 55 11 5080 9283 !_"""_! Elielson Fontanezi (O) (o) PRODAM - Technical Support Analyst---oOOO--(_)--OOOo--- Success usually comes to those who are too busy to be looking for it. 0 0---( )--( ) \ ( ) / \_/ \_/
Re: [SQL] ERROR: DefineIndex: index function must be marked iscachable
On Fri, 25 Jul 2003, Elielson Fontanezi wrote: > What can I do in this case? > I could not found anything about iscachable. > > postgres$ cat in.sql > create index bt_proposta_f01 on proposta > using btree (func_cod_secretaria(nr_proponente)); > > postgres$ psql -d escola -f in.sql > psql:in.sql:2: ERROR: DefineIndex: index function must be marked iscachable Check the create function reference page, I believe you'd need to add WITH (iscachable) to the end of the function creation. iscachable means that the function always returnes the same result when given the same arguments. It's a prereq to using the function in an index. If your function doesn't meet those requirements (for example if it does a query on a table) making an index on that function is a bad idea. ---(end of broadcast)--- TIP 8: explain analyze is your friend
RES: [GENERAL] [SQL] ERROR: DefineIndex: index function must be marked iscachable
Oh sorry! Linux netlab142.prodam 2.4.8-26mdk #1 Sun Sep 23 17:06:39 CEST 2001 i686 unknown pg_ctl (PostgreSQL) 7.2.1 -Mensagem original- De: Stephan Szabo [mailto:[EMAIL PROTECTED] Enviada em: sexta-feira, 25 de julho de 2003 16:25 Para: Elielson Fontanezi Cc: pgsql-general; pgsql-sql Assunto: Re: [GENERAL] [SQL] ERROR: DefineIndex: index function must be marked iscachable On Fri, 25 Jul 2003, Stephan Szabo wrote: > On Fri, 25 Jul 2003, Elielson Fontanezi wrote: > > > What can I do in this case? > > I could not found anything about iscachable. > > > > > > postgres$ cat in.sql > > create index bt_proposta_f01 on proposta > > using btree (func_cod_secretaria(nr_proponente)); > > > > postgres$ psql -d escola -f in.sql > > psql:in.sql:2: ERROR: DefineIndex: index function must be marked iscachable > > Check the create function reference page, I believe you'd need to add WITH > (iscachable) to the end of the function creation. > > iscachable means that the function always returnes the same result when > given the same arguments. It's a prereq to using the function in an > index. If your function doesn't meet those requirements (for example if it > does a query on a table) making an index on that function is a bad idea. Almost forgot. In recent versions you'd specify that the function was IMMUTABLE and I believe the error message would use such as well. What version are you using (it might be worth considering an upgrade to 7.4 when it comes out) ---(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: [GENERAL] [SQL] ERROR: DefineIndex: index function must be
On Fri, 25 Jul 2003, Stephan Szabo wrote: > On Fri, 25 Jul 2003, Elielson Fontanezi wrote: > > > What can I do in this case? > > I could not found anything about iscachable. > > > > > > postgres$ cat in.sql > > create index bt_proposta_f01 on proposta > > using btree (func_cod_secretaria(nr_proponente)); > > > > postgres$ psql -d escola -f in.sql > > psql:in.sql:2: ERROR: DefineIndex: index function must be marked iscachable > > Check the create function reference page, I believe you'd need to add WITH > (iscachable) to the end of the function creation. > > iscachable means that the function always returnes the same result when > given the same arguments. It's a prereq to using the function in an > index. If your function doesn't meet those requirements (for example if it > does a query on a table) making an index on that function is a bad idea. Almost forgot. In recent versions you'd specify that the function was IMMUTABLE and I believe the error message would use such as well. What version are you using (it might be worth considering an upgrade to 7.4 when it comes out) ---(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] [GENERAL] Query analyse
Stephan Szabo <[EMAIL PROTECTED]> writes: > You're probably ending up with different plans since in one case it has > a plain column reference and in the other it has a marginally complicated > expression in the join condition. Yeah. 7.3 and before cannot do merge or hash joins on conditions that are any more complex than "var = var". The query with the trunc() is undoubtedly falling back to the stupidest kind of nestloop. > As something to try, perhaps make a function that returns > trunc($1/10.0)*10 and index on that function for the column and > see if that changes the plan you get. It might help --- you might possibly get a nestloop-with-inner-indexscan out of that. Not sure though, since the planner is likely to be using bad guesstimates about the selectivity of the expression. 7.4 should do better on this. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] ERROR: DefineIndex: index function must be marked iscachable
Hi! Who can help me on that? First of all, my envoronment is: Linux netlab142.prodam 2.4.8-26mdk #1 Sun Sep 23 17:06:39 CEST 2001 i686 unknown pg_ctl (PostgreSQL) 7.2. Problem: ERROR: DefineIndex: index function must be marked iscachable by executing: create index bt_proposta_f01 on propostausing btree (func_cod_secretaria(nr_proponente)); Where nr_proponte is a integer type column. The function is: CREATE OR REPLACE FUNCTION func_cod_secretaria(INTEGER) RETURNS INTEGER AS ' DECLARE v_nr_proponente ALIAS FOR $1; BEGIN return TRUNC(v_nr_proponente/10,0)*10 END;' LANGUAGE 'plpgsql'; >\\\!/< 55 11 5080 9283 !_"""_! Elielson Fontanezi (O) (o) PRODAM - Technical Support Analyst---oOOO--(_)--OOOo--- Success usually comes to those who are too busy to be looking for it. 0 0---( )--( ) \ ( ) / \_/ \_/
Re: [SQL] [GENERAL] ERROR: DefineIndex: index function must be marked
On Fri, 25 Jul 2003, Elielson Fontanezi wrote: > Who can help me on that? > > First of all, my envoronment is: > Linux netlab142.prodam 2.4.8-26mdk #1 Sun Sep 23 17:06:39 CEST 2001 > i686 unknown > pg_ctl (PostgreSQL) 7.2. You should definately move to the highest 7.2 release (7.2.4 I think) which shouldn't require a restore (although you should back up first in any case). I think there were some reasonably important fixes between 7.2.1 and 7.2.4. > CREATE OR REPLACE FUNCTION func_cod_secretaria(INTEGER) RETURNS INTEGER AS ' >DECLARE > v_nr_proponente ALIAS FOR $1; >BEGIN > return TRUNC(v_nr_proponente/10,0)*10 >END; > ' LANGUAGE 'plpgsql' Add WITH (iscachable) after LANGUAGE 'plpgsql' to make the function usable in the functional index unless I'm misremembering the old syntax. ---(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] RES: [GENERAL] ERROR: DefineIndex: index function must be marked iscachable
Thanks a lot! The complete solution is here! 1st. The function wich substitute the trunc() function CREATE OR REPLACE FUNCTION func_cod_secretaria(INTEGER) RETURNS INTEGER AS ' DECLARE v_nr_proponente ALIAS FOR $1; BEGIN return TRUNC(v_nr_proponente/10,0)*10; END; ' LANGUAGE 'plpgsql' WITH isCachable; 2nd. The index on that function CREATE INDEX bt_proposta_f01 ON proposta USING BTREE (func_cod_secretaria(nr_proponente)); 3rd. The anlysis of both queries: the old and new one. a) the old query: --- DEBUG: query: select pa.nr_projeto, pa.dc_denom_projeto, pa.nr_proponente, pa.dc_coordenador, op.dc_proponente from proposta pa inner join orgao_proponente op on (trunc(pa.nr_proponente/10,0)*10 = op.nr_proponente) where pa.in_situacao_proposta <> 'E' ORDER BY 1 DESC; DEBUG: QUERY STATISTICS ! system usage stats: ! 104.665005 elapsed 10.09 user 0.42 system sec ! [10.10 user 0.42 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 141/50 [352/180] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 0/0 [0/0] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 7408 read, 0 written, buffer hit rate = 13.23 % ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written --- b) the new query --- DEBUG: query: select pa.nr_projeto, pa.dc_denom_projeto, pa.nr_proponente, pa.dc_coordenador, op.dc_proponente from proposta pa inner join orgao_proponente op on (op.nr_proponente = func_cod_secretaria(pa.nr_proponente)) where pa.in_situacao_proposta <> 'E'; DEBUG: query: SELECT TRUNC( $1 /10,0)*10 DEBUG: QUERY STATISTICS ! system usage stats: ! 0.130885 elapsed 0.02 user 0.01 system sec ! [0.02 user 0.02 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 168/68 [369/172] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 0/0 [0/0] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks:142 read, 1 written, buffer hit rate = 88.10 % ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written --- -Mensagem original- De: Stephan Szabo [mailto:[EMAIL PROTECTED] Enviada em: sexta-feira, 25 de julho de 2003 16:47 Para: Elielson Fontanezi Cc: pgsql-general; pgsql-sql Assunto: Re: [GENERAL] ERROR: DefineIndex: index function must be marked iscachable On Fri, 25 Jul 2003, Elielson Fontanezi wrote: > Who can help me on that? > > First of all, my envoronment is: > Linux netlab142.prodam 2.4.8-26mdk #1 Sun Sep 23 17:06:39 CEST 2001 > i686 unknown > pg_ctl (PostgreSQL) 7.2. You should definately move to the highest 7.2 release (7.2.4 I think) which shouldn't require a restore (although you should back up first in any case). I think there were some reasonably important fixes between 7.2.1 and 7.2.4. > CREATE OR REPLACE FUNCTION func_cod_secretaria(INTEGER) RETURNS INTEGER AS ' >DECLARE > v_nr_proponente ALIAS FOR $1; >BEGIN > return TRUNC(v_nr_proponente/10,0)*10 >END; > ' LANGUAGE 'plpgsql' Add WITH (iscachable) after LANGUAGE 'plpgsql' to make the function usable in the functional index unless I'm misremembering the old syntax. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] Function index qeustion
Jonathan Bartlett <[EMAIL PROTECTED]> writes: > 1) If you have an index on a cacheable function, does PostgreSQL use the > index instead of calculating the results? Not in general --- only for an indexscan lookup. > 2) How does PostgreSQL know when to recompute the function? Never. That's what the iscachable flag means: you are promising that the function's output for given input never changes. If you aren't prepared to make that promise, you cannot index the function. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
