Re: [SQL] Text/Image, JSP tomcat. How can I operate the text and image
Have a look at lo_import and lo_export. Can handle chunks at a time Also bytea type. You have to handle complete items with this. Although it'd need some formating. I believe zeros go to \000, ' to \' and \ to \\ as a bare minimum, but am not at all sure on that. - Stuart > -Original Message- > From: Frank Zhu [SMTP:[EMAIL PROTECTED]] > Sent: Tuesday, October 16, 2001 4:42 AM > To: [EMAIL PROTECTED] > Subject: Text/Image, JSP tomcat. How can I operate the text and image > type field in Postgresql? only in java/jsp > > I want to store a long article in the Postgresql in Linux, how can I put > the > content into it and redraw it back to show? urgent. Thanks. > I use JSP. > I note that all database systems are very dull in BLOB. Why? > > Frank Zhu. > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Doing a regexp-based search/replace?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello: I've got a table containing property_id's with values of the form ###-. I would like to discard the slash onwards (and I can't use a substr() because I am not guaranteed if a) the - portion exists, b) what position it exists from. If this were a text file, I would use a sed expression such as: cat textfile | sed 's/-.*$//' I've been looking for a way to do this with PostgreSQL but so far haven't found a function that seems to be suitable. I thought maybe I could do it with translate, but translate doesn't appear to work with regular expressions. So far I've tried things like: select translate(property_id, '-.*', '') from mytable; I need to do this, because the -.* portion of my property_id was entered in error, and I would like to do an update on the entire table and just have the left-hand side of the property_id column remaining. Any ideas? Thank you in advance. - ---< LINUX: The choice of a GNU generation. >- Steve Frampton <[EMAIL PROTECTED]> http://www.LinuxNinja.com GNU Privacy Guard ID: D055EBC5 (see http://www.gnupg.org for details) GNU-PG Fingerprint: EEFB F03D 29B6 07E8 AF73 EF6A 9A72 F1F5 D055 EBC5 -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.0 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7zv1TmnLx9dBV68URAisEAJ4nNYz4lxpgWojULE/Xo9SUb5IexgCfS9At J6kAVn/3vFHeJkl9bjr4AcQ= =W4xQ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Doing a regexp-based search/replace?
Steve, > I've got a table containing property_id's with values of the form > ###-. I would like to discard the slash onwards (and I can't use > a > substr() because I am not guaranteed if a) the - portion exists, > b) > what position it exists from. > > If this were a text file, I would use a sed expression such as: > > cat textfile | sed 's/-.*$//' In SQL/plpgsql, you can't do this with a single expression. However, you can do it with three expressions put together. CREATE FUNCTION remove_propid_tail ( VARCHAR ) RETURNS VARCHAR AS' SELECT SUBSTR($1, 1, ((STRPOS($1, ''-'') - 1)); END;' LANGUAGE 'SQL'; Then run: UPDATE main_table SET property_id = remove_propid_tail(property_id) WHERE property_id ~ '-'; -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] COUNT func
It worked! I checked the query plan it generates, and it's really a one-pass scan. thanks, Oleg Stephan Szabo wrote: > On Thu, 18 Oct 2001, Oleg Lebedev wrote: > > > Hi, > > I am trying to count the number or rows in a table with similar field > > values. I want to do it in one table scan. > > In other words, say I want to count the number of rows in Person table, > > having age equal to 40, and the number of rows with status as 'married'. > > I want Person table to be scanned only once during this query. > > So, basically I want to do the following: > > SELECT COUNT(age=40), COUNT(status='married') > > FROM Person; > > > > I tried to use aggregate functions with group by and having clauses, but > > I still can't figure it out (probably because of the lack of > > experience). > > Maybe something like [untested] > SELECT SUM(case when age=40 then 1 else 0 end), >SUM(case when status='married' then 1 else 0 end) > FROM PERSON; > will work? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] COUNT func
hi, try this too.. select count(*),age,status from person where age=40 and status='MARRIED' group by age,status; Regards, Bhuvaneswar. On Oct 18, Oleg Lebedev wrote: > Hi, > I am trying to count the number or rows in a table with similar field > values. I want to do it in one table scan. > In other words, say I want to count the number of rows in Person table, > having age equal to 40, and the number of rows with status as 'married'. > I want Person table to be scanned only once during this query. > So, basically I want to do the following: > SELECT COUNT(age=40), COUNT(status='married') > FROM Person; > > I tried to use aggregate functions with group by and having clauses, but > I still can't figure it out (probably because of the lack of > experience). > Please help. > Thanks, > > Oleg > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [ADMIN] update in rule
You can use the following to install plpgsql: CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'; I assume that you have default installation of postgres, otherwise change the path in the second line. good luck Oleg Dmitry Morozovsky wrote: > On Wed, 17 Oct 2001, Stephan Szabo wrote: > > SS> Use a trigger instead, something like > SS> > SS> create function adresses_trigger() returns opaque as ' > SS> begin > SS> NEW.date_maj := now(); > SS> return NEW; > SS> end;' language 'plpgsql'; > > hmm. it seems defaul pgsql installation does not contains definition for > plpgsql language: > > test=# select * from pg_language ; > lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler > --+-+--+---+- > internal | f | f| 0 | n/a > C| f | f| 0 | /bin/cc > sql | f | f| 0 | postgres > (3 rows) > > however, plpgsql.so is in pgsql lib directory (though it is *NOT* visible > by ldconfig -r) > > My installation is 7.1.3 under FreeBSD 4-stable. Is it somehow my fault or > does it need to manually do something like > > create function plpgsql_handler() ... > create language 'plpgsql' ... > > ? Please give me advise how exactly should these statements look? > > Sincerely, > D.Marck [DM5020, DM268-RIPE, DM3-RIPN] > > *** Dmitry Morozovsky --- D.Marck --- Wild Woozle --- [EMAIL PROTECTED] *** > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Deleting obsolete values
DELETE FROM partitur WHERE EXISTS (SELECT * FROM partitur AS ss_partitur
WHERE ss_partitur.userid=partitur.userid AND ss_partitur.ts>partitur.ts);
Seems like it should seems like it should delete all old values (however I
have not tested it)
- Stuart
> -Original Message-
> From: Haller Christoph [SMTP:[EMAIL PROTECTED]]
> Sent: Tuesday, October 16, 2001 5:45 PM
> To: [EMAIL PROTECTED]
> Subject: Deleting obsolete values
>
> This may look familiar to you - it was on the list last month.
> Consider the following table
> create table partitur
> (userid text, val integer, ts timestamp DEFAULT NOW() );
> Do some inserts
> insert into partitur values('Bart', 1440);
> insert into partitur values('Lisa', 1024);
> insert into partitur values('Bart', 7616);
> insert into partitur values('Lisa', 3760);
> insert into partitur values('Bart', 3760);
> insert into partitur values('Lisa', 7616);
> To retrieve the latest values (meaning the last ones inserted)
> Tom Lane wrote
> >This is what SELECT DISTINCT ON was invented for. I don't know any
> >comparably easy way to do it in standard SQL, but with DISTINCT ON
> >it's not hard:
> >SELECT DISTINCT ON (userid) userid, val, ts FROM partitur
> >ORDER BY userid, ts DESC;
>
> My question now is
> Is there a way to delete all rows the select statement did not
> bring up?
> After that *unknown* delete statement
> select userid, val, ts from partitur ;
> should show exactly the same as the SELECT DISTINCT ON (userid) ...
> did before.
>
> Regards, Christoph
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Granting database level permissions...
Is it possible to grant database level access to a user in PostgreSQL? I have created a user and database, and I want the user to have full control over that database. I have been through the online docs and news groups but have not found a statement or answer to the question. Thomas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] index question
Joseph Shraibman <[EMAIL PROTECTED]> writes: > Lets say I have a table with columns a and b. I want to do a query like > SELECT count(distinct b) WHERE a = 2; > Should I have an index on a or an index on (a,b)? At present, only an index on a will help any. Our implementation of count(distinct ...) isn't bright enough to make use of indexes. Now an index on (a,b) can substitute for an index on a, so if you have other queries that could use both columns of the (a,b) index then it might be worth making that instead of an index on a. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] oid's in views.
CREATE VIEW testview AS SELECT test.oid, tull FROM test; ERROR: Attribute 'oid' has a name conflict Name matches an existing system attribute I think this should be allowed, because if you do: CREATE VIEW testview AS SELECT tull FROM test; SELECT oid, tull FROM testview; The oid column will always be blank. The oid column can never be used for anything usefull. There are many workarounds to this problem - and I appreciate that altering this behaviour might be contradictory to some design philosophy you may have. Just my comment that's all! Aasmund Midttun Godal [EMAIL PROTECTED] - http://www.godal.com/ +47 40 45 20 46 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Replication
Title: Replication Does Postgres in anyway support replication? Will I be able to have load-balancing between two postgres databases? RAY HUNTER Automated Test Group Software Support Engineer ENTERASYS NETWORKS Internal: 53888 Phone: 801 887-9888 Fax: 801 972-5789 Cellular: 801 698-0622 [EMAIL PROTECTED] www.enterasys.com
Re: [SQL] When will vacuum go away?
BTW will there be a 7.1.4 release before 7.2 comes out so we can dump our databases to upgrade to 7.2 w/o there being 60 in the seconds field? Tom Lane wrote: > "Michael Richards" <[EMAIL PROTECTED]> writes: > >>I've been watching for this for some time. First it was 7.0, then >>7.1. Does anyone have any idea on when the row re-use code will be >>ready? >> > > VACUUM isn't disappearing any time soon, but 7.2's version of vacuum > runs in parallel with normal transactions, so it's not so painful to > run it frequently. See discussion in development docs, > http://candle.pha.pa.us/main/writings/pgsql/sgml/maintenance.html > > >>Given trouble with Great Bridge is there any info out there on when >>7.2 might hit the streets? >> > > The last several postponements of 7.2 beta have *not* been the fault > of the ex-GreatBridge folks around here. > > You can find a snapshot that should be pretty durn close to 7.2beta1 > at ftp://ftp2.us.postgresql.org/pub/dev/postgresql-snapshot.tar.gz > (note that at last word, other mirrors were not up to date --- if > the doc/TODO file doesn't contain a date in October, it's stale). > I think the only thing we're still waiting on is some datetime fixes > from Tom Lockhart... > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] COUNT func
Hi, I am trying to count the number or rows in a table with similar field values. I want to do it in one table scan. In other words, say I want to count the number of rows in Person table, having age equal to 40, and the number of rows with status as 'married'. I want Person table to be scanned only once during this query. So, basically I want to do the following: SELECT COUNT(age=40), COUNT(status='married') FROM Person; I tried to use aggregate functions with group by and having clauses, but I still can't figure it out (probably because of the lack of experience). Please help. Thanks, Oleg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] COUNT func
On Thu, 18 Oct 2001, Oleg Lebedev wrote: > Hi, > I am trying to count the number or rows in a table with similar field > values. I want to do it in one table scan. > In other words, say I want to count the number of rows in Person table, > having age equal to 40, and the number of rows with status as 'married'. > I want Person table to be scanned only once during this query. > So, basically I want to do the following: > SELECT COUNT(age=40), COUNT(status='married') > FROM Person; > > I tried to use aggregate functions with group by and having clauses, but > I still can't figure it out (probably because of the lack of > experience). Maybe something like [untested] SELECT SUM(case when age=40 then 1 else 0 end), SUM(case when status='married' then 1 else 0 end) FROM PERSON; will work? ---(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] Table Constraints with NULL values
It appears that Postgres will allow any INSERT, despite a multiple-column
constraint, if any of the values INSERTed are NULL. If I read the included
excerpt correctly (there are like three negatives in the second sentence,
sheesh :) ), multiple NULL values for a column are acceptable or, in other
words, are not a violation of UNIQUEness.
However, shouldn't any values that are not NULL violate the constraint if
the same values exist already?
As an example, into the table definition at the bottom of this message...
This should be acceptable.
INSERT INTO ao_functions (name, skill, arg1, arg2, arg3) VALUES (NULL,
NULL, NULL, NULL, NULL);
INSERT INTO ao_functions (name, skill, arg1, arg2, arg3) VALUES (NULL,
NULL, NULL, NULL, NULL);
But this should not...
INSERT INTO ao_functions (name, skill, arg1, arg2, arg3) VALUES
('Hit', 1, -1, -1, 91);
INSERT INTO ao_functions (name, skill, arg1, arg2, arg3) VALUES
('Hit', 1, -1, -1, 91);
ERROR: Cannot insert a duplicate key into unique index unique_aofunction
Why does this succeed?
INSERT INTO ao_functions (name, skill, arg1, arg2, arg3) VALUES
('TauntNPC', 1, NULL, NULL, NULL);
INSERT INTO ao_functions (name, skill, arg1, arg2, arg3) VALUES
('TauntNPC', 1, NULL, NULL, NULL);
-- Excerpt from the Postgres CREATE TABLE documentation ---
...
UNIQUE Constraint
...
The column definitions of the specified columns do not have to include a NOT
NULL constraint to be included in a UNIQUE constraint. Having more than one
null value in a column without a NOT NULL constraint, does not violate a
UNIQUE constraint. (This deviates from the SQL92 definition, but is a more
sensible convention. See the section on compatibility for more details.)
...
--- End of Excerpt --
CREATE TABLE ao_functions (
id SERIAL CONSTRAINT funckey PRIMARY KEY,
name CHARACTER(25),
skill INTEGER NULL,
arg1CHARACTER VARYING(100) NULL DEFAULT NULL,
arg2CHARACTER VARYING(100) NULL DEFAULT NULL,
arg3CHARACTER VARYING(100) NULL DEFAULT NULL,
CONSTRAINT unique_aofunction UNIQUE (name, skill, arg1, arg2, arg3)
);
David Allardyce
---(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] index question
Lets say I have a table with columns a and b. I want to do a query like SELECT count(distinct b) WHERE a = 2; Should I have an index on a or an index on (a,b)? -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Replication
Ray, > Does Postgres in anyway support replication? Will I be able to have > load-balancing between two postgres databases? Not at this time. GreatBridge was working on replication when they shut down. It's very likely that Red Hat will add replication once they get up to speed on Postgres, but there are no definite plans. -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Replication
> "Hunter, Ray" wrote: > > Does Postgres in anyway support replication? Will I be able to have > load-balancing between two postgres databases? > > RAY HUNTER > Automated Test Group > Software Support Engineer > > ENTERASYS NETWORKS > > Internal: 53888 > Phone: 801 887-9888 > Fax: 801 972-5789 > Cellular: 801 698-0622 > > [EMAIL PROTECTED] > > www.enterasys.com in the 7.1 contrib section there should be some code in there under rserv/ good luck ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Doing a regexp-based search/replace?
Well, the easiest general way is probably a plperl function, but I think
the following may work for your specific case:
update mytable set
property_id=substr(property_id, 1, position('-' in property_id)-1)
where position('-' in property_id)!=0;
On Thu, 18 Oct 2001, Steve Frampton wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Hello:
>
> I've got a table containing property_id's with values of the form
> ###-. I would like to discard the slash onwards (and I can't use a
> substr() because I am not guaranteed if a) the - portion exists, b)
> what position it exists from.
>
> If this were a text file, I would use a sed expression such as:
>
> cat textfile | sed 's/-.*$//'
>
> I've been looking for a way to do this with PostgreSQL but so far haven't
> found a function that seems to be suitable. I thought maybe I could do it
> with translate, but translate doesn't appear to work with regular
> expressions. So far I've tried things like:
>
> select translate(property_id, '-.*', '') from mytable;
>
> I need to do this, because the -.* portion of my property_id was entered
> in error, and I would like to do an update on the entire table and just
> have the left-hand side of the property_id column remaining.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Doing a regexp-based search/replace?
You could write a Tcl (i.e. pltcl) function, and use that to do what you want:
CREATE FUNCTION remove(varchar) RETURNS varchar AS '
set input $1
regsub -- {-.*$} $input {} output
return $output
' language 'pltcl';
[NOTE: untested]
you may have to monkey with the regexp to get exactly what you want...
--brett
On Thu, 18 Oct 2001 12:03:28 -0400 (EDT)
Steve Frampton <[EMAIL PROTECTED]> wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Hello:
>
> I've got a table containing property_id's with values of the form
> ###-. I would like to discard the slash onwards (and I can't use a
> substr() because I am not guaranteed if a) the - portion exists, b)
> what position it exists from.
>
> If this were a text file, I would use a sed expression such as:
>
> cat textfile | sed 's/-.*$//'
>
> I've been looking for a way to do this with PostgreSQL but so far haven't
> found a function that seems to be suitable. I thought maybe I could do it
> with translate, but translate doesn't appear to work with regular
> expressions. So far I've tried things like:
>
> select translate(property_id, '-.*', '') from mytable;
>
> I need to do this, because the -.* portion of my property_id was entered
> in error, and I would like to do an update on the entire table and just
> have the left-hand side of the property_id column remaining.
>
> Any ideas? Thank you in advance.
>
> - ---< LINUX: The choice of a GNU generation. >-
> Steve Frampton <[EMAIL PROTECTED]> http://www.LinuxNinja.com
> GNU Privacy Guard ID: D055EBC5 (see http://www.gnupg.org for details)
> GNU-PG Fingerprint: EEFB F03D 29B6 07E8 AF73 EF6A 9A72 F1F5 D055 EBC5
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.0.0 (GNU/Linux)
> Comment: For info see http://www.gnupg.org
>
> iD8DBQE7zv1TmnLx9dBV68URAisEAJ4nNYz4lxpgWojULE/Xo9SUb5IexgCfS9At
> J6kAVn/3vFHeJkl9bjr4AcQ=
> =W4xQ
> -END PGP SIGNATURE-
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Doing a regexp-based search/replace?
Steve Frampton <[EMAIL PROTECTED]> writes: > If this were a text file, I would use a sed expression such as: > cat textfile | sed 's/-.*$//' > I've been looking for a way to do this with PostgreSQL but so far haven't > found a function that seems to be suitable. Write a function in pltcl or plperl, either of which can mash text strings with ease and abandon ... 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] index problem
Hi, Stephan Szabo wrote: > On Tue, 16 Oct 2001, CoL wrote: > > >>--- >>The 2 table query, where prog_data has ~8800 rowsn and index on prog_id: >>bash-2.04$ time echo "explain select distinct >>prog_id,prog_ftype,prog_fcasthour,prog_date from prog_dgy_xy,prog_data >>where pxygy_pid=prog_id " | psql -Uuser db >>NOTICE: QUERY PLAN: >> >>Unique (cost=7432549.69..7680455.07 rows=2479054 width=32) >> -> Sort (cost=7432549.69..7432549.69 rows=24790538 width=32) >> -> Merge Join (cost=148864.65..161189.33 rows=24790538 width=32) >> -> Index Scan using prog_data_pkey on prog_data >>(cost=0.00..701.12 rows=8872 width=28) >> -> Sort (cost=148864.65..148864.65 rows=921013 width=4) >> -> Seq Scan on prog_dgy_xy (cost=0.00..30145.13 >>rows=921013 width=4) >> > > I'm guessing that the approximately 25 million row estimate on the join > has to be wrong as well given that prog_data.prog_id should be unique. > > Hmm, does the explain change if you vacuum analyze the other table > (prog_data)? If not, what does explain show if you do a > set enable_seqscan='off'; > before it? The result: db=>set enable_seqscan='off'; db=>explain select distinct prog_id,prog_ftype,prog_fcasthour,prog_date from prog_dgy_xy,prog_data where pxygy_pid=prog_id; NOTICE: QUERY PLAN: Unique (cost=7606982.10..7854887.48 rows=2479054 width=32) -> Sort (cost=7606982.10..7606982.10 rows=24790538 width=32) -> Merge Join (cost=0.00..335621.73 rows=24790538 width=32) -> Index Scan using progdgyxy_idx2 on prog_dgy_xy (cost=0.00..323297.05 rows=921013 width=4) -> Index Scan using prog_data_pkey on prog_data (cost=0.00..701.12 rows=8872 width=28) It "seems" index is used, but the same result :(((, and bigger execution time: real 3m41.830s What is in tables? prog_data contains unique id and other info. prog_dgy_xy contains that id with x,y coordinates (so many ids from prog_data with unique x,y) #prog_data: #prog_id, prog_ftype, prog_fcasthour, prog_date #1 'type'6 2001-10-14 12:00:00 #2 'type'12 2001-10-14 12:00:00 #prog_dgy_xy: #pxygy_pid, pxygy_x, pxygy_y #1 0.1 0.1 #1 0.1 0.15 How can this query takes real0m1.755s for mysql, [17 sec for oracle], and 2-3 minutes!! for postgres? And why: POSTGRES: set enable_seqscan ='off'; select count(*) from prog_dgy_xy where pxygy_pid<13161; count 900029 real2m34.340s explain: Aggregate (cost=327896.89..327896.89 rows=1 width=0) -> Index Scan using progdgyxy_idx2 on prog_dgy_xy (cost=0.00..325594.54 rows=920940 width=0) MYSQL: select count(pxygy_pid) from PROG_DGY_XY where pxygy_pid<13161 count(pxygy_pid) 900029 real0m27.878s explain: table typepossible_keys key key_len ref rowsExtra PROG_DGY_XY range progdgyxy_idx1,progdgyxy_idx2 progdgyxy_idx2 4 NULL906856 where used; Using index The same time difference in case of: = or >, however explain says, cause seq scan is off, the index is used. I did vacuum, and vacuum analyze too before. PS: I think i have to make a site for that, cause there are many questions :), and weird things. I love postgres but this makes me "hm?". Today i'll make these test under 7.1.2. thx CoL ---(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] When will vacuum go away?
Joseph Shraibman <[EMAIL PROTECTED]> writes: > BTW will there be a 7.1.4 release before 7.2 comes out so we can dump > our databases to upgrade to 7.2 w/o there being 60 in the seconds > field? I doubt it. We're having enough trouble trying to get everyone lined up to produce a 7.2 beta :-(. Producing another 7.1 patch release isn't in the cards. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Access to functions
There is currently no functionality to grant or deny access to functions. I believe anyone can run any function and the function is run as the user who runs it. This is also the case with views, which is odd, as the rest of the select statement is granted access to a table which the function in the view does not have access to. Functions triggered seem on the other handed to be 'trusted'. This can obviously be exploited. I have noticed a trusted field in the pg_proc and pg_language tables, can I use this somehow (I haven't got my hopes up as they are all 't' on pg_proc), ?? I looked through the todo, and albeit being a lot of new features in the grant section I cannot find any info regarding these issues. Sincerely, Aasmund. Aasmund Midttun Godal [EMAIL PROTECTED] - http://www.godal.com/ +47 40 45 20 46 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
