[HACKERS] why I need col. def. list with setof record?
Hello I know so I need to use column definition list when I have SRF function without def of own type. But I don't understand why. In my SRF function I specify all informations about columns? TupleDescInitEntry (tupdesc, 1, idn, INT4OID, -1, 0, false); TupleDescInitEntry (tupdesc, 2, idv, VARCHAROID, -1, 0, false); Why I have to duplicate to? Thank You Pavel Stehule ---(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: [HACKERS] Error with returning SETOF Record
On Sat, 27 Dec 2003, A E wrote: Hi, I was wondering if a solution was ever found to the error: wrong record type supplied in RETURN NEXT when executing a function that returns the Record datatype? I have seen a couple of previous post from Tom Lane and others, but no real resolution. I am attempting to execute this code and I get that error: code CREATE FUNCTION tester(varchar) RETURNS SETOF record as' DECLARE ft record; begin FOR ft IN SELECT * FROM visaapplicants LOOP RETURN NEXT ft; END LOOP; return null; You have to write only return! end;' LANGUAGE 'plpgsql' VOLATILE; Regards Pavel ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Nested Transactions, Abort All
An 'END SUB' after a 'BEGIN SUB' in plpgsql could be required, and could mean start/end block and subtx. I do not really see a downside. But, it would imho only make sense if the 'END SUB' would commit sub or abort sub iff subtx is in aborted state (see my prev posting) Andreas Hello, is good idea use keywords begin sub and end sub? Programmers like me will be an problems with reading and writing SP, because begin sub and mostly end sub are keywords from visual basic with different sense. BEGIN SUBTRANSACTION and END SUBTRANSACTION is longer but more readable regards Pavel Stehule ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] too short string for SQL in pg_stat_backend_activity
Hello I my work I have problem with some ugly SQL. Processing some query takes 2 days and more (problem is in queries not in PostgreSQL). I can't wait on end of query and can't take text of query from serverlog. Text of queries has about 2 - 7 Kb. I have problem identifi queries in our is, becouse I can see only begin of queries in pg_stat_activity, and in this view is text of query cut on 1Kb. Can I set somewhere an long for text, which is in current_query in pg_catalog.pg_stat_activity? Regards Pavel Stehule ---(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: [HACKERS] PROPOSAL - User's exception in PL/pgSQL
On Tue, 21 Jun 2005, Tom Lane wrote: Pavel Stehule [EMAIL PROTECTED] writes: I wont to prohibit synonyms in exception (every exception has unique sqlstate). I don't think that's a particularly good idea --- maybe if SQL had been designed according to your worldview, it'd be like that, but it isn't and you can't retroactively force it to be. The SQLSTATEs are deliberately designed to be fairly coarse, not unique. I believe the design intention is to distinguish between two cases when it's likely that client application code would do something different in the two cases. Not to be unique for uniqueness' sake. it's can be source of bugs. For me, uniqueness sqlstates is 20 lines more. Ok. I will send patch without unique states. Pavel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pl/pgsql: END verbosity
On Tue, 21 Jun 2005, Andrew Dunstan wrote: Neil Conway said: In PL/PgSQL, END LOOP is used to terminate loop blocks, and END IF is used to terminate IF blocks. This is needlessly verbose: we could simply accept END in both cases without syntactic ambiguity. I'd like to make this change, so that END can be used to terminate any kind of block. There's no need to remove support for the present syntax, of course, so there's no backward compatibility concern. Oracle's PL/SQL does require END IF and END LOOP, but folks interested in maximum compatibility can always use those forms if they like. Hello, I prefer actual syntax too, Neil. The reason isn't compatibility with Oracle, but better readibility - it's mean more work with finishing code but less with debugging Regards Pavel ---(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
[HACKERS] pl/pgsql: END verbosity [patch]
Hello
this patch allows optional using label with END and END LOOP. Ending label
has only informational value, but can enhance readability large block and
enhance likeness with Oracle.
mainLOOP
...
...
END LOOPmain;
Regards
Pavel Stehule
diff -c -r --new-file pgsql/doc/src/sgml/plpgsql.sgml
pgsql.01/doc/src/sgml/plpgsql.sgml
*** pgsql/doc/src/sgml/plpgsql.sgml 2005-06-24 13:10:33.0 +0200
--- pgsql.01/doc/src/sgml/plpgsql.sgml 2005-06-25 15:29:27.0 +0200
***
*** 456,462
replaceabledeclarations/replaceable /optional
BEGIN
replaceablestatements/replaceable
! END;
/synopsis
/para
--- 456,462
replaceabledeclarations/replaceable /optional
BEGIN
replaceablestatements/replaceable
! END optional lt;lt;replaceablelabel/replaceablegt;gt; /optional;
/synopsis
/para
***
*** 1792,1798
optionallt;lt;replaceablelabel/replaceablegt;gt;/optional
LOOP
replaceablestatements/replaceable
! END LOOP;
/synopsis
para
--- 1792,1798
optionallt;lt;replaceablelabel/replaceablegt;gt;/optional
LOOP
replaceablestatements/replaceable
! END LOOP
optionallt;lt;replaceablelabel/replaceablegt;gt;/optional;
/synopsis
para
***
*** 1923,1929
optionallt;lt;replaceablelabel/replaceablegt;gt;/optional
WHILE replaceableexpression/replaceable LOOP
replaceablestatements/replaceable
! END LOOP;
/synopsis
para
--- 1923,1929
optionallt;lt;replaceablelabel/replaceablegt;gt;/optional
WHILE replaceableexpression/replaceable LOOP
replaceablestatements/replaceable
! END LOOP
optionallt;lt;replaceablelabel/replaceablegt;gt;/optional;
/synopsis
para
***
*** 2000,2006
optionallt;lt;replaceablelabel/replaceablegt;gt;/optional
FOR replaceablerecord_or_row/replaceable IN
replaceablequery/replaceable LOOP
replaceablestatements/replaceable
! END LOOP;
/synopsis
The record or row variable is successively assigned each row
resulting from the replaceablequery/replaceable (which must be a
--- 2000,2006
optionallt;lt;replaceablelabel/replaceablegt;gt;/optional
FOR replaceablerecord_or_row/replaceable IN
replaceablequery/replaceable LOOP
replaceablestatements/replaceable
! END LOOP
optionallt;lt;replaceablelabel/replaceablegt;gt;/optional;
/synopsis
The record or row variable is successively assigned each row
resulting from the replaceablequery/replaceable (which must be a
***
*** 2039,2045
optionallt;lt;replaceablelabel/replaceablegt;gt;/optional
FOR replaceablerecord_or_row/replaceable IN EXECUTE
replaceabletext_expression/replaceable LOOP
replaceablestatements/replaceable
! END LOOP;
/synopsis
This is like the previous form, except that the source
commandSELECT/command statement is specified as a string
--- 2039,2045
optionallt;lt;replaceablelabel/replaceablegt;gt;/optional
FOR replaceablerecord_or_row/replaceable IN EXECUTE
replaceabletext_expression/replaceable LOOP
replaceablestatements/replaceable
! END LOOP
optionallt;lt;replaceablelabel/replaceablegt;gt;/optional;
/synopsis
This is like the previous form, except that the source
commandSELECT/command statement is specified as a string
diff -c -r --new-file pgsql/src/pl/plpgsql/src/gram.y
pgsql.01/src/pl/plpgsql/src/gram.y
*** pgsql/src/pl/plpgsql/src/gram.y 2005-06-24 13:11:25.0 +0200
--- pgsql.01/src/pl/plpgsql/src/gram.y 2005-06-25 15:21:22.0 +0200
***
*** 56,61
--- 56,62
PLpgSQL_datum *initial_datum);
staticvoid check_sql_expr(const char *stmt);
staticvoid plpgsql_sql_error_callback(void *arg);
+ static void check_labels(char *lbl, char *elbl, int lno);
%}
***
*** 81,86
--- 82,93
int n_initvars;
int *initvarnos;
} declhdr;
+ struct
+ {
+ char *label;
+ int lineno;
+ List *list;
+ } loop_body;
List*list;
PLpgSQL_type*dtype;
PLpgSQL_datum *scalar;/* a VAR,
RECFIELD, or TRIGARG */
***
*** 122,129
%type str opt_lblname opt_label
%type str opt_exitlabel
%type str execsql_start
! %type list proc_sect proc_stmts stmt_else loop_body
%type stmt proc_stmt pl_block
%type stmt stmt_assign stmt_if stmt_loop stmt_while stmt_exit
%type stmt stmt_return
Re: [HACKERS] pl/pgsql: END verbosity
On Mon, 27 Jun 2005, Neil Conway wrote: Peter Eisentraut wrote: It is required by the SQL standard. No, it isn't -- PL/PgSQL is not defined by the SQL standard. I guess you're referring to SQL/PSM, but that has only a passing resemblance to PL/PgSQL. Implementing SQL/PSM in some form would definitely be worth doing (especially now that MySQL have), but I haven't seen any plans to do that by adapting PL/PgSQL to SQL/PSM. PL/pgSQL is different language than SQL/PSM and is little bit nonsenc adapting them to SQL/PSM. PL/SQL live still - Oracle did some enhancing, and we can do it too. Some parts both languages are similar and some enough different. I had plan start develop new interpret for SQL/PSM two years ago, but I hadn't knowleages at that time. Situation is different now. If anybody wont to work on SQL/PSM I will go too. Solution is another pl - pl/psm. We can adapt gram.y plpgsql to psm Regards Pavel Stehule ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Implementing SQL/PSM for PG 8.2
On Sun, 26 Jun 2005, Tom Lane wrote: Denis Lussier [EMAIL PROTECTED] writes: For various technical and backward compatibility reasons, I don't think SQL/PSM should be a replacement for PL/pgSQL. Although I do think it should heavily leverage the solid foundation afforded by the PL/pgSQL code base. Solid? I've wanted for quite some time to throw away plpgsql and start over --- there are too many things that need rewritten in it, starting with the parser. This project would be a great place to do that. What is wrong on plpgsql code? I see some problems with processing SQL statements, with efectivity evaluation of expr, but parser is clean (in my opinion). what have to be rewriten? Regards Pavel Stehule ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Implementing SQL/PSM for PG 8.2 - debugger
On Tue, 28 Jun 2005, Dave Cramer wrote:
One thing bytecode would allow us to do is to write a debugger with
break points etc.
We can write debugger with breakpoints without bytecode. Every stmt rec
can have flag if has breakpoints. No problem. I don't see any advance of
bytecode. Maybe, goto stmt is possible.
What is problem? We need synchronous comunication (message) between
backend frontend.
I have idea (in exec_stmt()
CHECK_FOR_INTERRUPTS();
if (stmt-breakpoints)
estate-debug_mode = true;
if (estate-debug_mode)
{
for (;;)
{
rc = request_command();
switch (rc)
{
case 'c': -- continue
estate-debug_mode = false;
break
case 'q':
elog(EXCEPTION, stop debug);
break;
case 'n':
break;
case 'l':
sendstring(line(estate-src,
stmt-lineno));
Please, can somebody help me with protocol enhancing? It is mayor work on
PL/pgSQL debugger (and plperl and plpython too).
Using a java jvm however is considerable overkill.
Dave
On 27-Jun-05, at 8:28 PM, Neil Conway wrote:
Jonah H. Harris wrote:
I don't recommend discussion for this in this thread, but it could
also tie in with the packages support we've discussed and
(although some may argue this), compiling the PL to bytecode and
using that.
How would compilation to bytecode help?
-Neil
---(end of
broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Implementing SQL/PSM for PG 8.2 - debugger
What do you think you need for enhanced protocol ? What I need? Some like synchronous elog(NOTICE,''), which can return some user's interaction, if it's possible. I didn't find how I do it with current set of messages. But my knowleadges of protocol are minimal. Pavel ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Implementing SQL/PSM for PG 8.2 - debugger
On Tue, 28 Jun 2005, Tom Lane wrote: Pavel Stehule [EMAIL PROTECTED] writes: What do you think you need for enhanced protocol ? What I need? Some like synchronous elog(NOTICE,''), which can return some user's interaction, if it's possible. I didn't find how I do it with current set of messages. But my knowleadges of protocol are minimal. It'd probably be smarter to manage the debugging across a separate connection, so that you could carry out debugging without requiring sophisticated support for it inside the client program. If it's single-connection then it will be essentially impractical to debug except from a few specialized clients such as pgadmin; which will make it hard to investigate behaviors that are only seen under load from a client app. I don't think it. Debug process halt query process in bouth variants - remote | protocol. Remote debugging has one advance. I can monitor any living plpgsql process, but I have to connect to some special port, and it can be problem. Protocol debugging can be supported libpq, and all clients libpq can debug. But is problem if PostgreSQL support bouth variants? btw: debuging have to be only for some users, GRANT DEBUG ON LANGUAGE plpgsql TO .. For me, is better variant if I can debug plpgsql code in psql console. Without spec application. I don't speak so spec application don't have to exists (from my view, ofcourse). Maybe: set debug_mode to true; -- if 't' then func stmt has src reset function myfce(integer, integer); -- need recompilation create breakpoint on myfce(integer, integer) line 1; select myfce(10,10); dbg \l .. list current line \c .. continue \n .. next stmt \L .. show src \s .. show stack \b .. switch breakpoint \q .. quit function select myvar+10 .. any sql expression variable .. print variable \c myfce - 10 that's all. Maybe I have big fantasy :). Regards Pavel + small argument: if psql support debug mode, I don't need leave my emacs postgresql mode. I don't know exactly how to cause such a connection to get set up, especially remotely. But we should try to think of a way. 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: [HACKERS] Implementing SQL/PSM for PG 8.2 - debugger
On Tue, 28 Jun 2005, Dave Cramer wrote: Pavel, I am in agreement with Tom here, we should use a separate port, and protocol specifically designed for this. My understanding is that this protocol would be synchronous, and be used for transferring state information, variables, etc back and forth whereas the existing protocol would still be used to transfer data back and forth We can it. It can be good start point. I can do it alone. It simpler. But I don't think so this is optimal solution. You need two protocols. Maybe I don't understand, but I think so changes in protocol3 files will be minimal. I wont to do prototype. Pavel Dave On 28-Jun-05, at 10:36 AM, Pavel Stehule wrote: On Tue, 28 Jun 2005, Tom Lane wrote: Pavel Stehule [EMAIL PROTECTED] writes: What do you think you need for enhanced protocol ? What I need? Some like synchronous elog(NOTICE,''), which can return some user's interaction, if it's possible. I didn't find how I do it with current set of messages. But my knowleadges of protocol are minimal. It'd probably be smarter to manage the debugging across a separate connection, so that you could carry out debugging without requiring sophisticated support for it inside the client program. If it's single-connection then it will be essentially impractical to debug except from a few specialized clients such as pgadmin; which will make it hard to investigate behaviors that are only seen under load from a client app. I don't think it. Debug process halt query process in bouth variants - remote | protocol. Remote debugging has one advance. I can monitor any living plpgsql process, but I have to connect to some special port, and it can be problem. Protocol debugging can be supported libpq, and all clients libpq can debug. But is problem if PostgreSQL support bouth variants? btw: debuging have to be only for some users, GRANT DEBUG ON LANGUAGE plpgsql TO .. For me, is better variant if I can debug plpgsql code in psql console. Without spec application. I don't speak so spec application don't have to exists (from my view, ofcourse). Maybe: set debug_mode to true; -- if 't' then func stmt has src reset function myfce(integer, integer); -- need recompilation create breakpoint on myfce(integer, integer) line 1; select myfce(10,10); dbg \l .. list current line \c .. continue \n .. next stmt \L .. show src \s .. show stack \b .. switch breakpoint \q .. quit function select myvar+10 .. any sql expression variable .. print variable \c myfce - 10 that's all. Maybe I have big fantasy :). Regards Pavel + small argument: if psql support debug mode, I don't need leave my emacs postgresql mode. I don't know exactly how to cause such a connection to get set up, especially remotely. But we should try to think of a way. 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]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Implementing SQL/PSM for PG 8.2 - debugger
I lean with you and Tom. While running it over the same libpq protocol would be helpful in some ways, it would have a lot of drawbacks and would really change the function of libpq. I think a separate debugging protocol is in order. One message? I can't belive :). work on it (ANTLR currently generates Python, Java, or C++). I don't suggest we really reuse one of the current VMs as it would require a lot more support and coordination. Let's take the bytecode discussion off this thread and move it to another. There is certainly a good and bad side to using bytecode and I would be glad to discuss it in another thread. I see only one advantage of WM - sharing between languages. But SQL/PSM or PL/pgSQL are not clasic languages. Big advantage is big disadvantage too - relation on SQL engine. I can use all SQL types, but I can't to do efective concation of strings. Sorry, I don't see any benefit of bytecode for these languages. PL/pgSQL works fine (for specific task). What can be better? o evaluation of expressions. -- needs integration with sql parser o debugging o persistent compiled code o syntax Please, write me, private, your opinions. And don't scowl at me, so I am in oportunity :). Regards Pavek ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Implementing SQL/PSM for PG 8.2 - debugger
There's going to be a painful period later this year when Mysqueel is able to claim that their production db has more ansi compatability than PG (at least for triggers and stored procs). MySQL5 is really comparable with Pg8, but Firebird2 or SQLlite3 too. But from my perspective procedural language isn't essentials. Possiblity run perl or python prucedures is important. Today is first day of discussion and there is half of year space for developing. It'll be very kewl having native PG with a fully ansi-iso compliant stored procedure language with an efficient and clean implementation with great performance charateristics and a debugger to boot... Who not? ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Proposal: associative arrays for plpgsql (concept)
Hello
The concept is from Oracle 9i, but with some changes.
http://www.oracle-10g.de/oracle_10g_documentation/appdev.101/b10807/05_colls.htm#i35672
Associative arrays are any arrays with index. Will be created
DECLARE
x varchar[] INDEX BY VARCHAR = '{}'; -- some format, haven't idea
then I can use anywhere x[key];
two enhancing FOR cycle:
-- iteration over all values
FOR i IN VALUES OF x LOOP -- x array or associative array
END LOOP;
-- iteration over all keys
FOR i IN INDICIES OF x LOOP -- x associatice array
x[i]
END LOOP;
new functions:
exists(x, key);
delete(x, key);
index is accessable only from PL/pgSQL. Associative arrays can be spec
PostgreSQL type or clasic arrays with hash index.
Comments, notes?
Regards
Pavel Stehule
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[HACKERS] problem with plpgsql
Hello
In my code I evaluate expr
select array(select generate_series from generate_series(1,800)
my code
var = (PLpgSQL_var *) (estate-datums[stmt-varno]);
value = exec_eval_expr(estate, stmt-expr, isnull, valtype);
exec_eval_cleanup(estate);
and iteration over array
ndim = ARR_NDIM(value);
dims = ARR_DIMS(value);
nitems = ArrayGetNItems(ndim, dims);
element_type = ARR_ELEMTYPE(value);
p = ARR_DATA_PTR(value);
get_typlenbyvalalign(element_type, typlen, typbyval, typalign);
for (i = 0; i nitems; i++) // tak aby to zvladalo dimenze
{
Datum itemvalue;
itemvalue = fetch_att(p, typbyval, typlen);
exec_assign_value(estate, (PLpgSQL_datum *) var,
itemvalue, element_type, isnull);
p = att_addlength(p, typlen, PointerGetDatum(p));
p = (char *) att_align(p, typalign);
works fine, but from random index 300 array is broken
NOTICE: 400
NOTICE: 401
NOTICE: 402
NOTICE: 403
NOTICE: 404
NOTICE: 405
NOTICE: 406
NOTICE: 407
NOTICE: 408
NOTICE: 409
NOTICE: 410
NOTICE: 411
NOTICE: 412
NOTICE: 413
NOTICE: 414
NOTICE: 415
NOTICE: 157207208
NOTICE: 16
NOTICE: 3486004
NOTICE: 419
NOTICE: 420
NOTICE: 421
NOTICE: 157207208
NOTICE: 16
Can you help me, what I do wrong?
Thank You
Pavel Stehule
---(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: [HACKERS] Proposal: associative arrays for plpgsql (concept)
On Wed, 29 Jun 2005, Josh Berkus wrote: Pavel, The concept is from Oracle 9i, but with some changes. http://www.oracle-10g.de/oracle_10g_documentation/appdev.101/b10807/05_coll s.htm#i35672 How does this match the SQL2003 spec? I don't know. What I can read about it, it's only PL/SQL feature and maybe reason for PL/pgSQL. I like and need a) hash arrays b) iteration over all items of array All I can use well in my codes. Pavel ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Proposal: associative arrays for plpgsql (concept)
On Wed, 29 Jun 2005, Douglas McNaught wrote:
David Fetter [EMAIL PROTECTED] writes:
I'm all in favor of having associative arrays as a 1st-class data type
in PostgreSQL. How much harder would it be to make these generally
available vs. tied to one particular language?
We already have them--they're called tables with primary keys. :)
What's the use-case for these things? Just imitating Oracle?
-Doug
no
for example
DECLARE _d varchar[] INDEX BY VARCHAR = {'cmd1' = '723:t:f:1', 'cmd2'=..
BEGIN
FOR r IN SELECT * FROM data LOOP
check_params(_r, _d[_r.cmd])
END LOOP;
or without assoc. arrays
DECLARE _d varchar;
BEGIN
FOR r IN SELECT * FROM data LOOP
SELECT INTO par _d WHERE cmd = _r.cmd;
check_params(_r, _d)
END LOOP;
I can't to speak about speed without tests but I can expect so hash array
can be much faster. This sample is easy, but I can have procedure witch
operate over big arrays of numbers(prices) and I need save somewhere this
arrays if I don't wont to read them again and again. And if I have in
data identification by key, I everytime have to find key, and translate it
into number
Regards
Pavel Stehule
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Qustions about timestampz
Hello
I am working on trunc and round function now. I have some problems with
timestemp with time zone datatype. First question?
is correct behaviour (I am in GMT+2 time zone)?
select current_timestamp, date_trunc('year', current_timestamp),
date_trunc('month', current_timestamp);
timestamptz|date_trunc|date_trunc
2005-07-31 10:46:39.087+02|2005-01-01 00:00:00+01|2005-07-01
00:00:00+02
I expected for all values time zone + 2.
Next questions. Is correct idea?
trunc(timestamptz '20010101 10:10:10+8', 'IYYY') -- 20010102 00:00:00+8 ??
or 20010101 18:00:00+02 (24-6) or 20010101 19:00:00+01 or ???
There are somewhere some rules about behavior timestamp with time zone?
Thenk You
Pavel Stehule
_
Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com.
http://www.msn.cz/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[HACKERS] when started century? PostgreSQL vs Oracle diff
Hello I am testing comformity between PostgreSQL and Oracle. I found one difference. Century started 1900-01-01 for Oracle and 1901-01-01 for PostgreSQL. What value is good? Best regards Pavel Stehule _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] SQL/XML public functions documentation for PostgreSQL 8.2
Hello, I did patch http://archives.postgresql.org/pgsql-patches/2005-09/msg00050.php SQL/XML public functions. Can somebody help me with documentation? Or can somebody write doc.. Regards Pavel Stehule _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] current_user versus current_role
hello I used info from current_user for log. about some operations (who, when, ..). What I can see, current_user is equal current_role function. I had problem with it, because user (if is member of any group role) can change his identity. example: peter is member of role users. But peter can do set role to users. From this moment I lost possibility of get identity if user, because current_user returns users and not peter. I can check it (if some user changed identity) ~ by exampl. test if role has login privilegia, and if not I can stop any operations. But Is there some possibility get identity of user without impact of change of role? (default role?) thank you Pavel Stehule _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] current_user versus current_role SOLVED
Hi, I used info from current_user for log. about some operations (who, when, ..). What I can see, current_user is equal current_role function. I had problem with it, because user (if is member of any group role) can change his identity. example: peter is member of role users. But peter can do set role to users. From this moment I lost possibility of get identity if user, because current_user returns users and not peter. I can check it (if some user changed identity) ~ by exampl. test if role has login privilegia, and if not I can stop any operations. I believe you can use session_user for this. -- it's exactly it what I want. Thank You Pavel Stehule _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] some interes. doc about job scheduling
Hello I found relative good thesis about job scheduling http://hristov.com/master_thesis_final.pdf it's implementation j.s. for mysql5. It's can be usefull for somebody best regards Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] I can't get row type from tuple (SPI)
Hello I execute select anyrowfce(..) in plpgsql via exec_run_select I need to get inner row, but I can't find good way for it retval = SPI_getbinval(estate-eval_tuptable-vals[0], estate-eval_tuptable-tupdesc,1); rettype = SPI_gettypeid(estate-eval_tuptable-tupdesc,1); rettupdesc = lookup_rowtype_tupdesc(rettype,0); rettupdesc is ok, but when I try SPI_getbinval(retval, rettupdesc, ...) I kill backend. What I do wrong? What is optimal process to get first field in row, when this first field is row too. for example I need to way for value 1 in select select row(row(1,2,3)); = ((1,2,3)) Can I get inner tupdesc without lookup_rowtype_tupdesc? Thank you very much Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Supporting NULL elements in arrays
Hello,
it's great news. My personal opinion about formating NULL values
'{a,,b} -- wrong, means empty string, isn't null
'{a,,b} ' -- ok, maybe not unique,
'{a, NULL, b}' -- longer, clean NULL is NULL
'{a, NULL, b}' -- NULL is not null varchar 'NULL'
Flags for array? Maybe bit isHash? So, hash array can be in line
array-null array-hash array
or flag for nor regular array (sparse array), not type unique arrays (array
can contains different types)
Regards
Pavel Stehule
_
Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com.
http://www.msn.cz/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Function with Variable number of parameters
Are variable number of parameters supported in the new version of Postgres? Hello no, there isn't way for it without modification of parser. Or you can write somethink like create function a(init, int, int, int) .. create function a(int, int, int) returns .. return a($1,$2,$3, null) Regards Pavel Stehule _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 1: 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
[HACKERS] request for enhancement of protocol
Hello Meybe is time for some changes. Maybe. I haven't courage for it. But maybe is good time for discussion. What I miss in protocol? 1. debug. support + other level for elog. Current elog is too heavy (sometimes) 2. multi result sets. This is necessery for support procedures in DB2, MySQL, ANSI, MsSQL style. 3. session (package) variables and calling procedures with OUT, INOUT in normal style, tj. stmt CALL. - heavy task, because I can write function a(IN int, IN int), and a(OUT int, OUT int) now. This is problem, and need restriction. 4. ping What is my motivation for 2? 1. I can write solution - stored application. Example: info about growing of database. Output is n tables: first table is info about database, others about top n - 1 tables, ... 2. easy reporting. I haven't possibility write stored procedure for generating cross table now. I have to do all in two steps (example): generate view, select from view. This is difference between procedures and functions. Function have to have exactly defined interface. Procedures can't. 3. easy porting from databases which support this style. sorry for my wrong english. best regards Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] request for enhancement of protocol
What do you mean? There are already 10 levels for elog, including five
levels of DEBUG. How many more do you want?
sometimes I need show only some text. Now I get stack info.
lighter elog ~ sending text, not. proc, stack info.
2. multi result sets. This is necessery for support procedures in DB2,
MySQL, ANSI, MsSQL style.
The protocol already supports this and libpq does also. However, I
think that unless you are using async mode you may have difficulty
retrieving it. There's also a comment there about whether the backend
can actually do it, so maybe some work need to be done there.
libpq is black box for me :-(. I need support in psql and plpgsql. And not
in
async mode, or I need wraper over async mode:
multih = execute_multi('call somestoredproc');
while not (rec = fetch_rs(multih))
{
...
}
3. session (package) variables and calling procedures with OUT, INOUT in
normal style, tj. stmt CALL. - heavy task, because I can write function
a(IN int, IN int), and a(OUT int, OUT int) now. This is problem, and
need
restriction.
I can understand the CALL but what's the confusing between the two
functions a? One is a(1,2), the other is a().
when I can use variables (in plpgsql now, in sql in future - package
variables) I have to remember form of function. I can't to call a(@x1, @x2).
Why. Caller don't know if I mean variant one or variant two. And I have to
use nonstandard convension select into a(). Nonstandard in separation in and
out variables. I prefere some restriction here.
4. ping
You mean, a ping command without requiring a login?
yes
What is my motivation for 2?
1. I can write solution - stored application. Example: info about
growing of database. Output is n tables: first table is info about
database, others about top n - 1 tables, ..
So you mean a function that can return anything (and hence cannot be
used in normal queries). And thus define a special interface for it
(CALL). Still, SELECT function() would work just as well, no?
SELECT works well if I expect scalar value. But if I expect table I have to
use diff. form
SELECT * FROM ... I see two modes of calling a) select - typed result, b)
call - untyped result. For point a I have different requirements than for
point b. And I see difference between statement call (clauses where, from,
.) and statement call. PostgreSQL don't support procedures now, only
functions.
2. easy reporting. I haven't possibility write stored procedure for
generating cross table now. I have to do all in two steps (example):
generate view, select from view.
Why do you need a view, why can't you use a subquery?
if you have to solve creating cross table for normal interactive using in
console, you have two possibilities: 1. call stored procedure which generate
temp wiew and user will do select from view, or procedure can create cursor
and user will do select from cursor. But you can't do in one procedure now.
This is difference between procedures and functions. Function have to
have exactly defined interface. Procedures can't.
So essentially, procedures here are functions that return unknown
rather than functions that return nothing?
yes. This is reason why procedures can't to use in select statement
3. easy porting from databases which support this style.
Ok, valid point.
Interesting points all, but they seem to be more backend related than
protocol related.
I spent some time for looking way for implementing this into plpgsql. I
didn't find it. It's part of SPI too.
Pavel
_
Express yourself instantly with MSN Messenger! Download today - it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Returning multiple result sets
Consider: create function a(anyrecord) returns anyrecord; create function b(int4) returns anyrecord; select a(b(2)); for my task I need little different form :-( create function a(..) returns setof tables but SQL2003 needs type table, and this can be solution _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Returning multiple result sets
for my task I need little different form :-( create function a(..) returns setof tables but SQL2003 needs type table, and this can be solution You want a function return entire tables at a time? Why bother when you can just return rows and signal when the next table starts? what is difference between rows with different structures and tables? Tables are more logic. But I unlike function which returns setof tables. This need data type table. I prefere normal clasic solution. -- stored proc -- | --- client -- function - scalar, vector, table procedure - OUT params - every free select table - I don't have imagine how I can write readable code with your proposal variants one: create function aaa returns setof anyrecord begin for each a in select * from temptab1 return next a; end loop; return next 'next table'; for each a in select * from temptab2 return next a; end loop; return next 'ok'; return; end; variants two: create procedure aaa(OUT allok bool) begin select * from temptab1; select * from temptab2; a := true; end; I don't have better words :-). I am sorry. I don't wont to complicate internal structure of planer, executor, etc ... Procedures are different than functions, and can be executed different, Isn't possible using procedure in params list. Nice day Pavel _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Returning multiple result sets
Hello I thinking about solution based on setof cursors. This solustion has three big minus: 1. I can unpack cursors after finish of called procedure. If I get exception, or long query, I can show nothing. 2. Old clients don't understand and don't unpack cursor. Statement call is (+/-) == statement SELECT (more if call return only one table). 3. twice communication. backend client == - call --- cursors -- select cursor table this isn't pretty solution. May be with minimal changes in code. I think, this need bigger changes and support next class of stored objects. best regards Pavel Stehule _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] MS SQL Server compatibility functions
Hello DB2, MySQL and MsSQL has shared group of function (date, time, strings). You can do it a bit complex - use variable which direct behavior, but there isn't bigger differences, I hope. Please (for start), use mycode, orafunc from pgfoundry. Regards Pavel Stehule From: Christopher Kings-Lynne [EMAIL PROTECTED] To: Fredrik Olsson [EMAIL PROTECTED] CC: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] MS SQL Server compatibility functions Date: Thu, 24 Nov 2005 09:24:06 +0800 I just started a MySQL compatibility functions project on pgfoundry.org. I suggest starting an MSSQL one as well. I'd be interested if you could mail me your code for your functions so far because many of the MySQL functions are copied from MSSQL... Chris Fredrik Olsson wrote: Hi. In the course of porting a database from Microsoft SQL Server to PostgreSQL I have rewritten a few of the date and string functions in pl/pgSQL. Started with just datepart, datediff and soundex, but once started I continued and rewrote all date/time and string functions supported by MS SQL 2005. Leaving only compatibility with unicode-handling and binary objects (that MS SQL Server for some reason overloads string functions to work with). I guess I am not the only one moving from MS SQL Server, so is there interest for others to use my work, as a contrib perhaps. And how should I continue from here in that case? regards ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] NVL vs COALESCE
When we're having an alias discussion, I'd really like to see NVL in postgres. Not because of porting from oracle as much as just spelling that without the reference manual is completely impossible. Best regards, Marcus You can found NVL in orafunc on pgfoundry. Regards Pavel Stehule _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Delphi+pqsql
On Sat, 8 Jan 2005, Ales Pavel wrote: I want make some components for pqsql + Delphi, exists some PQ API ? Thanks Aldik Czech Republic Hello yes, comercial http://www.vitavoom.com/Products/ dbexpress driver, clasic ODBC or postgresdac http://www.sharewareconnection.com/postgresdac.htm Regards Pavel Stehule ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Slow PL/pgSQL 8.0.RC5 (7.4.6. 3times faster)
Hello, I have small piece of code for testing speed of stored procedures. First time used its for compare mysql5 and postgresql. One year ago (feb.2004) was PostgreSQL much faster than mysql5. Now I was surprised. Mysql was faster. Mysql's developers did some work, but plpgsql is slowly than before :-(. I used Linux, 2xP160, 256MB, with zero loading PostgreSQL 7.4.6 needed 8896 ms PostgreSQL 8.0.0. rc5 needed 24009 ms I know so used SP is not clasic, only arithmetic operations, no sql code, but maybe it can signalize some problems.. CREATE OR REPLACE FUNCTION delitel(int, int) RETURNS int AS ' DECLARE a integer; b integer; BEGIN a := $1; b := $2; WHILE a b LOOP IF a b THEN a := a - b; ELSE b := b - a; END IF; END LOOP; RETURN a; END; ' LANGUAGE plpgsql; Regards Pavel Stehule ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Slow PL/pgSQL 8.0.RC5 (7.4.6. 3times faster)
And? (ie, what test case are you talking about?) regards, tom lane This test is function for searching max factor. It is speaking only about quality of interpret an language. I would ask why? If I need solve fast this task I can write C function. What is reason and what can be impacts on speed for my aplication. Iam not sure if I understand well. Pavel Stehule ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Slow PL/pgSQL 8.0.RC5 (7.4.6. 3times faster)
I don't thing so differention on speed depends on compilation or other options. I compile 8.0 and 7.4.6 from source today. I didn't use any option for configure. But the difference is too big for optimalizations. [EMAIL PROTECTED] root]# uname -a Linux stehule.fsv.cvut.cz 2.6.4 #1 SMP Mon Mar 15 17:21:52 CET 2004 i586 i586 i386 GNU/Linux [EMAIL PROTECTED] root]# gcc --version gcc (GCC) 3.3 20030715 (Red Hat Linux 3.3-14) I know PostgreSQL is little slowly when started new block BEGIN END now when I use catch exceptions. But in this test isn't any other subblock. Only one cycle and some basic arithmetic operations. On Thu, 13 Jan 2005, Michael Fuhr wrote: On Thu, Jan 13, 2005 at 05:05:00PM -0500, Tom Lane wrote: Pavel Stehule [EMAIL PROTECTED] writes: And? (ie, what test case are you talking about?) This test is function for searching max factor. It is speaking only about quality of interpret an language. I would ask why? So I can replicate your test. SELECT delitel(100, 1); Mean times over the last five of six runs on my poor 500MHz FreeBSD 4.11-PRERELEASE box: 6741 ms 7.4.6 (from FreeBSD ports collection) 14427 ms 8.0.0rc5 (from CVS source) I remembered that I had build 8.0.0rc5 with --enable-debug so I rebuilt it without that option, not sure if that would make a difference. The mean time increased by 8% to 15580 ms, which was opposite from what I expected. I re-ran the 7.4.6 tests and they came out the same as they had before. I'm not sure what optimization flags (if any) the ports build of 7.4.6 might have used. I can take a closer look if you think it matters. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Slow PL/pgSQL 8.0.RC5 (7.4.6. 3times faster)
Hello, with IMMUTABLE or STABLE function is only 7% slowly. It can be usefull add into documentation so default flag is immutable, but if its not necessary its recommended IMMUTABLE or STABLE flag. Regards Pavel Stehule ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] slow SP with temporary tables, any idea of solution?
Hello I have very slow SP this type: BEGIN CREATE TEMP TABLE xxx ON COMMIT DROP(); WHILE n 0 LOOP -- n 0 FOR _r IN EXECUTE 'SELECT ...' LOOP RETURN NEXT _r; EXECUTE 'UPDATE xxx SET item = 1 WHERE id = '||_r.id; n := n - 1; END LOOP; END LOOP; END; The main problem is too much EXECUTE commands. My first idea wos substitute it PREPARED plans. But PREPARED plans are compiled when procedure is first time lunched, and I can't easy use it form temp tables. I can change PREPARE upd UPDATE item SET .. to EXECUTE 'PREPARE upd UPDATE..' and I really saved some time. But this method isn't possible for cmd FOR I have two possibility solution (before ending successfull solution for SP and temporary tables). First, the time for really preparing command will be really time of executing PREPARE command. Second, PREPARE can accept string parametr like EXECUTE command. But all is inpossible now. Can you help me other possibilities? Thank you Pavel Stehule ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] slow SP with temporary tables, PLPGSQL problems
Pavel Stehule [EMAIL PROTECTED] writes: Can you help me other possibilities? Create the temp table only once per connection (you can use ON COMMIT DELETE ROWS instead of ON COMMIT DROP to clean it out). Then you won't need to use EXECUTE. I am not sure so it's possible. I use persistent connect via PHP. There isn't trigger for new connect. But I found same problem as you. PL/pgSQL can't FOR r IN EXPLAIN SELECT .. FOR r IN EXECUTE 'EXECUTE plan()' I rewrite SP, and I have only one SELECT without two, 20% time less, but it's not readable code. I don't know how much work or if its possible move compilation time for PREPARE on every processing of this command. I think so its more natural for cmd PREPARE. But in this part of PL/pgSQL are more problems: CREATE OR REPLACE FUNCTION foo() RETURNS SETOF varchar AS $$ DECLARE r RECORD; BEGIN PREPARE se(date) AS SELECT * FROM queue WHERE activated = $1; FOR r IN EXECUTE se(CURRENT_DATE) LOOP RETUTRN NEXT r.activated; END LOOP; DEALLOCATE se; RETURN; END; $$ LANGUAGE plpgsql; is this code correct? I think yes. But isn't true. I get message: function se(date) does not exist CONTEXT: SQL statement SELECT se(CURRENT_DATE). Is only theory: FOR rn IN EXECUTE 'EXECUTE se(CURRENT_DATE)' LOOP Now I get error: cannot open non-SELECT query as cursor. Prepared commands are good idea, but I cant use its now. I have Pg 8.0.1 Regards Pavel Stehule ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] slow SP with temporary tables, PLPGSQL problems
Create the temp table only once per connection (you can use ON COMMIT DELETE ROWS instead of ON COMMIT DROP to clean it out). Then you won't need to use EXECUTE. I am sorry, first time I didn't understand. Now I did some test and its good adivice. Sometimes I have problem understand so I can use temp. tables on an level of connection and its not neccesery push temp tables on SP level. If I don't change structure of temp tables (and its unpropably on produstion database) I haven't problems. I have still one question. What is possible so I can CREATE OR REPLACE FONCTION foo() .. BEGIN CREATE TEMP TABLE xxx(... INSERT INTO xxx VALUES (... It's works, but in time of compilation SP temp table xxx doesn't exists. It's mean so Id in cmd INSERT is little bit dynamic? I rewrite my SP and I have one notice: In documentation is note, so TRUNCATE TABLE is faster then DELETE FROM. It's not true for small temp tables. On my computer TRUNCATE needs 100ms and DELETE 8ms. It's general or any exception? Thank You Pavel Stehule ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SQL99 Hierarchical queries
hello, I tested you patch, and it's good work. I would all methods in PostgreSQL. I found query which kill backand WITH t AS ( SELECT 0::int AS i UNION ALL SELECT i + 1 FROM t WHERE i 100) SELECT * FROM t; Regards Pavel Stehule ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SQL99 Hierarchical queries
On Mon, 28 Feb 2005, Evgen Potemkin wrote: Ok, I'm started porting it to 8.0.1 and will fix this also. By the way, did you know any test suit for such queries? To make some regression test. Hello, I can find some examples on internet and prepare regression tests. I think PostgreSQL can support all syntax H.Q. Is more easy created question via Oracle syntax, and processing is faster (maybe better optimalisation now), than ANSI WITH syntax. Can You add support for clausule VALUE? Pavel Stehule ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Exception ERROR Code
Hello, It's no possible now. But I prepared small patch which implemented variables sqlcode and sqlerrm for plpgsql. I can send it tomorrow. regards Pavel Stehule On Sat, 5 Mar 2005, Ali Baba wrote: Hi , I am looking for the way to get the error code corresponding to the exception in plpgsql. Can any body guide me. Thanks, Asif Ali __ Celebrate Yahoo!'s 10th Birthday! Yahoo! Netrospective: 100 Moments of the Web http://birthday.yahoo.com/netrospective/ ---(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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Implementation of SQLCODE and SQLERRM variables for PL/pgSQL
Hello
This is my second patch, than please will be tolerant :-). For one my
project I miss information about exception when I use EXCEPTION WITH
OTHERS THEN. I found two Oracle's varaibles SQLCODE and SQLERRM which
carry this information. With patch you can:
--
-- Test of built variables SQLERRM and SQLCODE
--
create or replace function trap_exceptions() returns void as $_$
begin
begin
raise exception 'first exception';
exception when others then
raise notice '% %', SQLCODE, SQLERRM;
end;
raise notice '% %', SQLCODE, SQLERRM;
begin
raise exception 'last exception';
exception when others then
raise notice '% %', SQLCODE, SQLERRM;
end;
return;
end; $_$ language plpgsql;
select trap_exceptions();
drop function trap_exceptions();
CREATE FUNCTION
NOTICE: P0001 first exception
NOTICE: 00 Sucessful completion
NOTICE: P0001 last exception
trap_exceptions
-
(1 row)
DROP FUNCTION
Regards,
Pavel Stehule
--
-- Test of built variables SQLERRM and SQLCODE
--
create or replace function trap_exceptions() returns void as $_$
begin
begin
raise exception 'first exception';
exception when others then
raise notice '% %', SQLCODE, SQLERRM;
end;
raise notice '% %', SQLCODE, SQLERRM;
begin
raise exception 'last exception';
exception when others then
raise notice '% %', SQLCODE, SQLERRM;
end;
return;
end; $_$ language plpgsql;
select trap_exceptions();
drop function trap_exceptions();
CREATE FUNCTION
NOTICE: P0001 first exception
NOTICE: 00 Sucessfull completation
NOTICE: P0001 last exception
trap_exceptions
-
(1 row)
DROP FUNCTION
357a358,360
int sqlcode_varno;
int sqlerrm_varno;
826a827,842
/* INICIALIZACE fooi a foot */
PLpgSQL_var *var;
var = (PLpgSQL_var *) (estate-datums[block-sqlcode_varno]);
var-isnull = false;
var-freeval = false;
var-value = DirectFunctionCall1(textin, CStringGetDatum(00));
var = (PLpgSQL_var *) (estate-datums[block-sqlerrm_varno]);
var-isnull = false;
var-freeval = false;
var-value = DirectFunctionCall1(textin, CStringGetDatum(Sucessful
completion));
931a948,966
/* unpack MAKE_SQLSTATE code */
chartbuf[12];
int ssval;
int i;
ssval = edata-sqlerrcode;
for (i = 0; i 5; i++)
{
tbuf[i] = PGUNSIXBIT(ssval);
ssval = 6;
}
tbuf[i] = '\0';
var = (PLpgSQL_var *)
(estate-datums[block-sqlcode_varno]);
var-value = DirectFunctionCall1(textin,
CStringGetDatum(tbuf));
var = (PLpgSQL_var *)
(estate-datums[block-sqlerrm_varno]);
var-value = DirectFunctionCall1(textin,
CStringGetDatum(edata-message));
88a89,93
struct
{
int sqlcode_varno;
int sqlerrm_varno;
} fict_vars;
104a110
%type fict_vars fict_vars_sect
251c257
pl_block : decl_sect K_BEGIN lno proc_sect exception_sect K_END
---
pl_block : decl_sect fict_vars_sect K_BEGIN lno proc_sect
exception_sect K_END
259c265
new-lineno = $3;
---
new-lineno = $4;
263,264c269,272
new-body = $4;
new-exceptions = $5;
---
new-body = $5;
new-exceptions = $6;
new-sqlcode_varno =
$2.sqlcode_varno;
new-sqlerrm_varno =
$2.sqlerrm_varno;
271a280,291
fict_vars_sect:
{
plpgsql_ns_setlocal(false);
PLpgSQL_variable*var;
var =
plpgsql_build_variable(strdup(sqlcode), 0,
plpgsql_build_datatype(TEXTOID, -1), true);
$$.sqlcode_varno = var-dno;
var =
plpgsql_build_variable(strdup(sqlerrm), 0,
plpgsql_build_datatype(TEXTOID, -1), true
Re: [HACKERS] Implementation of SQLCODE and SQLERRM variables for
I think we discussed this last year and decided that it would be a bad idea to use those names because Oracle's use of them is not exactly compatible with our error codes and messages. SQLCODE in particular is not compatible at all --- it's an integer in Oracle, isn't it? There is more incompatibilities to Oracle. SQLERRM is function on Oracle, only if you use it without parametr, returns current message error. SQLCODE is really integer. But it's only names. There is no problem change it. IIRC we had put off solving this problem until we decided what to do with RAISE. There really needs to be some changes in RAISE to allow it to raise a specific error code rather than always P0001, but exactly what is still undecided. I didn't know it. But for my work is SQLERRM more important. I have more constraints on tables and I need detect which which constraints raise exception. The possibility EXCEPTION WITH OTHERS is nice, but not too much usefull because I have not possibility get some informations about except. Some other problems with your patch: no documentation, and not in diff -c format. Plain diff patches are never acceptable because it's too risky to apply them against files that might have changed since you started working with them. Also, it's much easier to deal with one patch than with a separate diff for each file. (diff -c -r between an original and a modified directory is one good way to produce a useful patch.) I am not sure, I able create documentation - my english is poor. I will change diff's format and send patch again. Thank you Pavel ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] custome exception handling support ?
Hi, i want to add support for exceptions that are supported in oracle, in plpgsql. mainly i am want to add custome exceptions support in plpgsql. like in Oracle we use EXCEPTION myexp can any body help me. Hello http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING But in this time is inpossible get details about exception. Regards Pavel Stehule ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Proposal: OUT parameters for plpgsql
On Mon, 21 Mar 2005, Tom Lane wrote: Awhile back I wrote: Basically what I am thinking is that we have all the infrastructure today to solve the OUT-parameter problem, it's just not wrapped up in an easy-to-use package. Note that the result type is RECORD; we won't explicitly create a named composite type for such functions. (We could, perhaps, but I think it'd clutter the catalogs more than be useful.) It might be interesting however to allow explicit specification of RETURNS existing-composite-type with a matching set of OUT parameters. Calling such a function from SQL: you write just the values for the IN and INOUT parameters, and the result is a record of the OUT and INOUT parameters. So typical call style would be SELECT * FROM foo(1,2,'xyzzy'); Unlike with an ordinary RECORD-returning function, you do not specify an AS list, since the result column names and types are already known. (We'll have to invent a column name in the case of an OUT parameter that wasn't given a name in CREATE FUNCTION, but this seems like no big deal.) I am not sure so this syntax is readable. I'm sure, so this solution is possible and usefull, but you mix SRF style of calling and normal style. For anonymous out record (not OUT parameters) is better Firebird syntax CREATE FUNCTION fce (...) RETURNS (c1 integer, c2 integer) AS BEGIN c1 := 10; c2 := 20; RETURN; END; SELECT * FROM fce (...); c1 | c2 --- 10 | 20 There is on first view clear which calling style I have to use. This is very similar you proposal - one difference - all OUT params are separeted into return's list. Or clasic SP CREATE FUNCTION fce (IN a integer, OUT b integer) RETURNS bool AS BEGIN b := a; RETURN 't'; END; When I use OUT params I have to have DECLARE command for variables DECLARE b integer; SELECT fce(10, b); fce --- t SELECT b; b -- 10 This is (I think) more standard behavior. Regards Pavel Stehule ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Foreign keys on array elements
Hi, Can you put a foreign key constraint on an array column that says that each element of the array must match a primary key? If not, is this a TODO perhaps? Chris Hello, Using array values for foreign key is very special. I not sure, so all people need it. More interesting is CHECK on array. But you can write simply trigger. CREATE OR REPLACE FUNCTION check_() RETURNS TRIGGER AS $$ DECLARE _v integer; BEGIN FOR _i IN array_lower(NEW.array_value,1) .. array_upper(NEW.array_value,1) LOOP PERFORM 1 FROM some_tab WHERE pk = NEW.array_value[_i]; IF NOT FOUND THEN RAISE EXCEPTION '..'; END IF; END LOOP; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER foo BEFORE INSERT OR UPDATE ON ... FOR EACH ROW EXECUTE PROCEDURE check_(); Regards Pavel Stehule ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] bitmapscan test, no success, again
Hello, I get success, with my hyphotetic sample on big table (10 rec) Bitmap index scan is really usefull. I tested select count(*) from foo where v IN (11,11,23,11,11,11,11,11,22,71,11) Some notes. 1. with bitmap index scan 7.16 ms 2. without bis 165.731 ms (seq. scan) 3. list of constant contains equals values. Is possible remove it? explain analyze select count(*) from foo where v in (11,11,11,12) Regards Pavel Stehule ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] bitmapscan test, no success, bs is not faster
On Tue, 26 Apr 2005, Tom Lane wrote: Pavel Stehule [EMAIL PROTECTED] writes: I tested bitmap scan and maybe I didnt find good examples, but with bitmap scan is slower than hashjoin. Only when I use non otiptimized SELECT bps was little bit faster. All my SELECTs are equal. Bitmap scans can't possibly be any faster for cases where the indexscan only fetches one row, which is true of all your test cases AFAICS. yes, it's true. I found some selects where the benefit of bitmap scans is more clearly. There is only one small problem - optimizer didn't have to choose plan with bitmap scan in my examples. Thank you for explication, Regards Pavel Stehule ---(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: [pgsql-advocacy] [HACKERS] Increased company involvement
Another example is the recent patch to check if there are orphaned file system files. That was submitted, Tom had questions, I posted why I thought it was valid, and the patch is going in today. Anyone has the ability to argue their point and try to sway the community, and any member has the right to request a vote on a specific issue. I know so maintainig of PostgreSQL isn't easy. And it's normal so everybody wont to see commit of your patch. The comunication with core developers is best, but some times I have opinion so some patches are lost - for example my little patch SQLSTATE, .. I remeber situation one year ago with named params of plpgsql function. Patch waited half of year. I don't wont to be negative :-)). PostgreSQL did big progress. Really. And last modification of plpgsql helped my in work. But its human natural. I looking for others nows. I am expectant for 2PC, hiearch queries, and ... PostgreSQL isn't only sw for me, it's more like idol :-) Best Regards, Pavel Stehule ---(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
[HACKERS] PROPOSAL - User's exception in PL/pgSQL
Hello I did some work on implementation of user's exception. Generally: o add pseudotype EXCEPTION DECLARE excpt EXCEPTION [= 'SQLSTATE'] o change RAISE stmt RAISE error_level [excpt_var|sys_excpt_name] errmsg, ... o change EXCEPTION EXCEPTION WHEN excpt_var|sys_excpt_name THEN ... Rules: o User can specify SQLSTATE only from class 'U1' o Default values for SQLSTATE usr excpt are from class 'U0' o Every exception's variable has unique SQLSTATE o User's exception or system's exception can be raised only with level EXCEPTION Any comments, notes? Regards Pavel Stehule Regres test: create function innerfx() returns integer as $$ declare my_excpt exception = 'U0001'; begin -- using msgtext as one param of exception raise exception my_excpt '%', CURRENT_TIMESTAMP; return 1; end $$ language plpgsql; psql:regres.sql:6: ERROR: Invalid class for SQLSTATE value 'U0001' for user's exception. HINT: Select any unoccupied value from class U1 which is reserved for user's exception. CONTEXT: compile of PL/pgSQL function innerfx near line 1 create function innerfx() returns integer as $$ declare my_excpt exception = 'U1001'; my_sec_excpt exception = 'U1001'; begin -- using msgtext as one param of exception raise exception my_excpt '%', CURRENT_TIMESTAMP; return 1; end $$ language plpgsql; psql:regres.sql:15: ERROR: Invalid SQLSTATE value 'U1001' for user's exception. HINT: Select any unoccupied value from class U1 which is reserved for user's exception. CONTEXT: compile of PL/pgSQL function innerfx near line 3 create function innerfx() returns integer as $$ declare my_excpt exception = 'U1001'; begin -- using msgtext as one param of exception raise exception my_excpt '%', CURRENT_TIMESTAMP; return 1; end $$ language plpgsql; CREATE FUNCTION create function outerfx() returns integer as $$ declare my_excpt exception = 'U1001'; alias_div_by_zero exception = 'U1002'; my_excpt_def_sqlstate exception; begin begin raise exception my_excpt_def_sqlstate 'foo'; exception when my_excpt_def_sqlstate then raise notice '01 catch: %, %', sqlstate, sqlerrm; end; begin raise notice '%', innerfx(); exception when my_excpt then raise notice '02 catch: %, %', sqlstate, sqlerrm::timestamp; end; begin raise exception division_by_zero 'testing'; exception when division_by_zero then raise notice 'Divison by zero: %, %', sqlstate, sqlerrm; end; raise exception alias_div_by_zero 'Unhandled exception'; return 1; end; $$ language plpgsql; CREATE FUNCTION select innerfx(); psql:regres.sql:50: ERROR: 2005-06-16 10:12:53.27408+02 DETAIL: User's exception/notice - sqlstate: U1001, name: my_excpt HINT: from RAISE stmt on line 3 select outerfx(); psql:regres.sql:51: NOTICE: 01 catch: U0001, foo psql:regres.sql:51: NOTICE: 02 catch: U1001, 2005-06-16 10:12:53.274656 psql:regres.sql:51: NOTICE: Divison by zero: 22012, testing psql:regres.sql:51: ERROR: Unhandled exception DETAIL: User's exception/notice - sqlstate: U1002, name: alias_div_by_zero HINT: from RAISE stmt on line 21 drop function outerfx(); DROP FUNCTION drop function innerfx(); DROP FUNCTION ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Proposal - Continue stmt for PL/pgSQL
Hello Statement CONTINUE isn't in PL/SQL too, I know it, but Oracle PL/SQL has statement GOTO. I don't need GOTO statement, but 'continue' can be very usefull for me. I have to do some ugly trick now. With little change, we can enhance stmt EXIT for behavior continue. After some work I can CREATE OR REPLACE FUNCTION lll() RETURNS void AS $$ DECLARE i integer = 0; BEGIN LOOP i = i + 1; CONTINUE WHEN i 10; RAISE NOTICE '%', i; EXIT; END LOOP; BEGIN CONTINUE WHEN i = 10; RAISE NOTICE '---1---'; END; RAISE NOTICE '---2---'; FOR _i IN 1 .. 10 LOOP CONTINUE WHEN _i 5; RAISE NOTICE '%', _i; END LOOP; END; $$ LANGUAGE plpgsql; select lll(); pokus=# NOTICE: 10 NOTICE: ---2--- NOTICE: 5 NOTICE: 6 NOTICE: 7 NOTICE: 8 NOTICE: 9 NOTICE: 10 lll - (1 row) What do you think about it? It's broke PL/SQL compatibility, I know, but via last discussion I have opinion so Oracle compatibility isn't main objective PL/pgSQL. There is some less/bigger diferencess: SQLSTATE, EXCEPTION from my last proposal, atd. What do you think about it? Regards Pavel Stehule ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PROPOSAL - User's exception in PL/pgSQL
On Thu, 16 Jun 2005, Josh Berkus wrote: Pavel, o User can specify SQLSTATE only from class 'U1' o Default values for SQLSTATE usr excpt are from class 'U0' o Every exception's variable has unique SQLSTATE o User's exception or system's exception can be raised only with level EXCEPTION Any comments, notes? Looks great to me, pending a code examination. Will it also be possible to query the SQLSTATE/ERRSTRING inside the EXCEPTION clause? i.e. WHEN OTHERS THEN RAISE NOTICE '%',sqlstate; ROLLBACK; yes, ofcourse. CVS can it now That's something missing from 8.0 exception handling that makes it hard to improve SPs with better error messages. ---(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: [HACKERS] Proposal - Continue stmt for PL/pgSQL
On Thu, 16 Jun 2005, Josh Berkus wrote: Pavel, Statement CONTINUE isn't in PL/SQL too, I know it, but Oracle PL/SQL has statement GOTO. I don't need GOTO statement, but 'continue' can be very usefull for me. I have to do some ugly trick now. With little change, we can enhance stmt EXIT for behavior continue. Can you explain a little better what CONTINUE does that's different from EXIT? continue is equialent next iteration of cycle. exit break cycle. with block stmt? ~ break and continue are equal. Pavel ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Proposal - Continue stmt for PL/pgSQL
On Thu, 16 Jun 2005, Tom Lane wrote: Pavel Stehule [EMAIL PROTECTED] writes: BEGIN CONTINUE WHEN i = 10; RAISE NOTICE '---1---'; END; I find that really ugly and confusing. If we add a CONTINUE it's only sensible to allow it inside a loop --- otherwise it's just a nonstandard spelling of EXIT. I played too much :-). But, there is something wich can complicate implementation, if I disallow it inside block. for ... LOOP begin continue; end end loop; if I can use continue in begin and block I have easy rules for implementation. I have to first find any outside loop. But I think it's no really problem Pavel Stehule ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Proposal - Continue stmt for PL/pgSQL
Well, yes, but I don't think we should break compatibility arbitrarilly. I guess it could be argued that this is a missing feature in PL/SQL and its Ada parent - implementing GOTO just to handle this case seems unnecessary. Yes. I din't use goto 5 years :-). Continue stmt is more cleaner and readable. now: FOR i IN 1 .. 100 LOOP continue := true WHILE continue LOOP ... EXIT; -- contine continue := false; -- really exit END LOOP; END LOOP; with continue FOR i IN 1 .. 100 LOOP ... EXIT WHEN .. CONTINUE WHEN .. END LOOP; One argument for continue inside begin block - for discussion only. on loop exit means break iteration, continue new iteration. Continue and Exit are symmetric. I didn't know ADA haven't continue. In PL/pgSQL there isn't any problem implement continue stmt (wit any face), but goto stmt means relative big changes in source code. Pavel I agree with Tom that it should only be allowed inside a loop. cheers andrew ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] space for optimalization: DISTINCT without index
Hello I did some test and I can see so DISTINCT works well on indexed columns, but is slow on derived tables without indexes. If I use without distinct group by I get much better times. SELECT DISTINCT a, b FROM tab SELECT a,b FROM tab GROUP BY a, b. Can You Explain it. Thank You Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] ANSI SQL, CASE expression Conformance F262, F263 any info
Hello I can't find any information of syntax for ANSI Conformance F262, F263. Has somebody any information about this points: Extended CASE expression, comma separated predicates in simple case expression Thank You Pavel Stehule _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ANSI SQL, CASE expression Conformance F262, F263 any info
Pavel Stehule [EMAIL PROTECTED] writes: I can't find any information of syntax for ANSI Conformance F262, F263. Has somebody any information about this points: Extended CASE expression, comma separated predicates in simple case expression There are no such feature IDs listed in either SQL99 or SQL2003. What are you reading? regards, tom lane First, I looked http://www.postgresql.org/docs/8.1/interactive/unsupported-features-sql-standard.html I don't found any information on internet - only BNF syntax for SQL 2003. http://savage.net.au/SQL/sql-2003-2.bnf.html#xref-CASE SQL2003 has little bit different syntax for CASE. example: BETWEEN between := row value between_part2 between_part2 := [NOT] BETWEEN [ASYMMETRIC|SYMMETRIC] row value AND row value --- simple_case := CASE row value simple_when_clause [else_clause] END simple_when_clause := WHEN when_operand THEN result when_operand := row value | between_part2 | so I can: SELECT CASE EXTRACT(minute FROM when_col) WHEN BETWEEN 0 AND 14 THEN 0 WHEN BETWEEN 15 AND 29 THEN 15 WHEN BETWEEN 30 AND 44 THEN 30 ELSE 45 END this is extended CASE expression propably F262. About F263 I don't found any indicies. Maybe it's SELECT CASE col WHEN 1,3,5,7,9,11 THEN false ELSE END I looked into parser. F262 is usefull, but I am not sure if can be implemented. It's need refactoring gram.y or duplicate code for CASE, and maybe others - is possible share one operand in more expressions? I see others ANSI or NON ANSI enhancing as much usefull: window function or autonomous transaction. Regards Pavel Stehule _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 1: 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
[HACKERS] PL/pgSQL proposal: using list of scalars in assign stmts, fore and fors stmts
Hello
Now, statements EXECUTE INTO and SELECT INTO allow using list of scalars.
FORe and FORs allow only ROW o RECORD VARIABLE. I'll plan and I did it
enhance this stmts:
for := FOR target IN {SELECT | EXECUTE} ... LOOP
target := {row|record|comma separated list of scalar vars}
assign := target2 ':=' expression
target2 := {row|record|variable|'ROW(' comma separated list of scalar vars
')'}
for example:
CREATE OR REPLACE FUNCTION test(OUT _rc, OUT _x varchar, OUT _y varchar)
RETURNS SETOF RECORD AS $$
DECLARE _r RECORD;
BEGIN
rc := 0;
-- old style;
FOR _r IN SELECT generate_series AS x, generateseries + 1 AS y FROM
generate_series(1,4) LOOP
_rc := _rc + 1; _x := _r.x; _y := _r.y;
RETURN NEXT;
END LOOP;
-- new one
FOR _x,_y IN SELECT generate_series, generateseries + 1 FROM
generate_series(1,4) LOOP
_rc := _rc + 1;
RETURN NEXT;
END LOOP;
-- new two
FOR _r IN SELECT generate_series AS x, generateseries + 1 AS y FROM
generate_series(1,4)LOOP
_rc := _rc + 1; ROW(_x,_y) := _r;
RETURN NEXT;
END LOOP;
RETURN;
END; $$ LANGUAGE plpgsql;
any comments?
Regards
Pavel Stehule
_
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Fixing row comparison semantics
TODO updated: * %Make row-wise comparisons work per SQL spec Right now, '(a, b) (1, 2)' is processed as 'a 1 and b 2', but the SQL standard requires it to be processed as a column-by-column comparison, so the proper comparison is '(a 1) OR (a = 1 AND b 2)' Can we save current behave (with small modification) with other operator, like * (1,1) * (1,2) = true (1,2) * (2,1) is NULL (2,3) * (1,2) = false it's usefull for multicriterial optimalisation Regards Pavel Stehule _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Fixing row comparison semantics
Huh? The only current behavior with other operators is failure: you didn't understand me. I know so operator * isn't supported now. I prefere SQL spec behave too. But what I wont: a * b ~ ai = bi and one ai bi = true ; if one ai bi = NULL; else false but this behave is from some views really chaotic. This comparation is used in operation research, but propably is far to ideas ANSI SQL. It was only idea. regression=# select (1,1) * (1,2); ERROR: operator * is not supported for row expressions In any case, you can get the equivalent of the current behavior by writing out 1 * 1 AND 1 * 2 so I don't see any strong need to support non-SQL-spec behaviors here. regards, tom lane _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Oracle PL/SQL Anonymous block equivalent in postgres
Of course having the ability to execute arbitrary plpgsql in .sql scripts would be handy in some cases as well, though as others pointed out there are alternatives. I don't know if it's possible. PL/pgSQL parser live in different context than SQL parser and PostgreSQL supports more languages than one. These needs different syntax, maybe: EXECUTE CODE (integer, integer) RETURNS integer AS $$ DECLARE c integer = 10; BEGIN RETURN $1 + $2 + c; END; $$ LANGUAGE plppgsql USING(10,20); we can use plplgsql compiler, and only use different memory context. It's maybe better solution than temp functions. Regards Pavel Stehule _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] sending mail from Postgres
Hello, it depend on your possibilities. Simply, use PL/Perl or PL/sh. Regards Pavel Stehule or use PgSendMail http://sourceforge.net/project/showfiles.php?group_id=35804 Hi there, How can i send mail form postgresql. any suggestion. thanx regards aftab ---(end of broadcast)--- TIP 6: explain analyze is your friend _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] system triggers
Hello, Propably not. But You can use this patch http://gorda.di.uminho.pt/community/pgsqlhooks/ regards Pavel Stehule HEY!!! Are You going to implement some system triggers like in ORACLE i.e. on login trigger ?? -- Best regards LaroG ---(end of broadcast)--- TIP 6: explain analyze is your friend _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 1: 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
[HACKERS] slow information schema with thausand users, seq.scan pg_authid
Hello,
I know so db 500 000 users isn't normal situation, but I need it. After
user's generation all selects on system's catalog are slow. For example:
list of sequences
SELECT n.nspname as Schema,
c.relname as Name,
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as Type,
r.rolname as Owner
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('S','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
QUERY PLAN
-
Sort (cost=47532.09..47544.59 rows=5000 width=193) (actual
time=30333.490..30333.504 rows=5 loops=1)
Sort Key: n.nspname, c.relname
- Hash Left Join (cost=1.06..46947.04 rows=5000 width=193) (actual
time=45.918..30333.390 rows=5 loops=1)
Hash Cond: (outer.relnamespace = inner.oid)
Filter: (inner.nspname ALL ('{pg_catalog,pg_toast}'::name[]))
- Nested Loop Left Join (cost=0.00..46795.97 rows=5000
width=133) (actual time=28.648..30316.020 rows=5 loops=1)
Join Filter: (inner.oid = outer.relowner)
- Seq Scan on pg_class c (cost=0.00..9.59 rows=2 width=73)
(actual time=16.212..165.521 rows=5 loops=1)
Filter: ((relkind = ANY ('{S,}'::char[])) AND
pg_table_is_visible(oid))
- Seq Scan on pg_authid (cost=0.00..12143.06 rows=56
width=118) (actual time=12.702..4306.537 rows=56 loops=5)
- Hash (cost=1.05..1.05 rows=5 width=68) (actual
time=0.070..0.070 rows=5 loops=1)
- Seq Scan on pg_namespace n (cost=0.00..1.05 rows=5
width=68) (actual time=0.013..0.035 rows=5 loops=1)
Total runtime: 30376.547 ms
there is any possibility creating index for pg_authid?
best regards
Pavel Stehule
_
Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] slow information schema with thausand users, seq.scan pg_authid
In the meantime, reducing the LEFT JOIN to pg_roles to a JOIN as per
Peter's suggestion seems like the best short-term workaround.
It's solution
explain analyze SELECT n.nspname as Schema,
c.relname as Name,
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN \'special' END as Type,
r.rolname as Owner
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('S','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
Sort (cost=22.68..22.68 rows=2 width=193) (actual time=1.047..1.064 rows=5
loops=1)
Sort Key: n.nspname, c.relname
- Nested Loop Left Join (cost=1.05..22.67 rows=2 width=193) (actual
time=0.480..0.983 rows=5 loops=1)
Join Filter: (inner.oid = outer.relnamespace)
Filter: (inner.nspname ALL ('{pg_catalog,pg_toast}'::name[]))
- Nested Loop (cost=0.00..21.34 rows=2 width=133) (actual
time=0.386..0.642 rows=5 loops=1)
- Seq Scan on pg_class c (cost=0.00..9.29 rows=2 width=73)
(actual time=0.334..0.431 rows=5 loops=1)
Filter: ((relkind = ANY ('{S,}'::char[])) AND
pg_table_is_visible(oid))
- Index Scan using pg_authid_oid_index on pg_authid
(cost=0.00..6.01 rows=1 width=68) (actual time=0.02$
Index Cond: (pg_authid.oid = outer.relowner)
- Materialize (cost=1.05..1.10 rows=5 width=68) (actual
time=0.007..0.032 rows=5 loops=5)
- Seq Scan on pg_namespace n (cost=0.00..1.05 rows=5
width=68) (actual time=0.008..0.028 rows=5 loops=1$ Total runtime: 1.294 ms
Regards
Pavel Stehule
_
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Expression index with function based on current_user?
The function private.haveaccess()'s result depends on the currently logged in user, is it still possible to create an expression index over that function? // Fredrik Olsson Hello, All functions and operators used in an index definition must be immutable, that is, their results must depend only on their arguments and never on any outside influence. ... And your function is vollatile = you can't to do expression index. Regards Pavel Stehule _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---(end of broadcast)--- TIP 1: 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: [HACKERS] SpeedComparison
Andrej Ricnik-Bay wrote: Has anyone here seen this one before? Do the values appear realistic? http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison Some of the particularly bad test results for PostgreSQL may be related to using the default memory configuration and never having run ANALYZE. The center point of this isn't missing ANALYZE, but so was used PostgreSQL on windows. There is propably bug in test no. 8. I hadn't this problem on Linux. Others PostgreSQL is comparable with Firebird. If you look on this test - it's only simple SELECT or max. join two tables. There isn't space for PostgreSQL force. When I did test for self join 4 tables, PostgreSQL was better then MySQL and Firebird Pavel _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] how solve diff of API counstruct_md_array between 8.1 and 8.2?
Hello I use counstruct_md_array function in my Orafunc module. CVS version has diff def now. I am findig way for simple solution of maintaince source code for both version. I have PG_VERSION variable, but it's unusable. Is there way for contrib's autors differentiate PostgreSQL versions? I don't want to have two versions of source code. Thank you Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Request: set opclass for generated unique and primary key indexes
Hello There isn't possibility change opclass for generated UNIQUE indexes. I found syntax for CREATE TABLE command USING INDEX TABLESPACE ..., This form can be enhanced to USING INDEX [TABLESPACE ..] [OPCLASS ..] What do you think about it? Regards Pavel Stehule _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Request: set opclass for generated unique and primary key indexes
Why would you need it? I can't to use unique index for like_op without setting opclass, because I have to use czech locale. I can create second index, but then I have two equal indexes. Example: number | description 000102 blabla bla 000103 fooo number: varchar primary key. Sometimes I need search all values with one prefix ~ like '0001%'. That's all. USING INDEX [TABLESPACE ..] [OPCLASS ..] This is unworkable --- consider a table with more than one unique constraint and/or multiple-column constraints. I forgot (full syntax is): CREATE TABLE number varchar PRIMARY KEY USING OPCLAS varchar_pattern_ops, ... I seem to recall someone proposing extending the syntax of the UNIQUE constraints themselves, but there really isn't enough use-case to justify it AFAICS. Especially not when you can always use CREATE UNIQUE INDEX. I can always use second unique index. But it's redundant. This problem is related to using nonC locale. Regards Pavel Stehule _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Request: set opclass for generated unique and primary key indexes
I seem to recall someone proposing extending the syntax of the UNIQUE constraints themselves, but there really isn't enough use-case to justify it AFAICS. Especially not when you can always use CREATE UNIQUE INDEX. I can always use second unique index. But it's redundant. This problem is related to using nonC locale. Why do you need both the unique index with varchar_pattern_ops and one with the default ops? Because LIKE op don't use index on default ops with non C locale. I found it on tables of czech communities. Primary key is NUTS - 4..6 numbers. I have to search values with some prefix - op Like and on primary key can't to use std. index. Pavel _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Request: set opclass for generated unique and primary key indexes
Right, but does the pattern_ops one have to be unique?
Sorry, I don't uderstand
And if it does, do you need the normal unique constraint as well?
Sometime yes. It's about using natural or generated unique values.
I looked to source code and I propouse syntax:
CREATE TABLE name ..
colname type PRIMARY KEY | UNIQUE [USING INDEX [TABLESPACE ...] [OPERATOR
CLASS opclass]
CREATE CONSTRAINT name
PRIMARY KEY | UNIQUE '(' colname opt_class,
I don't think so this need big patch.
What do you think?
Regards, nice a day
Pavel Stehule
_
Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com.
http://www.msn.cz/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[HACKERS] Function's final statement must not be a SELECT
Hello, I want do sql wrap for woid plpgsql function. But void SQL function must not finish SELECT cmd. I don't know any others command which I can use. Can You help me? Thank You Pavel Stehule _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Function's final statement must not be a SELECT
From: Jaime Casanova [EMAIL PROTECTED] To: Pavel Stehule [EMAIL PROTECTED] CC: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Function's final statement must not be a SELECT Date: Sat, 11 Mar 2006 12:42:15 -0500 On 3/10/06, Pavel Stehule [EMAIL PROTECTED] wrote: Hello, I want do sql wrap for woid plpgsql function. But void SQL function must not finish SELECT cmd. I don't know any others command which I can use. Can You help me? Thank You Pavel Stehule perform * from your_table; Sorry, I need SQL construct. Please try: create or replace function foo(bool) returns void as $$ ... $$ language plpgsql volatile strict; I can't to do: create or replace function wrapper() returns void as $$ select foo(true); $$ language sql volatile strict; Regards Pavel _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Intersession communication is available, please test it
Hello I finished implementation dbms_pipe package for PostgreSQL. It fully support private, public pipe, explicitly or implicitly created pipes, limits for pipes. Please look: http://pgfoundry.org/frs/download.php/818/orafce-2.0.0-preview.tgz This version contains plvstr and plvdate packages too. plvdate is one from widespread solutions for weakdays calculations. I appreciate the comments Regards Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] SQL/XML extension
Hello, This patch is well, I hope. I didn't look on it half year. Contains: SQL/XML support + doc by D.Fetter http://candle.pha.pa.us/mhonarc/patches_hold/msg00134.html regards Pavel Stehule _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 1: 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
[HACKERS] proposal - plpgsql: execute using into
Hello Current EXECUTE statemtn doesn't support other way for parametrisation than concating strings. It works well but it's little bit unreadable. Oracle's statement EXECUTE has positional replacement feature. It works similar our RAISE statement (when position holder is %). EXECUTE position holder has form :. has only symbolic value and isn't used for anything. Syntax of enhanced statements is: EXECUTE 'format string' USING expr_list There are some problems about replacing string values in the SQL string. Sometimes we have to enclose value between spaces or others symbols (apostrophe or double apostrophe), sometimes not. Possible rules: a) if position holder is inside string or identifier we don't enclose value; b) else numeric values are enclosed spaces and others (non regclass) single apostrophes c) regclass's values are enclosed douple apostrophes. PL/pgSQL knows three dynamic statements. All will be enhanced. Some examples: EXECUTE 'SELECT :name||:sp||:surname' USING 'Pavel',' ','Stehule'; EXECUTE e'SELECT \':name :surname' USING 'Pavel','Stehule'; EXECUTE 'SELECT * FROM :tabname' USING 'xb'::regclass; EXECUTE 'SELECT * FROM :base:num USING 'mytab',1; You can test it. I sent patch to pg_patches. I invite any comments Pavel Stehule _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] commit callback, request
Hello Is possible make transaction commit trigger without patching code now? I finding way , but all usable interfaces are static. I remember on diskussion about it and about changes in LISTEN/NOTIFY implementation. Is there any progress? I need it for simulation of Oracle dbms_alert.signal function. Whole dbms_alert package is similar our LISTEN/NOTIFY. Difference is dbms_alert is server side solution and L/N is client side. Is any chance so this interface will be in 8.2? Regards Pavel Stehule _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] commit callback, request
Pavel Stehule [EMAIL PROTECTED] writes: Is possible make transaction commit trigger without patching code now? You can get pretty close with a deferred trigger. I don't think there's any way to have a guaranteed at commit trigger --- as soon as (1) there are two of them and (2) one can get an error, the transaction could fail after running an alleged at commit trigger. regards, tom lane hm. I don't have big problem with false notifications. Who want to use dbms_alert have to calculate with this possibility. But triggers has disadventage - I have to sometimes clean any table, which cary triggers :-(. It's solution. I hope 8.2 will has any general mechanis. Is it possible enhance SPI to parametrized NOTIFY? Default mode send message via libpq, nonstandard raise any callback. Thank You Pavel _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 1: 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
[HACKERS] request: muting notice CREATE TABLE will create implicit sequence
Hello I am working on general functions accessable from console too. I create tempory tables from functions. Is necessary print notice about using serial type? I think actually we don't need print it, becouse DROP TABLE use dependencies. Regards Pavel Stehule _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 1: 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: [HACKERS] commit callback, request, SOLVED
Refered triggers works well, better than I expected. It's not equal NOTIFY,
but it works.
Thank You
Pavel Stehule
CREATE OR REPLACE FUNCTION dbms_alert._defered_signal() RETURNS trigger AS
$$
BEGIN
PERFORM dbms_alert._signal(NEW.event, NEW.message);
DELETE FROM ora_alerts WHERE id=NEW.id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER VOLATILE;
CREATE OR REPLACE FUNCTION dbms_alert.signal(_event text, _message text)
RETURNS void AS $$
BEGIN
PERFORM 1 FROM pg_catalog.pg_class c
WHERE pg_catalog.pg_table_is_visible(c.oid)
AND c.relkind='r' AND c.relname = 'ora_alerts';
IF NOT FOUND THEN
CREATE TEMP TABLE ora_alerts(id serial PRIMARY KEY, event text, message
text);
REVOKE ALL ON TABLE ora_alerts FROM PUBLIC;
CREATE CONSTRAINT TRIGGER ora_alert_signal AFTER INSERT ON ora_alerts
INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE
dbms_alert._defered_signal();
END IF;
INSERT INTO ora_alerts(event, message) VALUES(_event, _message);
END;
$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;
drop table test_alert cascade;
create table test_alert(v varchar);
create or replace function checkdata() returns void as $$
declare r record; d record;
begin
perform dbms_alert.register('refresh');
while true loop
select into r * from dbms_alert.waitone('refresh',10);
perform pg_sleep(0.1); -- I need wait moment
select into d * from test_alert where v = r.message;
raise notice 'found %', d;
end loop;
end;
$$ language plpgsql;
create or replace function ins(varchar) returns void as $$
begin
insert into test_alert values($1);
perform dbms_alert.signal('refresh',$1);
end;
$$ language plpgsql;
_
Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[HACKERS] please actualize FAQ, broken urls
Hello 1.11) How can I learn SQL? ... There is also a nice tutorial at http://www.intermedia.net/support/sql/sqltut.shtm, at http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM, and at http://sqlcourse.com. first link is broken, second moved Regards Pavel Stehule _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] please actualize FAQ, broken urls
David Fetter dir patch two days ago Pavel From: Bruce Momjian pgman@candle.pha.pa.us To: Pavel Stehule [EMAIL PROTECTED] CC: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] please actualize FAQ, broken urls Date: Thu, 13 Apr 2006 08:09:40 -0400 (EDT) Pavel Stehule wrote: Hello 1.11) How can I learn SQL? ... There is also a nice tutorial at http://www.intermedia.net/support/sql/sqltut.shtm, at http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM, and at http://sqlcourse.com. first link is broken, second moved The first URL works for me now, and I updated the URL for the second. He mentions DB2 in the second URL, but it probably still generic SQL. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 1: 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
[HACKERS] pgfoundry - news - is working?
Hello yesterday I did one news notice on my orafunc page. But it isn't on main page yet. Why? Can someboody explain mechanism of publishing messages on pgfoundry? Regards Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] please change url for czech postgresql site
Hello, please change url on http://www.postgresql.org/community/international from postgresql.ok.cz to http://postgresql.interweb.cz I actualized content for 8.1 and migrated to wiki and new site. Older site exists still, but isn't actualized. Thank you Pavel Stehule _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] please change url for czech postgresql site
I think you mean updated not actualized right? In any case, I've changed the url in cvs, it should appear on the next site build. yes, I am sorry. Thank you btw, I hope that site is running postgresql as a backend :-) Not yet :-(. I had big problems get good free hosting with php5. And I know only one reason for change backend from mysql to postgresql - tsearch2. It's supported now? What about spead? And my provider has 7.4 still. Maybe next version :-), it's shame Pavel _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] plpgsql cant to set role from function. is bug?
Hello This code run without error, but do nothing drop role x; create role x; create or replace function foo() returns void as $$ begin grant root to x; end; $$ language plpgsql; \dg x revoke root from x; postgres=# postgres=# DROP ROLE postgres=# CREATE ROLE postgres=# postgres$# postgres$# postgres$# postgres$# CREATE FUNCTION postgres=#List of roles Role name | Superuser | Create role | Create DB | Connections | Member of ---+---+-+---+-+--- x | no| no | no| no limit| (1 row) postgres=# WARNING: role x is not a member of role root REVOKE ROLE postgres=# what is wrong? Regards Pavel Stehule p.s. root is postgresql superuser _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] plpgsql cant to set role from function. is bug?, SOLVED
Hello I am stupid. 1) I don't call function. I am sorry Regards Pavel Stehule _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Wrong plan for simple join with index on FK
Hello I test using index on foreign key. I found situation, when planner choose worse plan. create table f1(pk serial primary key); create table f2(fk integer references f1(pk)); insert into f1 select a from generate_series(1,1) a; insert into f2 select (random()*)::int+1 from generate_series(1,14); vacuum analyze; create index xxx on f2(fk); \timing postgres= select count(*) from f1 join f2 on pk=fk; count 14 (1 row) Time: 538,254 ms drop index xxx; postgres= select count(*) from f1 join f2 on pk=fk; count 14 (1 row) Time: 311,580 ms Plans: postgres= explain select count(*) from f1 join f2 on pk=fk; QUERY PLAN -- Aggregate (cost=7788.00..7788.01 rows=1 width=0) - Hash Join (cost=170.00..7438.00 rows=14 width=0) Hash Cond: (f2.fk = f1.pk) - Seq Scan on f2 (cost=0.00..2018.00 rows=14 width=4) - Hash (cost=145.00..145.00 rows=1 width=4) - Seq Scan on f1 (cost=0.00..145.00 rows=1 width=4) (6 rows) postgres= explain select count(*) from f1 join f2 on pk=fk; QUERY PLAN Aggregate (cost=6631.75..6631.76 rows=1 width=0) - Merge Join (cost=0.00..6281.75 rows=14 width=0) Merge Cond: (f1.pk = f2.fk) - Index Scan using f1_pkey on f1 (cost=0.00..187.00 rows=1 width=4) - Index Scan using xxx on f2 (cost=0.00..4319.77 rows=14 width=4) (5 rows) PostgreSQL 8.1, Linux Regards Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 1: 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: [HACKERS] Wrong plan for simple join with index on FK
Can we seen an EXPLAIN ANALYZE output to see where the miscalculation lies. Is it underestimating the cost of the index scan, or overestimating the cost of the hash join. postgres= explain analyze select count(*) from f1 join f2 on pk=fk; QUERY PLAN --- Aggregate (cost=6631.75..6631.76 rows=1 width=0) (actual time=2433.700..2433.703 rows=1 loops=1) - Merge Join (cost=0.00..6281.75 rows=14 width=0) (actual time=0.055..1916.815 rows=14 loops=1) Merge Cond: (f1.pk = f2.fk) - Index Scan using f1_pkey on f1 (cost=0.00..187.00 rows=1 width=4) (actual time=0.025..45.635 rows=1 loops=1) - Index Scan using xxx on f2 (cost=0.00..4319.77 rows=14 width=4) (actual time=0.011..812.661 rows=14 loops=1) Total runtime: 2433.859 ms (6 rows) postgres= explain analyze select count(*) from f1 join f2 on pk=fk; QUERY PLAN - Aggregate (cost=7788.00..7788.01 rows=1 width=0) (actual time=2216.490..2216.493 rows=1 loops=1) - Hash Join (cost=170.00..7438.00 rows=14 width=0) (actual time=80.296..1712.505 rows=14 loops=1) Hash Cond: (f2.fk = f1.pk) - Seq Scan on f2 (cost=0.00..2018.00 rows=14 width=4) (actual time=0.031..493.614 rows=14 loops=1) - Hash (cost=145.00..145.00 rows=1 width=4) (actual time=80.201..80.201 rows=1 loops=1) - Seq Scan on f1 (cost=0.00..145.00 rows=1 width=4) (actual time=0.025..37.587 rows=1 loops=1) Total runtime: 2216.730 ms (7 rows) Regards Pavel _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Wrong plan for simple join with index on FK
These are all minor abberations though, on the whole the estimates are pretty good. Perhaps you need to tweak the values of random_page_cost and similar variables. Thank You, It's general problem or only mine? I have 100% standard current PC. Pavel _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Wrong plan for simple join with index on FK
These are all minor abberations though, on the whole the estimates are pretty good. Perhaps you need to tweak the values of random_page_cost and similar variables. Thank You, It's general problem or only mine? I have 100% standard current PC. The default random_page_cost assumes some concurrent activity. If your PC does nothing else concurrently, the performance of a seq scan will be underestimated. Try to do the statement with some concurrent disk load and you will most likely see that the 1. plan is faster. (assuming the tables are not fully cached) Andreas ok. I tested it with pgbench and it's true. With -c 50 merge_join is faster. I didn't expect it. Thank You Pavel _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] array constructor can't construct empty array
Hello
I have function
CREATE FUNCTION foo(date, date, INTEGER[]) RETURNS INTEGER
Array and array's functions works fine, but I need call this function
with empty array. I can't use array constructor for empty array. When
I call function foo with e.a. ,I get syntax error. I can call with '{}'.
What is coorect style?
Thank You
Pavel Stehule
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] array constructor can't construct empty array
On Mon, 1 Sep 2003, Bruce Momjian wrote:
Pavel Stehule wrote:
Hello
I have function
CREATE FUNCTION foo(date, date, INTEGER[]) RETURNS INTEGER
Array and array's functions works fine, but I need call this function
with empty array. I can't use array constructor for empty array. When
I call function foo with e.a. ,I get syntax error. I can call with '{}'.
Are you running 7.4beta?
I am sorry, yes, of course :-,
testdb= SELECT version();
version
---
PostgreSQL 7.4beta1 on i586-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2
(1 dka)
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
