Re: [SQL] [GENERAL] sort by relevance
> How am I able to sort query results by relevance? > I use contrib/tsearch to search using fill text index! I think you need to use OpenFTS (openfts.sf.net). tsearch does not provide relevance ranking at this time. Regards, Bjoern ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] cannot create function that uses variable table name
As per Matthew Nuzum's post My query is very similar... I need sql functions to update the database. If I specify the filename etc they work. BUT that leads to 6 functions which are exactly the same apart from the file they update. 1) why can't I use a variable name and 2) could someone please point me towards some examples of EXECUTE if thats the only way to do it? my example is: CREATE FUNCTION retire(varchar, varchar, int4) returns int4 AS' BEGIN UPDATE $1 SET "Retired" = 'true' WHERE $2 = $3; SELECT (whatever to return the int4); END;' Language 'plpgsql'; which has: parse error at or near "true" ($1 is the filename, $2 is the fieldname, $3 is the fieldvalue and "Retired" is a boolean field in each of the files) Thanks Beth ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] plpgsql: debugging
Hi, Searching Google I found a thread in July 2001 concerning the facilities for debugging plpgsql functions. The actual answer was: it should be improved. What is the best way to debug a plpgsql function? Oliver -- VECERNIK Datenerfassungssysteme A-2560 Hernstein, Hofkogelgasse 17 Tel.: +43 2633 47530, Fax: DW 50 http://members.aon.at/vecernik ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] CAST from VARCHAR to INT
(Postgres 7.2.1) I screwed up when I was designing a table a while back and made a column a VARCHAR that referenced (and should have been) an INT. Now I'm trying to correct my mistake, I've created a new table and I'm trying to INSERT INTO...SELECT the data into it, but it's complaining that it can't stick a VARCHAR into an INT. All the values in the column are valid integers (the foreign key sees to that) but even a CAST won't do it. How can I force it to copy/change the values? Luke Pascoe Senior Developer / Systems administrator KMG (NZ) Limited. http://www.kmg.co.nz Mobile: (021) 303019 Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL to list databases?
On Thu, Jan 23, 2003 at 02:33:55PM -0600, Kristopher Yates wrote: > I have been wondering the same thing.. SELECT * FROM pg_databases ? > >Is there a query that will return all the databases available, similar > >to what psql -l does? The handy "-E" flag to psql helps: roberto@brasileiro:~$ psql -E -l * QUERY ** SELECT d.datname as "Name", u.usename as "Owner", pg_encoding_to_char(d.encoding) as "Encoding" FROM pg_database d LEFT JOIN pg_user u ON d.datdba = u.usesysid ORDER BY 1; ** List of databases Name | Owner| Encoding ++-- addresses | windozefoo | LATIN1 foobar | roberto| LATIN1 openacs-4 | roberto| LATIN1 template0 | postgres | LATIN1 template1 | postgres | LATIN1 -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + Dente lupus, cornu taurus petit. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Scheduling Events?
On Thu, Jan 23, 2003 at 04:16:52PM -0800, David Durst wrote: > Is there anyway to schedule DB Events based on time? > So lets say I had a table w/ depreciation schedules in it, > I would like the DB to apply the formula and make the entries on the END > of every month. 1) Write a script that invokes psql with the appropriate queries 2) Schedule a cron job to the end of the month Each tool doing its own small task well done. -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + Junior, quit playing with your floppy! ---(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]
Hi one and all, Could some one tell me if there is a command or script that will inform me of every foreign constraint constructed in my tables? I would greatly appreciate it. Thank you Bruce [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] To use a VIEW or not to use a View.....
First of all I want to thank you for all responses! I was overwhelmed with it :D Below you find the schema I'm currently using and the output of explain. I removed all comments so the mail will be small, the schema is still work in progress. I especially I need to take a look at the indexes. Any hints will be appreciated. best reghards, Ries van Twisk <--- Here you find the output of the explain again: I cannot yet read the output of explain si I'm not sure if the output looks good or bad. echo "VACUUM ANALYZE; EXPLAIN SELECT * FROM full_cablelist WHERE projectcode=5" | psql testdb > /tmp/explain.txt NOTICE: QUERY PLAN: Hash Join (cost=26.28..39.00 rows=23 width=200) -> Hash Join (cost=24.85..37.17 rows=23 width=182) -> Hash Join (cost=23.43..35.34 rows=23 width=164) -> Seq Scan on libitems lit (cost=0.00..7.39 rows=339 width=27) -> Hash (cost=23.37..23.37 rows=23 width=137) -> Hash Join (cost=11.05..23.37 rows=23 width=137) -> Hash Join (cost=9.75..21.67 rows=23 width=120) -> Seq Scan on libitems lif (cost=0.00..7.39 rows=339 width=27) -> Hash (cost=9.69..9.69 rows=23 width=93) -> Hash Join (cost=4.76..9.69 rows=23 width=93) -> Hash Join (cost=3.46..7.99 rows=23 width=76) -> Hash Join (cost=2.42..6.32 rows=69 width=63) -> Seq Scan on cablelist cl (cost=0.00..2.69 rows=69 width=41) -> Hash (cost=2.06..2.06 rows=106 width=22) -> Seq Scan on cabletypes ct (cost=0.00..2.06 rows=106 width=22) -> Hash (cost=1.04..1.04 rows=1 width=13) -> Seq Scan on projectcodes pc (cost=0.00..1.04 rows=1 width=13) -> Hash (cost=1.24..1.24 rows=24 width=17) -> Seq Scan on libconnections lcf (cost=0.00..1.24 rows=24 width=17) -> Hash (cost=1.24..1.24 rows=24 width=17) -> Seq Scan on libconnections lct (cost=0.00..1.24 rows=24 width=17) -> Hash (cost=1.34..1.34 rows=34 width=18) -> Seq Scan on shiplocations slt (cost=0.00..1.34 rows=34 width=18) -> Hash (cost=1.34..1.34 rows=34 width=18) -> Seq Scan on shiplocations slf (cost=0.00..1.34 rows=34 width=18) <-- CREATE FUNCTION ord_fn (text,text) RETURNS text AS ' SELECT (CASE WHEN $1 < $2 THEN $1 || $2 ELSE $2 || $1 END) as t; ' LANGUAGE SQL WITH (iscachable); CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS '/usr/lib/postgresql/plpgsql.so' LANGUAGE 'C'; CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'; CREATE TABLE cabletypes ( id SERIAL, cabletype VARCHAR(24) NOT NULL CHECK ( length(cabletype) > 1 ), -- Naam van de kabel coretypeVARCHAR(16) NOT NULL CHECK ( length(coretype) > 1 ) -- Type kabel/aantal aders ); CREATE UNIQUE INDEX cabletypes_idx ON cabletypes (id); CREATE FUNCTION f_check_cabletypes() RETURNS OPAQUE AS ' DECLARE check RECORD; BEGIN SELECT INTO check * FROM cabletypes WHERE cabletype=NEW.cabletype AND coretype=NEW.coretype LIMIT 1; IF FOUND THEN RAISE EXCEPTION ''[0001] cabletype and coretype combination already exsists in cabletypes!''; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER tr_cabletypes BEFORE INSERT OR UPDATE ON cabletypes FOR EACH ROW EXECUTE PROCEDURE f_check_cabletypes(); CREATE TABLE marktypes ( id SERIAL, nameVARCHAR(24) NOT NULL UNIQUE,-- Naam van de markering color INTEGER NOT NULL-- Eventuele kleur ); CREATE UNIQUE INDEX marktypes_idx ON marktypes (id); CREATE TABLE projectcodes ( id SERIAL, projectcode VARCHAR(16) NOT NULL UNIQUE,-- Project code naam projectname VARCHAR(64) NOT NULL, -- Project uitleg deleted BOOLEAN DEFAULT 'false' NOT NULL ); CREATE UNIQUE INDEX projectcodes_idx ON projectcodes (id); CREATE TABLE libitems ( id SERIAL, projectcodeid INTEGER DEFAULT 0 NOT NULL REFERENCES projectcodes(id) ON DELETE CASCADE, nameVARCHAR(32) NOT NULL UNIQUE -- Naam van de item bv boiler ); CREATE UNIQUE INDEX libitems_idx ON libitems(id); CREATE FUNCTION f_check_libitems() RETURNS OPAQUE AS ' DECLARE check RECORD; BEGIN -- Updat
Re: [SQL] PostgreSQL + SSL
> I´m trying to config PG with SSL, but i got a error. I create the key > and the certificate and put both in $PGDATA directory. I also enabled > the ssl option in postgresql.conf. But when i run postmaster i got a > error saying that server.key has wrong permissions. It reports the error in either of the below cases: 1. If the file permission is not -r--r--r--. 2. If the certificate and the private key are invalid. The clear advice is available in the documentation itself. Try out http://developer.postgresql.org/docs/postgres/ssl-tcp.html for details. regards, bhuvaneswaran ---(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] DBCC CheckIdent in a stored proc?
> I need to know the last id of the last record added to the table from an > ASP page. => select field1 from my_table order by oid desc limit 1; -- will do that. regards, bhuvaneswaran ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] To use a VIEW or not to use a View.....
Ries van Twisk wrote: First of all I want to thank you for all responses! I was overwhelmed with it :D Below you find the schema I'm currently using and the output of explain. I removed all comments so the mail will be small, the schema is still work in progress. I especially I need to take a look at the indexes. Any hints will be appreciated. best reghards, Ries van Twisk <--- Here you find the output of the explain again: I cannot yet read the output of explain si I'm not sure if the output looks good or bad. It looks like your cablelist table doesn't contain too many records, so result is inacurate. Postgresql doesn't use indexes if you have too little rows. First look on your explain is ok, your query should work fine if tables are well indexed. Make additional tests with tables containing more rows, "explain analyze" helps a bit, because it shows real times. Tomasz Myrta echo "VACUUM ANALYZE; EXPLAIN SELECT * FROM full_cablelist WHERE projectcode=5" | psql testdb > /tmp/explain.txt NOTICE: QUERY PLAN: Hash Join (cost=26.28..39.00 rows=23 width=200) -> Hash Join (cost=24.85..37.17 rows=23 width=182) -> Hash Join (cost=23.43..35.34 rows=23 width=164) -> Seq Scan on libitems lit (cost=0.00..7.39 rows=339 width=27) -> Hash (cost=23.37..23.37 rows=23 width=137) -> Hash Join (cost=11.05..23.37 rows=23 width=137) -> Hash Join (cost=9.75..21.67 rows=23 width=120) -> Seq Scan on libitems lif (cost=0.00..7.39 rows=339 width=27) -> Hash (cost=9.69..9.69 rows=23 width=93) -> Hash Join (cost=4.76..9.69 rows=23 width=93) -> Hash Join (cost=3.46..7.99 rows=23 width=76) -> Hash Join (cost=2.42..6.32 rows=69 width=63) -> Seq Scan on cablelist cl (cost=0.00..2.69 rows=69 width=41) -> Hash (cost=2.06..2.06 rows=106 width=22) -> Seq Scan on cabletypes ct (cost=0.00..2.06 rows=106 width=22) -> Hash (cost=1.04..1.04 rows=1 width=13) -> Seq Scan on projectcodes pc (cost=0.00..1.04 rows=1 width=13) -> Hash (cost=1.24..1.24 rows=24 width=17) -> Seq Scan on libconnections lcf (cost=0.00..1.24 rows=24 width=17) -> Hash (cost=1.24..1.24 rows=24 width=17) -> Seq Scan on libconnections lct (cost=0.00..1.24 rows=24 width=17) -> Hash (cost=1.34..1.34 rows=34 width=18) -> Seq Scan on shiplocations slt (cost=0.00..1.34 rows=34 width=18) -> Hash (cost=1.34..1.34 rows=34 width=18) -> Seq Scan on shiplocations slf (cost=0.00..1.34 rows=34 width=18) ---(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] calling function from rule
I have a view and update rule on it, which updates another table. Now I would like to call a function, after update of the table is done. Also the function must be called in the same transaction as update. I tried to define an additional update rule on the view, but doing a SELECT in update rule spoils the affected records count: hekotek=# create table a(id integer); CREATE TABLE hekotek=# create view v as select * from a; CREATE VIEW hekotek=# create rule r as on update to v do instead update a set id = new.id where id = old.id; CREATE RULE hekotek=# create rule r1 as on update to v do select 1; CREATE RULE hekotek=# insert into a values (1); INSERT 1194985 1 hekotek=# update v set id = 2; ?column? -- 1 (1 row) hekotek=# drop rule r1 on v; DROP RULE hekotek=# update v set id = 3; UPDATE 1 It's important to me to have correct affected records count returned. I cannot use triggers, because views can't have triggers. I also cannot define the trigger on the table, because the function must be called only when updated through the view. I think I could define the rule to call the function in update query for some dummy table: update dummy set field = function(parameters). But is there a better way? Tambet ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Slow query
Hi List,
I need your help, I don't know what happend with my queries when I use this
table "simon".When I like display the result the query is very slow and take
five minutes or more.
The number of registers in simon is:2421580
and the other table "llamada" the number of registers is :1779741 but the
time of my query is faster that when i run the query but using my table
simon
The struct of my table "simon" is:
Table "simon"
Attribute |Type |Modifier
---+-+-
num | integer | not null default nextval('simon_num_seq'::text)
ip| varchar(16) |
fecha | date|
hora | time|
tras | smallint|
reci | smallint|
perd | smallint|
min | float4 |
avg | float4 |
max | float4 |
Indices:
avg_si,fecha_si,hora_si,ip_si,max_si,min_si,perd_si,reci_si,simon_hora,
simon_pkey,tras_si
I run Explain and this is the result:
EXPLAIN SELECT * from simon where fecha='20030122' order by hora desc;
NOTICE: QUERY PLAN:
Sort (cost=57700.73..57700.73 rows=23682 width=46)
-> Seq Scan on simon (cost=0.00..55591.05 rows=23682 width=46)
and when I run explain for another table this is te result:
EXPLAIN SELECT * from llamada where fecha='20030122' order by hora desc;
NOTICE: QUERY PLAN:
Sort (cost=41102.70..41102.70 rows=12413 width=82)
-> Index Scan using fecha_lla on llamada (cost=0.00..39944.95 rows=12413
width=82)
This is the sintax when i created my index:
CREATE INDEX fecha_si ON simon USING btree(fecha);
If somebody can help me or tell me why my queries using the table simon are
slow, I will thankfull
Regards,
Ricardo
PD:Sorry for my grammar Enghish
_
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Slow query
Ricardo Javier Aranibar León wrote: I run Explain and this is the result: EXPLAIN SELECT * from simon where fecha='20030122' order by hora desc; NOTICE: QUERY PLAN: Sort (cost=57700.73..57700.73 rows=23682 width=46) -> Seq Scan on simon (cost=0.00..55591.05 rows=23682 width=46) and when I run explain for another table this is te result: EXPLAIN SELECT * from llamada where fecha='20030122' order by hora desc; NOTICE: QUERY PLAN: Sort (cost=41102.70..41102.70 rows=12413 width=82) -> Index Scan using fecha_lla on llamada (cost=0.00..39944.95 rows=12413 width=82) I'm not sure if there is much to help. You have a lots of rows in result - 23k rows and 12k rows. The problem is in output, not in query or indexing. Do you really use such queries? Anyway, did you vaccum analyze before testing? Regards, Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] calling function from rule
"Tambet Matiisen" <[EMAIL PROTECTED]> writes: > I have a view and update rule on it, which updates another table. Now I would like >to call a function, after update of the table is done. Also the function must be >called in the same transaction as update. I tried to define an additional update rule >on the view, but doing a SELECT in update rule spoils the affected records count: Try 7.3, we changed the rules about returned records count. http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/rules-status.html regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] calling function from rule
> > "Tambet Matiisen" <[EMAIL PROTECTED]> writes: > > I have a view and update rule on it, which updates another > table. Now I would like to call a function, after update of > the table is done. Also the function must be called in the > same transaction as update. I tried to define an additional > update rule on the view, but doing a SELECT in update rule > spoils the affected records count: > > Try 7.3, we changed the rules about returned records count. > http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/ru > les-status.html > I have 7.3. When rule and action are the same, everything works fine. Doing an insert in update rule and opposite are OK too. Problem is, when I do select in insert/update/delete rule. Then the result of select is returned instead of command status, even if the select is done in non-instead rule and there is unconditional instead rule. See the example in my previous mail. Tambet ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] To use a VIEW or not to use a View.....
On Wed, 22 Jan 2003, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > On Wed, 22 Jan 2003, Tom Lane wrote: > >> Stephan Szabo <[EMAIL PROTECTED]> writes: > >>> The filter is applied only to a. So, if you really wanted the > >>> c.a=3 condition to be applied for whatever reason you're out of > >>> luck. > >> > >> FWIW, CVS tip is brighter: the condition does propagate to both relations. > > > Yeah. I was going to ask how hard you thought it would be to do for > > this particular sort of case. I thought about the simple case of using > > and realized it'd probably be reasonable in amount of work, but it seems > > I don't have to think about it. :) > > It could still use more eyeballs looking at it. One thing I'm concerned > about is whether the extra (derived) conditions lead to double-counting > restrictivity and thus underestimating the number of result rows. I > haven't had time to really test that, but I suspect there may be a problem. I haven't looked at code yet but tried examples like Tomasz's and some simple ones and have gotten reasonable seeming output for the estimates given accurate statistics (joining two estimate 3 outputs, getting 8 for the estimated rows, joining that with another copy getting 50 some odd where in this case the real would be 81). Not that I did a particularly thorough test. I hope to get a chance over the next couple of days to look and run more tests. Tomasz, if you have the chance, you might want to try CVS and see what it does for the queries you've been working with. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] calling function from rule
"Tambet Matiisen" <[EMAIL PROTECTED]> writes: >> Try 7.3, we changed the rules about returned records count. > I have 7.3. When rule and action are the same, everything works fine. > Doing an insert in update rule and opposite are OK too. Problem is, > when I do select in insert/update/delete rule. Then the result of > select is returned instead of command status, even if the select > is done in non-instead rule and there is unconditional instead rule. Oh, I think your complaint is really about the fact that psql doesn't print the command status if it got any tuples (or even just a tuple descriptor) in the result series. AFAICT the information returned by the backend is sensible in this situation: the "UPDATE 1" status message *is* returned and is available from PQcmdStatus. psql is just choosing not to print it. I'm not sure that that's wrong, though. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] To use a VIEW or not to use a View.....
Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Wed, 22 Jan 2003, Tom Lane wrote:
>> It could still use more eyeballs looking at it. One thing I'm concerned
>> about is whether the extra (derived) conditions lead to double-counting
>> restrictivity and thus underestimating the number of result rows. I
>> haven't had time to really test that, but I suspect there may be a problem.
> I haven't looked at code yet but tried examples like Tomasz's and some
> simple ones and have gotten reasonable seeming output for the estimates
> given accurate statistics
I realized this morning that there definitely is a problem. Consider
this example using the regression database:
regression=# explain analyze select * from tenk1 a join tenk1 b using(ten)
regression-# where ten = 3;
QUERY PLAN
--
Merge Join (cost=1055.45..2102.12 rows=83006 width=488) (actual
time=582.97..65486.57 rows=100 loops=1)
Merge Cond: ("outer".ten = "inner".ten)
-> Sort (cost=527.73..530.00 rows=910 width=244) (actual time=373.57..382.48
rows=1000 loops=1)
Sort Key: a.ten
-> Seq Scan on tenk1 a (cost=0.00..483.00 rows=910 width=244) (actual
time=8.98..330.39 rows=1000 loops=1)
Filter: (ten = 3)
-> Sort (cost=527.73..530.00 rows=910 width=244) (actual time=209.19..8057.64
rows=999001 loops=1)
Sort Key: b.ten
-> Seq Scan on tenk1 b (cost=0.00..483.00 rows=910 width=244) (actual
time=0.40..193.93 rows=1000 loops=1)
Filter: (3 = ten)
Total runtime: 73291.01 msec
(11 rows)
The condition "ten=3" will select 1000 rows out of the 1 in the
table. But, once we have applied that condition to both sides of the
join, the join condition "a.ten = b.ten" is a no-op --- it will not
reject any pair of rows coming out of the seqscans. Presently we count
its restrictivity anyway, so the estimated row count at the merge is a
bad underestimate.
Not only should we ignore the join condition for selectivity purposes,
but it's a waste of time for execution as well. We could have
implemented the above query as a nestloop with no join condition, and
saved the effort of the sort and merge logic.
What I was thinking was that any time the code sees a "var = const"
clause as part of a mergejoin equivalence set, we could mark all the
"var = var" clauses in the same set as no-ops. For example, given
WHERE a.f1 = b.f2 AND b.f2 = c.f3 AND c.f3 = 42
then after we finish deducing a.f1 = 42 and b.f2 = 42, there is no
longer any value in either of the original clauses a.f1 = b.f2 and
b.f2 = c.f3, nor in the other deduced clause a.f1 = c.f3. This would
take a little bit of restructuring of generate_implied_equalities() and
process_implied_equality(), but it doesn't seem too difficult to do.
Thoughts? Are there any holes in that logic?
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] To use a VIEW or not to use a View.....
On Thu, Jan 23, 2003 at 08:53:53AM -0800, Stephan Szabo wrote: > On Wed, 22 Jan 2003, Tom Lane wrote: > > I haven't looked at code yet but tried examples like Tomasz's and some > simple ones and have gotten reasonable seeming output for the estimates > given accurate statistics (joining two estimate 3 outputs, getting 8 for > the estimated rows, joining that with another copy getting 50 some odd > where in this case the real would be 81). Not that I did a > particularly thorough test. I hope to get a chance over the next couple > of days to look and run more tests. > > Tomasz, if you have the chance, you might want to try CVS and see what it > does for the queries you've been working with. Not too easy. Currently I have only windows machine with Postgresql/cygwin. I use dial-up for accessing internet, which isn't nice to use. I will try this if I find some free computer to install postgresql/linux ;-) Anyway I already gave up this kind of query, especially I can't use CVS as production server (should I?) Regards, Tomasz Myrta ---(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] To use a VIEW or not to use a View.....
On Thu, 23 Jan 2003, Tom Lane wrote:
> regression=# explain analyze select * from tenk1 a join tenk1 b using(ten)
> regression-# where ten = 3;
> QUERY PLAN
>
>--
> Merge Join (cost=1055.45..2102.12 rows=83006 width=488) (actual
>time=582.97..65486.57 rows=100 loops=1)
>Merge Cond: ("outer".ten = "inner".ten)
>-> Sort (cost=527.73..530.00 rows=910 width=244) (actual time=373.57..382.48
>rows=1000 loops=1)
> Sort Key: a.ten
> -> Seq Scan on tenk1 a (cost=0.00..483.00 rows=910 width=244) (actual
>time=8.98..330.39 rows=1000 loops=1)
>Filter: (ten = 3)
>-> Sort (cost=527.73..530.00 rows=910 width=244) (actual time=209.19..8057.64
>rows=999001 loops=1)
> Sort Key: b.ten
> -> Seq Scan on tenk1 b (cost=0.00..483.00 rows=910 width=244) (actual
>time=0.40..193.93 rows=1000 loops=1)
>Filter: (3 = ten)
> Total runtime: 73291.01 msec
> (11 rows)
Yeah, I see it once I got the estimated selectivity being smaller in the
joins in my test database as well.
> Not only should we ignore the join condition for selectivity purposes,
> but it's a waste of time for execution as well. We could have
> implemented the above query as a nestloop with no join condition, and
> saved the effort of the sort and merge logic.
>
> What I was thinking was that any time the code sees a "var = const"
> clause as part of a mergejoin equivalence set, we could mark all the
> "var = var" clauses in the same set as no-ops. For example, given
>
> WHERE a.f1 = b.f2 AND b.f2 = c.f3 AND c.f3 = 42
>
> then after we finish deducing a.f1 = 42 and b.f2 = 42, there is no
> longer any value in either of the original clauses a.f1 = b.f2 and
> b.f2 = c.f3, nor in the other deduced clause a.f1 = c.f3. This would
> take a little bit of restructuring of generate_implied_equalities() and
> process_implied_equality(), but it doesn't seem too difficult to do.
>
> Thoughts? Are there any holes in that logic?
The main thing I can think of is being careful when the types are
different (like padding vs no padding in strings). Playing with text and
char() the explain output appears to be resulting in the right thing
for the clauses but I'm not 100% sure.
Given that it only appears to bring across equality conditions and
not the parts of conditions with or, I think you're right in general.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] CREATE VIEW ERROR
I found answer. Thank you for everyone who did not reply. CREATE VIEW depend_view AS SELECT depend.subfunction_id, a.subfunction_file AS x, depend.subfunction_dep_id, b.subfunction_file AS y FROM depend INNER JOIN subfunction a ON depend.subfunction_id = a.subfunction_id INNER JOIN subfunction b ON depend.subfunction_dep_id = b.subfunction_id; "Igor" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... > Hi, > > I have following SQL statement which does not report any errors: > > test=# SELECT a.subfunction_file, b.subfunction_file > test-# FROM depend INNER JOIN subfunction a ON depend.subfunction_id = > a.subfunction_id > test-# INNER JOIN subfunction b ON depend.subfunction_dep_id = > b.subfunction_id; > subfunction_file | subfunction_file > + > show_batch_2.php | search_receipt.php > ... > > But when I try to create VIEW a have following error: > > test=# create view depend_view as SELECT a.subfunction_file, > b.subfunction_file > test-# FROM depend INNER JOIN subfunction a ON depend.subfunction_id = > a.subfunction_id > test-# INNER JOIN subfunction b ON depend.subfunction_dep_id = > b.subfunction_id; > > ERROR: CREATE TABLE: attribute "subfunction_file" duplicated > <<< > > > Please advise where is my mistake. I use: > > test=# select version(); >version > - > PostgreSQL 7.2.1 on i386-unknown-freebsd4.3, compiled by GCC 2.95.3 > (1 row) > > > Thank you, > > Igor ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] SQL to list databases?
Is there a query that will return all the databases available, similar to what psql -l does? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] To use a VIEW or not to use a View.....
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Thu, 23 Jan 2003, Tom Lane wrote: >> What I was thinking was that any time the code sees a "var = const" >> clause as part of a mergejoin equivalence set, we could mark all the >> "var = var" clauses in the same set as no-ops. For example, given >> WHERE a.f1 = b.f2 AND b.f2 = c.f3 AND c.f3 = 42 >> then after we finish deducing a.f1 = 42 and b.f2 = 42, there is no >> longer any value in either of the original clauses a.f1 = b.f2 and >> b.f2 = c.f3, nor in the other deduced clause a.f1 = c.f3. This would >> take a little bit of restructuring of generate_implied_equalities() and >> process_implied_equality(), but it doesn't seem too difficult to do. >> >> Thoughts? Are there any holes in that logic? > The main thing I can think of is being careful when the types are > different (like padding vs no padding in strings). This is a matter of being careful about marking cross-datatype operators as mergejoinable. We do not mark 'bpchar = text' as mergejoinable --- in fact we don't even have such an operator. AFAICS any pitfalls in those semantics come up already from the existing logic to treat mergejoinable equality as transitive for variables. Extending that transitivity to constants can't create problems that wouldn't exist anyway. For reference, these are the only cross-datatype mergejoinable operators as of CVS tip: regression=# select oid::regoperator,oprcode from pg_operator where oprlsortop!=0 and oprleft!=oprright; oid| oprcode --+--- =(integer,bigint)| int48eq =(bigint,integer)| int84eq =(smallint,integer) | int24eq =(integer,smallint) | int42eq =(real,double precision) | float48eq =(double precision,real) | float84eq =(smallint,bigint) | int28eq =(bigint,smallint) | int82eq (8 rows) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] SQL to list databases?
--On Thursday, January 23, 2003 12:56:50 -0600 Ben Siders <[EMAIL PROTECTED]> wrote: Is there a query that will return all the databases available, similar to what psql -l does? $ psql -E -l * QUERY ** SELECT d.datname as "Name", u.usename as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding" FROM pg_catalog.pg_database d LEFT JOIN pg_catalog.pg_user u ON d.datdba = u.usesysid ORDER BY 1; ** ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(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] SQL to list databases?
I have been wondering the same thing.. Thanks Kris Ben Siders wrote: Is there a query that will return all the databases available, similar to what psql -l does? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org . ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Rename database?
Can I use a SQL statement to rename database? e.g Rename Database Bank to Database Bank_backup Thanks! Wei
Re: [SQL] SQL to list databases?
On Thu, Jan 23, 2003 at 12:56:50PM -0600, Ben Siders wrote: > > Is there a query that will return all the databases available, similar > to what psql -l does? Any time you wish to know what psql is doing with ''meta'' commands (like the \ commands), use the -E switch, which will display the queries it's sending to the backend. man psql for more details. --keith -- [EMAIL PROTECTED] public key: http://wombat.san-francisco.ca.us/kkeller/kkeller.asc alt.os.linux.slackware FAQ: http://wombat.san-francisco.ca.us/cgi-bin/fom ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL to list databases?
psql -E causes psql to show it's "behind the scenes" queries to try: psql -lE (that's a lower case ell before the E) Cheers, Steve On Thursday 23 January 2003 10:56 am, Ben Siders wrote: > Is there a query that will return all the databases available, similar > to what psql -l does? > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Scheduling Events?
Is there anyway to schedule DB Events based on time? So lets say I had a table w/ depreciation schedules in it, I would like the DB to apply the formula and make the entries on the END of every month. ---(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] cannot create function that uses variable table name
Thanks for your reply David...
1)The "" round Retired are to label the column/field 'Retired' rather
than 'retired' (someone else created the database with Upper case
titles!)
2) Your code is correct.. cept that single quotes have to be escaped(?!)
so the following will do the trick when updating text fields...
CREATE FUNCTION temp(text,text,int4) RETURNS integer AS '
DECLARE
update_table ALIAS FOR $1;
update_field ALIAS FOR $2;
update_id ALIAS FOR $3;
BEGIN
EXECUTE ''UPDATE ''|| quote_ident(update_table) || '' SET "Retired" =
true WHERE '' || quote_ident(update_field) || '' = '' ||
quote_literal(update_id);
RETURN update_id;
END;
' language 'plpgsql';
which creates...
and: select temp('TableName', 'TableID', 20);
returns 20.
On Fri, 2003-01-24 at 13:13, David Durst wrote:
> > I need sql functions to update the database. If I specify the filename
> > etc they work. BUT that leads to 6 functions which are exactly the same
> > apart from the file they update.
> >
> > 1) why can't I use a variable name and
> > 2) could someone please point me towards some examples of EXECUTE if
> > thats the only way to do it?
> >
> >
> > my example is:
> >
>
> This should work
>
> CREATE FUNCTION retire(varchar, varchar, int4) returns int4 AS'
> DECLARE
>varone ALIAS FOR $1;
>vartwo ALIAS FOR $2;
>varthr ALIAS FOR $3;
> BEGIN
> UPDATE varone SET "Retired" = 'true' WHERE vartwo = varthr;
> SELECT (whatever to return the int4);
> END;'
> Language 'plpgsql';
>
>
> And I am not sure of the purpose for the "" around Retired
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] CAST from VARCHAR to INT
> (Postgres 7.2.1) > > I screwed up when I was designing a table a while back and made a column a > VARCHAR that referenced (and should have been) an INT. > > Now I'm trying to correct my mistake, I've created a new table and I'm > trying to INSERT INTO...SELECT the data into it, but it's complaining that > it can't stick a VARCHAR into an INT. All the values in the column are valid > integers (the foreign key sees to that) but even a CAST won't do it. > > How can I force it to copy/change the values? > varchar cannot be casted to integer directly. Rather we can do it this way: => select your_varchar_field::text::int from your_table; regards, bhuvaneswaran ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] CAST from VARCHAR to INT
"Luke Pascoe" <[EMAIL PROTECTED]> writes: > Now I'm trying to correct my mistake, I've created a new table and I'm > trying to INSERT INTO...SELECT the data into it, but it's complaining that > it can't stick a VARCHAR into an INT. All the values in the column are valid > integers (the foreign key sees to that) but even a CAST won't do it. I think you need to cast via TEXT. regression=> select 'z'::varchar::int; ERROR: Cannot cast type character varying to integer regression=> select 'z'::varchar::text::int; ERROR: pg_atoi: error in "z": can't parse "z" regression=> select '42'::varchar::text::int; int4 -- 42 (1 row) regards, tom lane ---(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] Scheduling Events?
On a side note, if the DB doesn't support this capability.
Does anyone see a HORRIBLE issue w/ creating a C func
something of this nature.
int handle_temporal_events() {
if(fork == 0) {
//In here we monitor what time it is
//And maintain a Datastructure w/ events
//And update it every so often
//Then preform various background tasks
}
else if(fork == -1) {
//Thread error
}
}
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] quastions about primary key
Is that possible to have a two columns primary key on a table with null value on second column? Jack ---(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] Scheduling Events?
On Thu, 23 Jan 2003, David Durst wrote: > Is there anyway to schedule DB Events based on time? Yes! cron > So lets say I had a table w/ depreciation schedules in it, > I would like the DB to apply the formula and make the entries on the END > of every month. > > > > ---(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 > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
