Re: [HACKERS] Is current_user a function ?
On 28 Nov 2002 11:34:49 -0500 Rod Taylor [EMAIL PROTECTED] wrote: Force the system to use it as a function. select current_user(); On Thu, 28 Nov 2002 17:20:59 -0500 Tom Lane [EMAIL PROTECTED] wrote: As for some current_*** functions, select current_user; seems to work, but select current_user(); doesn't . Complain to the SQL spec authors --- they mandated this peculiar keyword syntax for what is really a function call. Since current_user() can be used in 7.2, I have thought it would work in 7.3 too. I now understand it doesn't work any more -- as well, session_user(), user(), current_date(), current_time(), current_timestamp() and etc. Thank you, Rod and Tom. Regards, Masaru Sugawara ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: quote_ident and schemas (was Re: [HACKERS] connectby with schema)
On Fri, 22 Nov 2002 15:21:48 -0800
Joe Conway [EMAIL PROTECTED] wrote:
OK. Attached patch removes calls within the function to quote_ident, requiring
the user to appropriately quote their own identifiers. I also tweaked the
regression test to deal with value becoming a reserved word.
If it's not too late, I'd like this to get into 7.3, but in any case, please
apply to HEAD.
Thank you for your quick job.
Regards,
Masaru Sugawara
-
CREATE SCHEMA ms;
CREATE TABLE ms.test (id int4, parent_id int4, t text);
INSERT INTO ms.test VALUES(11, null, 'aaa');
INSERT INTO ms.test VALUES(101, 11, 'bbb');
INSERT INTO ms.test VALUES(110, 11, 'ccc');
INSERT INTO ms.test VALUES(111, 110, 'ddd');
SELECT *
FROM connectby('ms.test', 'id', 'parent_id', '11', 0, '.')
as t(id int, parent_id int, level int, branch text);
id | parent_id | level | branch
-+---+---+
11 | | 0 | 11
101 |11 | 1 | 11.101
110 |11 | 1 | 11.110
111 | 110 | 2 | 11.110.111
(4 rows)
CREATE SCHEMA MS;
drop table MS.Test;
CREATE TABLE MS.Test (id int4, parent_id int4, t text);
INSERT INTO MS.Test VALUES(22, null, 'aaa');
INSERT INTO MS.Test VALUES(202, 22, 'bbb');
INSERT INTO MS.Test VALUES(220, 22, 'ccc');
INSERT INTO MS.Test VALUES(222, 220, 'ddd');
SELECT *
FROM connectby('MS.Test', 'id', 'parent_id', '22', 0, '.')
as t(id int, parent_id int, level int, branch text);
id | parent_id | level | branch
-+---+---+
22 | | 0 | 22
202 |22 | 1 | 22.202
220 |22 | 1 | 22.220
222 | 220 | 2 | 22.220.222
(4 rows)
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] connectby with schema
Hi, all
While testing RC1, I found CONNECTBY had another problem.
It seems to me that SCHEMA can't be used in CONNECTBY.
Is it just in time for 7.3 to be added to TODO items ?
CREATE TABLE test (id int4, parent_id int4, t text);
INSERT INTO test VALUES(11, null, 'aaa');
INSERT INTO test VALUES(101, 11, 'bbb');
INSERT INTO test VALUES(110, 11, 'ccc');
INSERT INTO test VALUES(111, 110, 'ddd');
SELECT *
FROM connectby('test', 'id', 'parent_id', '11', 0, '.')
as t(id int4, parent_id int4, level int, branch text);
id | parent_id | level | branch
-+---+---+
11 | | 0 | 11
101 |11 | 1 | 11.101
110 |11 | 1 | 11.110
111 | 110 | 2 | 11.110.111
(4 rows)
CREATE SCHEMA ms;
CREATE TABLE ms.test (id int4, parent_id int4, t text);
INSERT INTO ms.test VALUES(11, null, 'aaa');
INSERT INTO ms.test VALUES(101, 11, 'bbb');
INSERT INTO ms.test VALUES(110, 11, 'ccc');
INSERT INTO ms.test VALUES(111, 110, 'ddd');
SELECT *
FROM connectby('ms.test', 'id', 'parent_id', '101', 0, '.')
as t(id int4, parent_id int4, level int, branch text);
ERROR: Relation ms.test does not exist
Regards,
Masaru Sugawara
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Transactions through dblink_exec()
On Sat, 12 Oct 2002 23:37:18 -0700
Joe Conway [EMAIL PROTECTED] wrote:
Masaru Sugawara wrote:
I'm hoping that dblink_exec() returns something like warning if those
who intend to do transactions make a declaration of
blink_exec('dbname=some', 'begin') by mistake.
for example
WARNING :You should declare dblink_exec('dbname=some', 'BEGIN; some queries;
COMMIT/ROLLBACK/END;') or use dblink_exec('BEGIN/COMMIT/ROLLBACK/END')
around dblink_exec('some queries')s. If not, your transactions won't work.
{...snip...]
-- case 3. --
SELECT dblink_exec('dbname=regression_slave', 'BEGIN');
SELECT dblink_exec('dbname=regression_slave',
'INSERT INTO foo VALUES(12,''m'',''{a12,b12,c12}'');');
SELECT dblink_exec('dbname=regression_slave', 'ROLLBACK'); -- failure !
Hmmm. No surprise this din't work. Each time you specify the connect string, a
connection is opened, the statement executed, and then the connection is
closed -- i.e. each of the invocations of dblink_exec above stands alone. Are
you suggesting a warning only on something like:
SELECT dblink_exec('dbname=regression_slave', 'BEGIN');
Yes.
? Seems like maybe a warning in the documentation would be enough.
Yes, certainly. I came to think a warning in the doc is better than in the
command line because that is not a bug.
Any other opinions out there?
What occurs to me though, is that this is one of those clients affected by
the autocommit setting situations. (...goes off and tries it out...) Sure
enough. If you have autocommit set to off, you can do:
SELECT dblink_exec('dbname=regression_slave',
'INSERT INTO foo VALUES(12,''m'',''{a12,b12,c12}'');');
all day and never get it to succeed.
I didn't think of a situation of autocommit = off. As for me in some
transactions like the following, I haven't deeply worried about behaviors of
dblink_exec(CONNSTR, 'BEGIN') because I would like to use dblink_connect() .
However, I'm not sure whether the following is perfectly safe against every
accident or not .
BEGIN;
SELECT dblink_connect('dbname=regression_slave');
SELECT dblink_exec('BEGIN');
SELECT dblink_exec('INSERT INTO foo VALUES(12, ''m'', ''{a12,b12,c12}'');');
INSERT INTO foo VALUES(12, 'm', '{a12,b12,c12}');
SELECT dblink_exec('END');
SELECT dblink_disconnect();
END;
or
CREATE OR REPLACE FUNCTION fn_mirror() RETURNS text AS '
DECLARE
ret text;
BEGIN
PERFORM dblink_connect(''dbname=regression_slave'');
PERFORM dblink_exec(''BEGIN'');
-- PERFORM dblink_exec(
-- ''INSERT INTO foo VALUES(12, m, {a12,b12,c12});'');
SELECT INTO ret * FROM dblink_exec(
''INSERT INTO foo VALUES(12, m, {a12,b12,c12});'');
RAISE NOTICE ''slave : %'', ret;
INSERT INTO foo VALUES(12, ''m'', ''{a12,b12,c12}'');
PERFORM dblink_exec(''END'');
PERFORM dblink_disconnect();
RETURN ''OK'';
END;
' LANGUAGE 'plpgsql';
SELECT fn_mirror();
Given the above, should dblink_exec(CONNSTR, SQL) always wrap SQL in an
explicit transaction? Any thoughts on this?
Joe
Regards,
Masaru Sugawara
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Transactions through dblink_exec()
Hi, all
While trying dblink_exec(), one of dblink()'s functions, I noticed there was an
odd situation: case 1 and case 2 worked well, but case 3 didn't(see below).
I hadn't been aware of it so that I only executed BEGIN and END in
dblink_exec() at first . This time, however, I noticed it by executing ROLLBACK.
I'm hoping that dblink_exec() returns something like warning if those who intend
to do transactions make a declaration of blink_exec('dbname=some', 'begin') by mistake.
for example
WARNING :You should declare dblink_exec('dbname=some', 'BEGIN; some queries;
COMMIT/ROLLBACK/END;') or use dblink_exec('BEGIN/COMMIT/ROLLBACK/END')
around dblink_exec('some queries')s. If not, your transactions won't work.
Regards,
Masaru Sugawara
On Fri, 27 Sep 2002 09:35:48 -0700
Joe Conway [EMAIL PROTECTED] wrote:
...
The version of dblink in 7.3 (in beta now) has a new function, dblink_exec,
which is specifically intended for INSERT/UPDATE/DELETE. If you can, please
give the beta a try.
I have a patch that allows dblink in 7.2 to execute INSERT/UPDATE/DELETE
statements. I'll send it to you off-list if you want (let me know), but it
would be better if you can wait for 7.3 to be released and use it.
Joe
...
query
dblink(text,text) RETURNS setof record
- returns a set of results from remote SELECT query
(Note: comment out in dblink.sql to use deprecated version)
from http://archives.postgresql.org/pgsql-general/2002-09/msg01290.php
-- tables --
$ cd ../postgresql-7.3.b2/contrib/dblink
$ createdb regression_slave
$ createdb regression_master
$ createlang plpgsql regression_master
$ psql regression_slave
\i dblink.sql
CREATE TABLE foo(f1 int, f2 text, f3 text[], PRIMARY KEY (f1,f2));
INSERT INTO foo VALUES(0,'a','{a0,b0,c0}');
INSERT INTO foo VALUES(1,'b','{a1,b1,c1}');
INSERT INTO foo VALUES(2,'c','{a2,b2,c2}');
\connect regression_master;
\i dblink.sql
CREATE TABLE foo(f1 int, f2 text, f3 text[], PRIMARY KEY (f1,f2));
INSERT INTO foo VALUES(0,'a','{a0,b0,c0}');
INSERT INTO foo VALUES(1,'b','{a1,b1,c1}');
INSERT INTO foo VALUES(2,'c','{a2,b2,c2}');
-- case 1. --
SELECT dblink_connect('dbname=regression_slave');
SELECT dblink_exec('BEGIN');
SELECT dblink_exec('INSERT INTO foo VALUES(12,''m'',''{a12,b12,c12}'');');
SELECT dblink_exec('ROLLBACK'); -- success !
SELECT dblink_disconnect();
-- case 2. --
SELECT dblink_exec('dbname=regression_slave',
'BEGIN;
INSERT INTO foo VALUES(12,''m'',''{a12,b12,c12}'');
ROLLBACK;
');-- success !
-- case 3. --
SELECT dblink_exec('dbname=regression_slave', 'BEGIN');
SELECT dblink_exec('dbname=regression_slave',
'INSERT INTO foo VALUES(12,''m'',''{a12,b12,c12}'');');
SELECT dblink_exec('dbname=regression_slave', 'ROLLBACK'); -- failure !
---(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] Does setof record in plpgsql work well in 7.3?
Hi, all Does 7.3 support SETOF RECORD in plpgsql ? As far as I test it, a function using it in plpgsql always seems to return no row. On the other hand, a sql function returns correct rows. If 7.3 doesn't support it in plpgsql, I would think plpgsql needs to raise an error rather than return 0 rows message. Am I misunderstanding how to use? -- CREATE TABLE test (a integer, b text); INSERT INTO test VALUES(1, 'function1'); INSERT INTO test VALUES(2, 'function2'); INSERT INTO test VALUES(1, 'function11'); INSERT INTO test VALUES(2, 'function22'); CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF record AS ' DECLARE rec record; BEGIN FOR rec IN SELECT * FROM test WHERE a = $1 LOOP RAISE NOTICE ''a = %, b = %'',rec.a, rec.b; END LOOP; RETURN rec; END; ' LANGUAGE 'plpgsql'; SELECT * FROM myfunc(1) AS t(a integer, b text); NOTICE: a = 1, b = function1 NOTICE: a = 1, b = function11 a | b ---+--- (0 rows) CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF record AS ' SELECT * FROM test WHERE a = $1; ' LANGUAGE 'sql'; SELECT * FROM myfunc(1) AS t(a integer, b text); a | b ---+ 1 | function1 1 | function11 (2 rows) Regards, Masaru Sugawara ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Does setof record in plpgsql work well in 7.3?
On Sun, 29 Sep 2002 13:42:43 +0200 Grant Finnemore [EMAIL PROTECTED] wrote: Note the use of the RETURN NEXT rec line in the body of the for loop, and also the RETURN null at the end. It is also possible to create typed returns, so in this case, in the declare body, the following would be valid. DECLARE rec test%ROWTYPE; The function definition then becomes:- CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF test ... Thank you for your useful info. the previous function turned out to work correctly by using RETURN NEXT rec. And, I found out that plpgsql was able to nest one. -- for example CREATE OR REPLACE FUNCTION myfunc(integer) RETURNS SETOF record AS ' DECLARE rec1 record; rec2 record; rec3 record; BEGIN SELECT INTO rec1 max(a) AS max_a FROM test; FOR rec2 IN SELECT * FROM test WHERE a = $1 LOOP SELECT INTO rec3 * FROM (SELECT 1::integer AS a, ''test''::text AS b) AS t; RETURN NEXT rec3; rec2.a = rec2.a + rec3.a + rec1.max_a; RETURN NEXT rec2; END LOOP; RETURN NEXT rec3; RETURN; END; ' LANGUAGE 'plpgsql'; SELECT * FROM myfunc(1) AS t(a integer, b text); a | b ---+ 1 | test 5 | function1 1 | test 5 | function11 1 | test (5 rows) Regards, Masaru Sugawara ---(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] About connectby() again
On Thu, 26 Sep 2002 16:32:08 -0700
Joe Conway [EMAIL PROTECTED] wrote:
Masaru Sugawara wrote:
The previous patch fixed an infinite recursion bug in
contrib/tablefunc/tablefunc.c:connectby. But, other unmanageable error
seems to occur even if a table has commonplace tree data(see below).
I would think the patch, ancestor check, should be
if (strstr(branch_delim || branchstr-data || branch_delim,
branch_delim || current_key || branch_delim))
This is my image, not a real code. However, if branchstr-data includes
branch_delim, my image will not be perfect.
Good point. Thank you Masaru for the suggested fix.
Attached is a patch to fix the bug found by Masaru. His example now produces:
regression=# SELECT * FROM connectby('connectby_tree', 'keyid',
'parent_keyid', '11', 0, '-') AS t(keyid int, parent_keyid int, level int,
branch text);
keyid | parent_keyid | level | branch
---+--+---+--
11 | | 0 | 11
10 | 11 | 1 | 11-10
111 | 11 | 1 | 11-111
1 | 111 | 2 | 11-111-1
(4 rows)
While making the patch I also realized that the no show branch form of the
function was not going to work very well for recursion detection. Therefore
there is now a default branch delimiter ('~') that is used internally, for
that case, to enable recursion detection to work. If you need a different
delimiter for your specific data, you will have to use the show branch form
of the function.
If there are no objections, please apply. Thanks,
I have no objection to your internally adding strings to detect a recursion.
And I agree with your definition--the default delimiter is a tilde.
Thanks a lot.
Regards,
Masaru Sugawara
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] About connectby() again
On Sat, 07 Sep 2002 10:21:21 -0700
Joe Conway [EMAIL PROTECTED] wrote:
I just sent in a patch using the ancestor check method. It turned out
that the performance hit was pretty small on a moderate sized tree.
My test case was a 22 record bill-of-material table. The tree built
was 9 levels deep with about 3800 nodes. The performance hit was only
about 1%.
The previous patch fixed an infinite recursion bug in
contrib/tablefunc/tablefunc.c:connectby. But, other unmanageable error
seems to occur even if a table has commonplace tree data(see below).
I would think the patch, ancestor check, should be
if (strstr(branch_delim || branchstr-data || branch_delim,
branch_delim || current_key || branch_delim))
This is my image, not a real code. However, if branchstr-data includes
branch_delim, my image will not be perfect.
-- test connectby with int based hierarchy
DROP TABLE connectby_tree;
CREATE TABLE connectby_tree(keyid int, parent_keyid int);
INSERT INTO connectby_tree VALUES(11,NULL);
INSERT INTO connectby_tree VALUES(10,11);
INSERT INTO connectby_tree VALUES(111,11);
INSERT INTO connectby_tree VALUES(1,111);
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', '11', 0, '-')
AS t(keyid int, parent_keyid int, level int, branch text)
ERROR: infinite recursion detected
Regards,
Masaru Sugawara
---(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] About connectby() again
On Fri, 27 Sep 2002 02:02:49 +0900
I wrote [EMAIL PROTECTED] wrote:
On Sat, 07 Sep 2002 10:21:21 -0700
Joe Conway [EMAIL PROTECTED] wrote:
I just sent in a patch using the ancestor check method. It turned out
that the performance hit was pretty small on a moderate sized tree.
My test case was a 22 record bill-of-material table. The tree built
was 9 levels deep with about 3800 nodes. The performance hit was only
about 1%.
The previous patch fixed an infinite recursion bug in
contrib/tablefunc/tablefunc.c:connectby. But, other unmanageable error
seems to occur even if a table has commonplace tree data(see below).
I would think the patch, ancestor check, should be
if (strstr(branch_delim || branchstr-data || branch_delim,
branch_delim || current_key || branch_delim))
This is my image, not a real code. However, if branchstr-data includes
^
keyid or parent_keyid
branch_delim, my image will not be perfect.
-- test connectby with int based hierarchy
DROP TABLE connectby_tree;
CREATE TABLE connectby_tree(keyid int, parent_keyid int);
INSERT INTO connectby_tree VALUES(11,NULL);
INSERT INTO connectby_tree VALUES(10,11);
INSERT INTO connectby_tree VALUES(111,11);
INSERT INTO connectby_tree VALUES(1,111);
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', '11', 0, '-')
AS t(keyid int, parent_keyid int, level int, branch text)
ERROR: infinite recursion detected
Regards,
Masaru Sugawara
---(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
Regards,
Masaru Sugawara
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] About connectby()
On Sat, 07 Sep 2002 10:26:36 -0700
Joe Conway [EMAIL PROTECTED] wrote:
OK -- patch submitted to fix this. Once the patch is applied, this case
gives:
test=# SELECT * FROM connectby('connectby_tree', 'keyid',
'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level
int, branch text);
ERROR: infinite recursion detected
Thank you for your patch.
If you specifically limit the depth to less than where the repeated key
is hit, everything works as before:
And I also think this approach is reasonable.
test=# SELECT * FROM connectby('connectby_tree', 'keyid',
'parent_keyid', '2', 4, '~') AS t(keyid int, parent_keyid int, level
int, branch text);
keyid | parent_keyid | level | branch
---+--+---+-
2 | | 0 | 2
4 |2 | 1 | 2~4
6 |4 | 2 | 2~4~6
8 |6 | 3 | 2~4~6~8
5 |2 | 1 | 2~5
9 |5 | 2 | 2~5~9
10 |9 | 3 | 2~5~9~10
11 | 10 | 4 | 2~5~9~10~11
(8 rows)
Thanks for the feedback!
Joe
Regards,
Masaru Sugawara
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] About connectby()
Now I'm testing connectby() in the /contrib/tablefunc in 7.3b1, which would be a useful function for many users. However, I found the fact that if connectby_tree has the following data, connectby() tries to search the end of roots without knowing that the relations are infinite(-5-9-10-11-9-10-11-) . I hope connectby() supports a check routine to find infinite relations. CREATE TABLE connectby_tree(keyid int, parent_keyid int); INSERT INTO connectby_tree VALUES(1,NULL); INSERT INTO connectby_tree VALUES(2,1); INSERT INTO connectby_tree VALUES(3,1); INSERT INTO connectby_tree VALUES(4,2); INSERT INTO connectby_tree VALUES(5,2); INSERT INTO connectby_tree VALUES(6,4); INSERT INTO connectby_tree VALUES(7,3); INSERT INTO connectby_tree VALUES(8,6); INSERT INTO connectby_tree VALUES(9,5); INSERT INTO connectby_tree VALUES(10,9); INSERT INTO connectby_tree VALUES(11,10); INSERT INTO connectby_tree VALUES(9,11);-- infinite Regards, Masaru Sugawara ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
