[GENERAL] Call for Beta Testers (PG Lightning Admin)
If anyone is interested in beta testing a new win32 based admin tool for Postgresql 8.x please sign up here: http://www.amsoftwaredesign.com/betatest.html Screen shots are available here: http://www.amsoftwaredesign.com/pg_ss.asp.asp We will be accepting 30 beta testers and each tester will receive a free one year subscription (starting at 1.0 release). Upon release the software will sell for $29.99 for a one year subscription. After one year, subscribers will be able to renew for $15.00 This software is geared to DBAs and developers and includes such features as: Function Version Control Advanced Function Editor with code completion and param hinting and the ability to print ability to print result sets. Export result sets to PDF,Excel,XML, Comma separated,Tab separated or as insert statements Tabbed Enterprise Manager, each database opens in it's own tab (makes working on several copies a snap and eliminates the confusion of everything in a single object tree). MDI style application which is familiar to windows users. MDI window task bar that filters based on the Enterprise Manager tab selection. Will run on Windows 98 and up. Note: this software is geared for Corporate/Home Windows users and is not cross platform, though it does run on the latest version of WINE (we will not support running on WINE) Thanks, Tony Caduto AM Software Design http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] deleting from arrays
On Sun, Jan 16, 2005 at 11:56:04PM -0600, [EMAIL PROTECTED] wrote:
>
> I've searched the documentation for a simple way to delete a single value from
> an array, i've come up with a complecated way to do it, but was wondering if
> there was some simple command to remove a single value from an array, where
> the
> position of the value in the array is unknown.
For integer arrays see the contrib/intarray module. Otherwise you
could write a function and create an operator around it -- maybe
there's an easier way, but the following works for arrays of any
type in simple tests:
CREATE FUNCTION array_remove(anyarray, anyelement) RETURNS anyarray AS '
DECLARE
a ALIAS FOR $1;
v ALIAS FOR $2;
newa a%TYPE := ''{}'';
i integer;
BEGIN
FOR i IN array_lower(a, 1) .. array_upper(a, 1) LOOP
IF a[i] <> v THEN
newa := array_append(newa, a[i]);
END IF;
END LOOP;
RETURN newa;
END;
' LANGUAGE plpgsql IMMUTABLE STRICT;
CREATE OPERATOR - (
LEFTARG = anyarray,
RIGHTARG = anyelement,
PROCEDURE = array_remove
);
SELECT '{bob,carol,ted,alice}'::text[] - 'carol';
?column?
-
{bob,ted,alice}
(1 row)
SELECT '{2,3,5,7}'::int[] - 3;
?column?
--
{2,5,7}
(1 row)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Cursor bug?
On Fri, Jan 14, 2005 at 01:28:47PM +0100, Martijn van Oosterhout wrote: > On Thu, Jan 13, 2005 at 04:35:04PM -0600, Mike G. wrote: > > thank you. > > > > I use the cursor because I really do an update against a different > > table based on a value from the select in the original table. I am > > eagerly awaiting 8.0 and the ability to issue an Update Table1 Set > > Table1.col = Table2.col Using (Select y from Table2) > > What's this USING keyword you're referring to, I can't find it in the > documentation anywhere, what does it do? It doesn't look like it has been added to the documentation yet. The only reference I could find to it was in the todo list (create similiar ability for delete statement). USING allows you to add join statements to your update statement and use the data in the columns of the joined tables as input into the table the update is performed against. http://manuals.sybase.com/onlinebooks/group-as/asg1/250e/sqlug/@Generic_BookTextView/27741 > > Beside, it's seems to me your entire function could be replaced by a > single query: > > insert into temp_tables.delete_me > select name, file_number, question_to_change, > new_result, email > from source_table > where question_to_change = 'consultant' > and file_number is not NULL; > > or something like that, i didn't check the schema. But this is probably > a cut down version. Tom's suggestion to use a FOR IN SELECT loop is a > good one. > > Hope this helps, > -- > Martijn van Oosterhout http://svana.org/kleptog/ > > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > > tool for doing 5% of the work and then sitting around waiting for someone > > else to do the other 95% so you can sue them. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] deleting from arrays
I've searched the documentation for a simple way to delete a single value from an array, i've come up with a complecated way to do it, but was wondering if there was some simple command to remove a single value from an array, where the position of the value in the array is unknown. thanks, matt ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] pgpsql help
"Mike G." <[EMAIL PROTECTED]> writes: > Maybe it is because 'name' is a sql 92/99 non-reserved / key word? No, it's because of careless choice of plpgsql variable names. >> declare >> uri alias for $3; >> ... >> insert into uri (articleid,uri,uritype) values (article_id,uri,urit_id); ^^^^^^ ^^^ Not being exceedingly bright, plpgsql will attempt to substitute its variable into all three matches in this command. Two of those are of course wrong and lead to syntax errors. First rule of plpgsql programming: do not use plpgsql variable names that duplicate any of the SQL-level names (tables, columns, functions, etc) you intend to use in the function. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] pgpsql help
On Mon, Jan 17, 2005 at 05:46:20AM +0100, LENGYEL Zoltan wrote: > uri alias for $3; ... > insert into uri (articleid,uri,uritype) values (article_id,uri,urit_id); PostgreSQL 8.0 will give more context for the error, making it easier to spot the problem: ERROR: syntax error at or near "$1" at character 14 QUERY: insert into $1 (articleid, $2 ,uritype) values ( $3 , $4 , $5 ) CONTEXT: PL/pgSQL function "new_uri" line 20 at SQL statement LINE 1: insert into $1 (articleid, $2 ,uritype) values ( $3 , $4 ,... The variable "uri" is apparently also the name of a table and a column in that table. Use a different name for the variable. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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: [GENERAL] pgpsql help
Maybe it is because 'name' is a sql 92/99 non-reserved / key word?
On Mon, Jan 17, 2005 at 05:46:20AM +0100, LENGYEL Zoltan wrote:
> Hi,
>
> I have a pgpsql function:
>
> create or replace function new_uri(varchar,varchar,varchar) returns
> integer as '
> declare
> src alias for $1;
> tit alias for $2;
> uri alias for $3;
> article_id integer := nextval(''articleid'');
> src_id integer;
> state_id integer;
> urit_id integer;
> begin
>
> select into src_id id from sources where name = src;
> select into state_id id from state where name = ''collected'';
> select into urit_id id from uritype where name = ''original html'';
> raise notice ''insert into article (id,source,title,state) values
> (%,%,%,%)'',article_id,src_id,tit,state_id;
> insert into article (id,source,title,state) values
> (article_id,src_id,tit,state_id);
> raise notice ''insert into uri (articleid,uri,uritype) values
> (%,%,%)'',article_id,uri,urit_id;
> insert into uri (articleid,uri,uritype) values (article_id,uri,urit_id);
>
> return 1;
> end;
> ' language 'plpgsql';
>
> An i also have some output:
>
> ifa=> \i new_uri.sql
> CREATE FUNCTION
> ifa=> select new_uri('index.hu','juscsenko','index.hu/foobar');
> NOTICE: insert into article (id,source,title,state) values
> (56,3,juscsenko,1)
> NOTICE: insert into uri (articleid,uri,uritype) values
> (56,index.hu/foobar,3)
> ERROR: syntax error at or near "$1" at character 14
> CONTEXT: PL/pgSQL function "new_uri" line 17 at SQL statement
> ifa=>
>
> What should i do?
>
>
> thx,
>
> War_New
>
> ---(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
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] pgpsql help
Hi,
I have a pgpsql function:
create or replace function new_uri(varchar,varchar,varchar) returns
integer as '
declare
src alias for $1;
tit alias for $2;
uri alias for $3;
article_id integer := nextval(''articleid'');
src_id integer;
state_id integer;
urit_id integer;
begin
select into src_id id from sources where name = src;
select into state_id id from state where name = ''collected'';
select into urit_id id from uritype where name = ''original html'';
raise notice ''insert into article (id,source,title,state) values
(%,%,%,%)'',article_id,src_id,tit,state_id;
insert into article (id,source,title,state) values
(article_id,src_id,tit,state_id);
raise notice ''insert into uri (articleid,uri,uritype) values
(%,%,%)'',article_id,uri,urit_id;
insert into uri (articleid,uri,uritype) values (article_id,uri,urit_id);
return 1;
end;
' language 'plpgsql';
An i also have some output:
ifa=> \i new_uri.sql
CREATE FUNCTION
ifa=> select new_uri('index.hu','juscsenko','index.hu/foobar');
NOTICE: insert into article (id,source,title,state) values
(56,3,juscsenko,1)
NOTICE: insert into uri (articleid,uri,uritype) values
(56,index.hu/foobar,3)
ERROR: syntax error at or near "$1" at character 14
CONTEXT: PL/pgSQL function "new_uri" line 17 at SQL statement
ifa=>
What should i do?
thx,
War_New
---(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: [GENERAL] Postgresql 8.0 and Cancel/Kill backend functions
Magnus Hagander wrote: The cancel function is implemented. See http://developer.postgresql.org/docs/postgres/functions-admin.html#FUNCT IONS-ADMIN-SIGNAL-TABLE. Kill function was considered too dangerous. Pity - I would have loved this for my test harnesses. I need to drop and recreate the database between each test and, unless I can kill them, a test that fails to close a connection victimizes all subsequent tests. (But not a showstopper in our case - we replace the connect method with a wrapper and have the harnesses keep track of the connection. This only leaves connections opened by spawned processes a problem.) It would be great if this was available as an external method I could install into a particular database. Hmm... I guess it wouldn't be difficult to write this - it would simply involve selecting the procpid from pg_stat_activity and sending a kill signal to it, wouldn't it? -- Stuart Bishop <[EMAIL PROTECTED]> http://www.stuartbishop.net/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Iis there anyway to do this(see in message)
Tony Caduto wrote:
Hi,
Just wondering if there is a way to monitor the SQL that is executed
inside a PL/pgsql function?
I can see some SQL via the stats view, but it does not show the sql that
is being executed in a function.
The reason I ask is it would make debugging large complex functions much
easier.
I get a lot of requests from former MS SQL server DBAs/developers for
such capablility.
Thanks,
Tony Caduto
http://www.amsoftwaredesign.com
I am not sure if this is what you mean but this is what I do to see
what DB calls are being made. I add a print statement just before the
actual SQL call (which I actually print to the filehandle 'LOG' which
points to a log file). For example:
$db_update=$DB->prepare("UPDATE file_info SET file_size=?, file_perm=?,
file_acc_time=?, file_mod_time=?, file_user_uid=?, file_user_name=?,
file_group_uid=?, file_group_name=? WHERE file_parent_dir=? AND
file_name=? AND file_type=?") || die...
...
if ( $log_level >= 4 ) { print LOG " |- ".__LINE__." DBI: UPDATE
file_info_".$dev_id." SET file_size=$size, file_perm=$mode,
file_acc_time=$atime, file_mod_time=$mtime, file_user_uid=$uid,
file_user_name=$user, file_group_uid=$gid, file_group_name=$group WHERE
file_parent_dir='/' AND file_name='.' AND file_type='d';\n"; }
$db_update->execute($size, $mode, $atime, $mtime, $uid, $user, $gid,
$group, "/", ".", "d") || die...
Noting of course that the quoting might be slightly off because a
prepared statement automatically quotes values as needed. If you wanted
to be really carefully you could quote the values for your log like so:
if ( $log_level >= 4 )
{
$db_user=$DB->quote($user);
$db_group=$DB->quote($group);
print LOG " |- ".__LINE__." DBI: UPDATE file_info SET file_size=$size,
file_perm=$mode, file_acc_time=$atime, file_mod_time=$mtime,
file_user_uid=$uid, file_user_name=$db_user, file_group_uid=$gid,
file_group_name=$db_group WHERE file_parent_dir='/' AND file_name='.'
AND file_type='d';\n";
}
I hope this was along the lines of your question.
Madison
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Iis there anyway to do this(see in message)
On Sun, Jan 16, 2005 at 07:00:42PM -0600, Tony Caduto wrote: > Just wondering if there is a way to monitor the SQL that is executed > inside a PL/pgsql function? > I can see some SQL via the stats view, but it does not show the sql that > is being executed in a function. You can get some logging with the log_statement and debug_* configuration settings, although maybe not as much as you'd like. http://www.postgresql.org/docs/7.4/static/runtime-config.html#RUNTIME-CONFIG-LOGGING-WHAT > The reason I ask is it would make debugging large complex functions much > easier. > I get a lot of requests from former MS SQL server DBAs/developers for > such capablility. People request a trace feature for PL/pgSQL from time to time but I don't think anybody has implemented it yet. I'm sure a patch would be welcome -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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
[GENERAL] Iis there anyway to do this(see in message)
Hi, Just wondering if there is a way to monitor the SQL that is executed inside a PL/pgsql function? I can see some SQL via the stats view, but it does not show the sql that is being executed in a function. The reason I ask is it would make debugging large complex functions much easier. I get a lot of requests from former MS SQL server DBAs/developers for such capablility. Thanks, Tony Caduto http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Problem with win32 installer for PG 8.0
I had this problem too, I am not sure why it happens. I am just guessing but I think its because of XP sp2. If you want to use the latest ms installer, at least at this point, you must install sp2. Anyway, what I did is something very funny. I opened the pg installer many times and then opened the task manager and started randomly closing msi processes and somehow the installation continues and opens the next frame and you can continue the install. Regards, tzahi. > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Tony Caduto > Sent: Monday, January 17, 2005 1:52 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Problem with win32 installer for PG 8.0 > > > Hi, > I installed beta 2 a couple of months ago, and today I installed RC 5 > and it seems there is no way to tell the installer where to actually > install, so I could not re use my data cluster from the beta > 2(with out > renaming the directory to the same name as what the installer would > install). > > I am not sure it this is a limitation of the MSI installer or it was > just overlooked. > > Also why do all the PG projects insist on using M$ installer > technology? A better choice would be Inno Setup at > http://www.jrsoftware.org. It's a ton easier to use and would be a > better choice than msi. > > > ---(end of > broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Index optimization ?
"Florian G. Pflug" <[EMAIL PROTECTED]> writes: > Lets say, you have an query "select * from table where field = function()". Maybe this would be clearer with a more egregious example of volatility. Say you had a function odd() that returns 1 and 0 alternating. That is, it returns 1 the first time it's called, 0 the second time it's called, then 1, then 0, etc. If you did "select * from tab where col = odd()" you would expect to get half of the rows where col=0 or col=1. Of course since the order is unpredictable there's no way to know which ones but you should still be pretty sure it'll be half of the rows. If Postgres used an index it would call odd(), which would return 1 because it's the first time, and then Postgres would go look up the rows where col is 1 and return all of them. That's a very different behaviour from if the index isn't used. If all the records have col=1 then you're getting all of the records instead of half of them. If col=0 then you're getting none of them instead of half of them. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Problem with win32 installer for PG 8.0
Hi, I installed beta 2 a couple of months ago, and today I installed RC 5 and it seems there is no way to tell the installer where to actually install, so I could not re use my data cluster from the beta 2(with out renaming the directory to the same name as what the installer would install). I am not sure it this is a limitation of the MSI installer or it was just overlooked. Also why do all the PG projects insist on using M$ installer technology? A better choice would be Inno Setup at http://www.jrsoftware.org. It's a ton easier to use and would be a better choice than msi. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Index optimization ?
Bo Lorentsen <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> SELECT * FROM mytable WHERE random() < 0.1; >> If we evaluated random() only once in this query, we would get either >> all or none of the rows, clearly not the right answer. >> > So if the random function was stable, you either get all or none, as et > gets executed only ones ? No, you'd still end up with a seqscan, because this WHERE clause offers no chance of matching an index, and we don't do anything special with stable functions beyond trying to match them to index conditions. But consider something like SELECT * FROM mytable WHERE keycol = int(random() * 1000); where keycol is indexed and contains integers 0..1000; let's say each such value appears ten times. With a seqscan implementation (which I consider is what SQL defines the semantics to be) random() would be recomputed at each row and there would be about a 1/1000 chance of selecting each row. You might get more or less than exactly ten result rows, and they'd almost certainly contain different values of keycol. Now if random() were marked stable (and of course both multiply and int() are immutable), then the planner would consider an indexscan on keycol to be a valid optimization. But that would produce distinguishably different results, because random() would be evaluated only once: you would always get exactly ten rows and they'd always all have the same keycol value. >> An indexscan is a legal optimization only if the function(s) in the >> WHERE clause are all STABLE or better. This is because the index access >> code will only evaluate the righthand side of the "indexcol = something" >> clause once, and then will use that value to descend the btree and >> select matching index entries. We must be certain that this gives the >> same result we would get from a seqscan. >> > Now this sounds like a blink of the problem that I don't understand :-) > When you say it evaluate "right side" ones, what kind of information are > you (the executer) then getting, and how is the index match then > performed. An index can basically implement conditions like "WHERE indexedcol = constant" --- it takes the constant value and searches the index for matches. (Btrees can also do things like WHERE indexedcol <= constant, but let's just think about equality to keep things simple.) We can deal with a nonconstant righthand side, so long as it's okay to evaluate the value just once before the index starts to do its thing. That assumption is what STABLE is all about. regards, tom lane ---(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] ntfs for windows port rc5-2
Magnus Hagander wrote: rc5-2 msi will not install at all on a fat32 filesystem even without initialising the database. Really? The code for checking the filesystem type is only executed if you chose to initdb, so I really don't see this happening. Exactly what message do you get? Log in the temp install dir: The Cacls command can be run only on disk drives that use the NTFS file system I'll have to rip half or more of the full log as it seems to be to large for the list to accept I assume you are talking about the initdb.log file? That file is created by initdb.bat, which should only be called when you choose to run initdb. Exactly which options did you specify during the installation? //Magnus with msi installer, options are only for where to install, until initdb stage. chose no at that point, and it installs, then errors and completely un-installs. leaving a dir struct under program files with a single file: pgperm.log under the directory with the msi files in it there is a full install log, which the list has twice refused to accept as being to large. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Index optimization ?
Bo Lorentsen wrote:
So if the random function was stable, you either get all or none, as et
gets executed only ones ?
An indexscan is a legal optimization only if the function(s) in the
WHERE clause are all STABLE or better. This is because the index access
code will only evaluate the righthand side of the "indexcol = something"
clause once, and then will use that value to descend the btree and
select matching index entries. We must be certain that this gives the
same result we would get from a seqscan.
Now this sounds like a blink of the problem that I don't understand :-)
When you say it evaluate "right side" ones, what kind of information are
you (the executer) then getting, and how is the index match then
performed. Is all the where clause expression marked as volatile at this
level, just to be sure ?
Lets say, you have an query "select * from table where field =
function()". Now, according to the sql-spec, you would have to
scan each row in "table", call the function "functio()", and compare the
result. If the result of the call to "function()" matches the value in
"field", the you return the row, otherwise you don't return it.
An index is a tree, where each node has two subnodes/children. Each node
representents a row of your table (or, rather, references a row - it
contails only the value of the field you indexed). Additionally,
the value of the field of the "left" child (and the value of the field
of its children, and so on) is always guaranteed to be smaller-or-equal
to the value of field of the node itself, and the value
of the field of the "right" child is always guaranteed to be
greater-or-equal to the value of the field of the node.
So, if you have three records in the table "table", like this:
f1
--
1
2
3
Then your index looks the following:
2
/ \
1 3
Now, when doing an index lookup, you have to know which value to look
for (lets say you look for 3). Then you look at the top node, and
compare the value you are looking for to the value of the node. In our
case, the node has a smaller value then the one we are looking for.
Because we know that the left child of the toplevel node will have an
even smaller value, we don't need to look at the left child at all. We
just check the right child, and there we find our record with "field"=3.
_BUT_ this only works, because we knew for which value to look before we
started searching. If we the value we look for is constantly changing,
our index lookup would return bogus results. So, if the value is unknown
_at_the_beginning_ of the search, you can't use the index, because the
power of an index search comes from the idea to skip a whole subtree (in
our case the left one), because we _know_ it can't contain the value we
are looking for.
Functions marked "immutable" or "stable" is _guaranteed_ to never change
their value during a select statement, or at least not in an
unpredictable way. Thats why you can use return values of "immutable" or
"stable" functions for an index search.
Well maybe the real question is how does the executer match an index, or
am I off track ?
Lets say, you are doing the following query
"select * from table where field1 = currval('seq')" and field2 =
nextval('seq')
Now, the value of "currval('seq')" changes with every row visited. In
your example, the value of "currval" is actually stable - but postgres
has no way to know this. To use an index scan for your query, postgres
would need to know, that only a call to nextval can change the value of
currval - but this, of course, is a quite difficult dependency for a
database to track.
greetings, Florian Pflug
smime.p7s
Description: S/MIME Cryptographic Signature
Re: [GENERAL] ntfs for windows port rc5-2
>>>rc5-2 msi will not install at all on a fat32 filesystem >>>even without initialising the database. >> >> >> Really? The code for checking the filesystem type is only executed if >> you chose to initdb, so I really don't see this happening. >Exactly what >> message do you get? >> >Log in the temp install dir: >The Cacls command can be run only on disk drives that use the >NTFS file >system > >I'll have to rip half or more of the full log as it seems to >be to large >for the list to accept I assume you are talking about the initdb.log file? That file is created by initdb.bat, which should only be called when you choose to run initdb. Exactly which options did you specify during the installation? //Magnus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Index optimization ?
On Sun, 2005-01-16 at 14:11 -0500, Tom Lane wrote: > Ragnar =?ISO-8859-1?Q?Hafsta=F0?= <[EMAIL PROTECTED]> writes: > > On Sun, 2005-01-16 at 17:45 +0100, Bo Lorentsen wrote: > >> Why not use the index scan for every row, is this a "limit" in the > >> planner ? I think there is something in the planner I don't understand :-) > > > the planner will just use the plan it estimates will be fastest. > > because of how indexscans work in postgresql, in this case it would be > > slower than a tablescan (assuming the function really is volatile) > > It has nothing to do with speed, it has to do with giving the correct > answer. We define "correct answer" as being the result you would get > from a naive interpretation of the SQL semantics --- that is, for every > row in the FROM table, actually execute the WHERE clause, and return the > rows where it produces TRUE. I should not have used the word 'indexscan'. I just meant that it would be less effective to use an index to look up each result of the volatile function than using a tablescan. It was clear that the function would have to be called for each row, but the OP was asking (I think) why the index was not used. gnari ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Index optimization ?
Tom Lane wrote: It has nothing to do with speed, it has to do with giving the correct answer. We define "correct answer" as being the result you would get from a naive interpretation of the SQL semantics --- that is, for every row in the FROM table, actually execute the WHERE clause, and return the rows where it produces TRUE. As an example, a query like SELECT * FROM mytable WHERE random() < 0.1; should produce a random sampling of about one-tenth of the rows in mytable. Nice explaination ... If we evaluated random() only once in this query, we would get either all or none of the rows, clearly not the right answer. So if the random function was stable, you either get all or none, as et gets executed only ones ? An indexscan is a legal optimization only if the function(s) in the WHERE clause are all STABLE or better. This is because the index access code will only evaluate the righthand side of the "indexcol = something" clause once, and then will use that value to descend the btree and select matching index entries. We must be certain that this gives the same result we would get from a seqscan. Now this sounds like a blink of the problem that I don't understand :-) When you say it evaluate "right side" ones, what kind of information are you (the executer) then getting, and how is the index match then performed. Is all the where clause expression marked as volatile at this level, just to be sure ? Well maybe the real question is how does the executer match an index, or am I off track ? /BL ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Index optimization ?
Martijn van Oosterhout wrote: No, it depends on your interpretation of the query. Note, I'm not up with the SQL standard so maybe it doesn't work like this, but this is what I think the problem is. I just try to learn, so that is ok :-) Tom gave me a solution that works, so now I struggle to understand why. The above query can be interpreted as: for each row in test_table, compare id against plus_random( test_col ). Now, in theory the plus_random function needs to be evaluated for every row, each time giving a different value, thus it may or may not match id. But if you take a look at a function, it has a return type. So "currval" always returns a BIGINT no matter what kind of parameters are given, that is a part of the declaration, as far as I can see. Why are this type info not used to match an index, as the type is the same no matter what row we are in, or no matter its parameter value (or context). The value change, but not the type. The type is used to find a matching index is it not ? Am I misunderstanding you ? You can see that with that interpretation an index on id doesn't help. No, I think this is the problem, I don't see :-) The function promise to return a certain type, and type can be used to find the prober index (if any). If you interpret the query so plus_random is evaluted only once, then an index will help. If test_col is a column of the table then there is no way an index can help you. If and only if the function returns a different value TYPE, otherwise it can use the same index but with different values, of the same type alias use index scan. But again, I am sure there is something I have misunderstud :-) Thanks for trying :-) /BL ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Index optimization ?
Ragnar =?ISO-8859-1?Q?Hafsta=F0?= <[EMAIL PROTECTED]> writes: > On Sun, 2005-01-16 at 17:45 +0100, Bo Lorentsen wrote: >> Why not use the index scan for every row, is this a "limit" in the >> planner ? I think there is something in the planner I don't understand :-) > the planner will just use the plan it estimates will be fastest. > because of how indexscans work in postgresql, in this case it would be > slower than a tablescan (assuming the function really is volatile) It has nothing to do with speed, it has to do with giving the correct answer. We define "correct answer" as being the result you would get from a naive interpretation of the SQL semantics --- that is, for every row in the FROM table, actually execute the WHERE clause, and return the rows where it produces TRUE. As an example, a query like SELECT * FROM mytable WHERE random() < 0.1; should produce a random sampling of about one-tenth of the rows in mytable. If we evaluated random() only once in this query, we would get either all or none of the rows, clearly not the right answer. An indexscan is a legal optimization only if the function(s) in the WHERE clause are all STABLE or better. This is because the index access code will only evaluate the righthand side of the "indexcol = something" clause once, and then will use that value to descend the btree and select matching index entries. We must be certain that this gives the same result we would get from a seqscan. The definition of STABLE that PostgreSQL uses was crafted specifically to capture the property that a function is safe to use in an indexscan qualification ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] ntfs for windows port rc5-2
Magnus Hagander wrote: rc5-2 msi will not install at all on a fat32 filesystem even without initialising the database. Really? The code for checking the filesystem type is only executed if you chose to initdb, so I really don't see this happening. Exactly what message do you get? Log in the temp install dir: The Cacls command can be run only on disk drives that use the NTFS file system I'll have to rip half or more of the full log as it seems to be to large for the list to accept -- only plain text format email accepted. smaller file size, no virus transfer no proprietary file formats. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Index optimization ?
# kleptog@svana.org / 2005-01-16 17:48:08 +0100: > On Sun, Jan 16, 2005 at 05:30:22PM +0100, Bo Lorentsen wrote: > > >One could conceivably attempt to make a functional index using > > >plus_random(), but the result it gives every time is indeterminant. > > >How would you be able to usefully search for values in an index that > > >is based on this function? Would it make sense do to do so? > > > > What you say is that PG can't see the difference between this > > "plus_random" and the "currval", right. > > > > But if I have a select (a quite strange one), like this : > > > > SELECT * FROM test_table WHERE id = plus_random( test_col ); > > > > I don't understand the problem. The function always return an integer as > > specified in the function decl. so why not use the PK index for search, > > instead of using seq scan ? The value is totally unpredictable but it is > > still an integer and the pk index is still useful regarding performance ! > > No, it depends on your interpretation of the query. Note, I'm not up > with the SQL standard so maybe it doesn't work like this, but this is > what I think the problem is. > > The above query can be interpreted as: for each row in test_table, > compare id against plus_random( test_col ). That's what happens if you declare the function VOLATILE. Make it STABLE, and the function call will be evaluated only once for the whole table scan. That's just what Tom Lane suggested in his post. -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Index optimization ?
On Sun, 2005-01-16 at 17:45 +0100, Bo Lorentsen wrote: > Ragnar Hafstað wrote: > >when a volatile function is used thus: > > SELECT * FROM mytable WHERE col=myvolatilefunc(); > >the planner must call the function once per table row, and assume > >possibly different return values each time, so an indexscan will > >not improve timings. > > > > > Why not use the index scan for every row, is this a "limit" in the > planner ? I think there is something in the planner I don't understand :-) the planner will just use the plan it estimates will be fastest. because of how indexscans work in postgresql, in this case it would be slower than a tablescan (assuming the function really is volatile) gnari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Index optimization ?
On Sun, Jan 16, 2005 at 05:30:22PM +0100, Bo Lorentsen wrote: > >One could conceivably attempt to make a functional index using > >plus_random(), but the result it gives every time is indeterminant. > >How would you be able to usefully search for values in an index that > >is based on this function? Would it make sense do to do so? > > What you say is that PG can't see the difference between this > "plus_random" and the "currval", right. > > But if I have a select (a quite strange one), like this : > > SELECT * FROM test_table WHERE id = plus_random( test_col ); > > I don't understand the problem. The function always return an integer as > specified in the function decl. so why not use the PK index for search, > instead of using seq scan ? The value is totally unpredictable but it is > still an integer and the pk index is still useful regarding performance ! No, it depends on your interpretation of the query. Note, I'm not up with the SQL standard so maybe it doesn't work like this, but this is what I think the problem is. The above query can be interpreted as: for each row in test_table, compare id against plus_random( test_col ). Now, in theory the plus_random function needs to be evaluated for every row, each time giving a different value, thus it may or may not match id. You can see that with that interpretation an index on id doesn't help. If you interpret the query so plus_random is evaluted only once, then an index will help. If test_col is a column of the table then there is no way an index can help you. Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgp6DEl6JKvoi.pgp Description: PGP signature
Re: [GENERAL] Index optimization ?
Ragnar Hafstað wrote: this has nothing to do with the return type. a volatile function is a function that is not garanteed to return the same value given same input parameters, (such as currval()). when a volatile function is used thus: SELECT * FROM mytable WHERE col=myvolatilefunc(); the planner must call the function once per table row, and assume possibly different return values each time, so an indexscan will not improve timings. Why not use the index scan for every row, is this a "limit" in the planner ? I think there is something in the planner I don't understand :-) on the other hand, if the function is labeled STABLE, the planner can assume that the same value will alway be returned, so only one call to it can be made, and an indexscan might be found the most effective. The two other function types are not interesting, but I don't understand the planners use of index optimization. /BL ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Index optimization ?
On Sun, 2005-01-16 at 16:25 +0100, Bo Lorentsen wrote: [about a volatile function in a where clause not generating index scan] > Will the only possible way to fix this be to make a volatile function > with a return type (I know this is not possible now, but in theory) ? this has nothing to do with the return type. a volatile function is a function that is not garanteed to return the same value given same input parameters, (such as currval()). when a volatile function is used thus: SELECT * FROM mytable WHERE col=myvolatilefunc(); the planner must call the function once per table row, and assume possibly different return values each time, so an indexscan will not improve timings. on the other hand, if the function is labeled STABLE, the planner can assume that the same value will alway be returned, so only one call to it can be made, and an indexscan might be found the most effective. hope this helps gnari ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Index optimization ?
Michael Glaesemann wrote: I don't believe it has necessarily anything to do with the return type, but rather the return value. An index only works if you know what the value is, and the return value for a volatile function is not guaranteed to be the same for given parameters. Here's a contrived (and untestsd) example, but one I think makes it clear: CREATE FUNCTION plus_random ( INTEGER ) RETURNS INTEGER LANGUAGE SQL AS ' SELECT round( $1 + random() * 100 ); '; One could conceivably attempt to make a functional index using plus_random(), but the result it gives every time is indeterminant. How would you be able to usefully search for values in an index that is based on this function? Would it make sense do to do so? What you say is that PG can't see the difference between this "plus_random" and the "currval", right. But if I have a select (a quite strange one), like this : SELECT * FROM test_table WHERE id = plus_random( test_col ); I don't understand the problem. The function always return an integer as specified in the function decl. so why not use the PK index for search, instead of using seq scan ? The value is totally unpredictable but it is still an integer and the pk index is still useful regarding performance ! I know there is something I don't understand, so I just have to ask :-) Does this help? (And if I'm completely off base, someone please let me know :) No this time I think missed the point, I understand the volatility of functions, so the planer know what to expect from the function, regarding side effect, but I still don't understand why this influences the choice of valid indexes. /BL ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Index optimization ?
On Jan 17, 2005, at 0:25, Bo Lorentsen wrote: Tom Lane wrote: http://developer.postgresql.org/docs/postgres/xfunc-volatility.html Ok, thanks I see why there is these three differant function types, but I don't quite understand why the value from a volatile function, can't be used as a index key. Is this because there is no return type garanti, for the voilatile function too ? I don't believe it has necessarily anything to do with the return type, but rather the return value. An index only works if you know what the value is, and the return value for a volatile function is not guaranteed to be the same for given parameters. Here's a contrived (and untestsd) example, but one I think makes it clear: CREATE FUNCTION plus_random ( INTEGER ) RETURNS INTEGER LANGUAGE SQL AS ' SELECT round( $1 + random() * 100 ); '; One could conceivably attempt to make a functional index using plus_random(), but the result it gives every time is indeterminant. How would you be able to usefully search for values in an index that is based on this function? Would it make sense do to do so? Does this help? (And if I'm completely off base, someone please let me know :) Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Index optimization ?
Tom Lane wrote: http://developer.postgresql.org/docs/postgres/xfunc-volatility.html Ok, thanks I see why there is these three differant function types, but I don't quite understand why the value from a volatile function, can't be used as a index key. Is this because there is no return type garanti, for the voilatile function too ? Will the only possible way to fix this be to make a volatile function with a return type (I know this is not possible now, but in theory) ? /BL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PostgreSQL 8 on windows very slow
On 11 Jan 2005 at 8:16, lol wrote: > I use zeoslib 6.1.5 for Delphi 7. I have also tried 6.5.1-alpha. It's > faster (especially with firebird). > I will take a look at dbExpress. I've recently started using dbExpress with Delphi 6 for a desktop client application, and have found it fast and easy to use. --Ray. - Raymond O'Donnell http://www.galwaycathedral.org/recitals [EMAIL PROTECTED] Galway Cathedral Recitals - ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL code for nested sets
I'm wondering if anyone has taken the code from http://www.dbazine.com/tropashko4.shtml and converted it to PostgreSQL? You can use the contrib/ltree type, which represents a path, and will be easier and faster to use. http://www.sai.msu.su/~megera/postgres/gist/ltree/ Create a table with : node_id SERIAL PRIMARY KEY, parent_id INTEGER NULL REFERENCES yourtable( node_id ) ON DELETE CASCADE; full_path ltree NOT NULL Create a gist index on ltree parent_id IS NULL implies the node is in the root of the tree Add an ON INSERT/UPDATE TRIGGER which will fill the full_path with the parent's full_path + the node_id Then you can use the ltree operators for very efficient querying ! Example : folder_id | parent_id | full_path | title ---+---+-+--- 1 | | 1 | root 10 | 1 | 1.10| folder 9 109 |10 | 1.10.109| sub 68 139 |10 | 1.10.139| sub 98 29 |10 | 1.10.29 | sub 8 128 |29 | 1.10.29.128 | sub 87 158 |29 | 1.10.29.158 | sub 117 68 |29 | 1.10.29.68 | sub 27 98 |29 | 1.10.29.98 | sub 57 49 |10 | 1.10.49 | sub 8 79 |10 | 1.10.79 | sub 38 11 | 1 | 1.11| folder 10 110 |11 | 1.11.110| sub 69 140 |11 | 1.11.140| sub 99 30 |11 | 1.11.30 | sub 9 129 |30 | 1.11.30.129 | sub 88 159 |30 | 1.11.30.159 | sub 118 69 |30 | 1.11.30.69 | sub 28 Getting the path to an element : select folder_id, parent_id, full_path, title from folders WHERE full_path @> '1.10.29.128'; folder_id | parent_id | full_path | title ---+---+-+-- 1 | | 1 | root 10 | 1 | 1.10| folder 9 29 |10 | 1.10.29 | sub 8 128 |29 | 1.10.29.128 | sub 87 Getting all children from a node (recursively) : select folder_id, parent_id, full_path, title from folders WHERE full_path <@ '1.10'; folder_id | parent_id | full_path | title ---+---+-+-- 10 | 1 | 1.10| folder 9 29 |10 | 1.10.29 | sub 8 49 |10 | 1.10.49 | sub 8 68 |29 | 1.10.29.68 | sub 27 79 |10 | 1.10.79 | sub 38 98 |29 | 1.10.29.98 | sub 57 109 |10 | 1.10.109| sub 68 128 |29 | 1.10.29.128 | sub 87 139 |10 | 1.10.139| sub 98 158 |29 | 1.10.29.158 | sub 117 Isn't it nice ? Thanks to the gist/ltree team ! This contrib is great. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PostgreSQL and WebObjects
On Sun, 16 Jan 2005 01:13:58 -0500 Ken Tozier <[EMAIL PROTECTED]> wrote: > Anybody using PostgreSQL with WebObjects on OS X 10.3.x? I'm having > trouble setting it up and could use some pointers from anyone who has > successfully gotten the two working together. I've done this a numerous times. You need a PostgreSQL-Plugin from Hexdreams (http://www.hexdreams.com/) or ProjectWONDER (http://wonder.sourceforge.net/) and a recent JDBC-Driver (jdbc.postgresql.org). Put the JDBC driver into the Java-Extensions directory, compile and install the plugins ... and it should work. If there are more questions, I would suggest using one of the dedicated WO mailing lists at omnigroup or apple (where you might find some hints in the archives, too). Ralf ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Is it possible to measure IO costs of a query in
10x, for the reply. I did read all the developer documentation and it got me close but not close enough. It turns out that on linux the io statistics per processes is not counted at all. There is however what is called laptop_mode where you want to find unecessary disk activity to save batteries. In order to activate the io stats per process you do echo "1">/proc/sys/vm/block_dump and watch the dmesg. Now, it's a big mess there so as soon as I have some time I plan to remove the messages from dmesg with some switch and instead count statistics into /proc//statio. naturally I want to count the postgres process which runs my query. I also need to disable the bgwriter in the conf files and probably the other stat which I won't need. btw, when I disabled the bgwriter on windows, I am not sure but I think its not completely disabled since I still see io writes in a different postgres process (which I must assume is the bgwriter since they don't have clear names In the task list in windows). Anyway, in linux (and probably in windows) there is also a background writer of the system. In linux 2.4 its called kupdated and it write out dirtied buffers it has read from the disk. I will also need to circumvent it somehow ( I don't know if it can be simply disabled) with maybe O_SYNC or/and O_DIRECT in the fd.c source of postgreSQL where they do fileWrite and fileRead. Also, when you think about it, it is useless for the kernel to second guess postgreSQL since the developers know what they want to achieve when they cache disk blocks. Maybe I should also search for a way to tell kupdated to stop caching a given list of processes. Regards, tzahi. > -Original Message- > From: Michael Fuhr [mailto:[EMAIL PROTECTED] > Sent: Sunday, January 16, 2005 5:44 AM > To: Tzahi Fadida > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Is it possible to measure IO costs of > a query in postgreSQL? > > > On Sat, Jan 08, 2005 at 08:01:51PM +0200, Tzahi Fadida wrote: > > > I have been trying for a week now without success to > discover if you > > can measure the cost of a query (with my c function). > EXPLAIN ANALYZE > > seems to give you the actual time it took it to run but the "cost" > > seems to be a fixed estimate number and not actual. > > "cost" is the planner's estimate of disk page fetches; > "actual" is elapsed real time. See "Using EXPLAIN" in the > "Performance Tips" chapter of the documentation. > > > I see in the code many times references to pgstats so > > i figure there must be some kind of accounting. > > Does anyone have a clue on this? its very important to me > > to be able to measure scientifically the costs of a query. > > Have you looked at the "The Statistics Collector" section in > the "Monitoring Database Activity" chapter? The statistics > views won't show statistics for a particular query, but if > the database is quiescent except for your activity then you > might be able to measure queries' I/O costs by observing > changes in the gathered statistics. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
