Re: [HACKERS] Error when comparing an integer to an empty string.
i compared an integer to an empty string, i ran in an error. Is this a bug or a feature of the new 7.3 version ? Is there a purpose ? What number do you expect '' to represent? Probably you either want to use: = '0' or is null depending on what you are really trying to do. It's because it comes from a perl building of the request. Typically : '$youpee' When $youpee is undef, it's no problem in 7.2, and the request returns false. Now it raises an error. (Ok, i've understood it was on purpose ; i give these info only for the background :-) Best regards, David -- [EMAIL PROTECTED] ---(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] 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] Optimizer boolean syntax
On Thu, 21 Nov 2002, Stephan Szabo wrote: On Thu, 21 Nov 2002, Christopher Kings-Lynne wrote: col isn't of the general form indexkey op constant or constant op indexkey which I presume it's looking for given the comments in indxpath.c. I'm not sure what the best way to make it work would be given that presumably we'd want to make col IS TRUE/FALSE use an index at the same time (since that appears to not do so as well). Not that I see the point of indexing booleans, but hey :) also, in reference to my last message, even if the % was 50/50, if the table was such that the bool was in a table next to a text field with 20k or text in it, an index on the bool would be much faster to go through than to seq scan the table. Hmmm...I'm not sure about that. Postgres's storage strategry with text will be to keep it in a side table (or you can use ALTER TABLE/SET STORAGE) and it will only be retrieved if it's in the select parameters. True, but replace that text with 1500 integers. :) The only problem with the partial index solution is that it seems to still only work for the same method of asking for the result, so if you make an index where col=true, using col IS TRUE or col in a query doesn't seem to use it. True. I always use the syntax: select * from table where field IS TRUE OR IS FALSE for consistency. ---(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] nested transactions
Bruce Momjian wrote: I am going to work on nested transactions for 7.4. If you're going to do a lot of reworking of how transactions are handled, maybe this is a good time to beg for cursors that stay open across commits. It looks like the JDBC driver is moving to using cursors with ResultSet.CLOSE_CURSORS_AT_COMMIT, for the advantage of not having to fetch the entire result immediately and hold it in memory. If this were implemented, the same could be done for ResultSet.HOLD_CURSORS_OVER_COMMIT, which I think a lot of JDBC code needs. Thanks, Scott ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
quote_ident and schemas (was Re: [HACKERS] connectby with schema)
Masaru Sugawara wrote:
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
I've tracked this down to the fact that connectby does a quote_ident on the
provided relname, and in quote_ident, (quote_ident_required(t)) ends up being
true. The problem will occur even with a simple query:
test=# SELECT id, parent_id FROM ms.test WHERE parent_id = '101' AND id IS NOT
NULL;
id | parent_id
+---
(0 rows)
test=# SELECT id, parent_id FROM ms.test WHERE parent_id = '101' AND id IS
NOT NULL;
ERROR: Relation ms.test does not exist
But this is not the behavior for unqualified table names:
test=# select * from foo;
f1
1
(1 row)
test=# select * from foo;
f1
1
(1 row)
Is quote_ident_required incorrectly dealing with schemas?
Thanks,
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: quote_ident and schemas (was Re: [HACKERS] connectby with schema)
Joe Conway wrote:
Is quote_ident_required incorrectly dealing with schemas?
Sorry to reply to myself, but another related question; shouldn't the
following produce Ms.Test?
test=# select quote_ident('Ms.Test');
quote_ident
-
Ms.Test
(1 row)
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: quote_ident and schemas (was Re: [HACKERS] connectby with schema)
Joe Conway [EMAIL PROTECTED] writes:
Joe Conway wrote:
Is quote_ident_required incorrectly dealing with schemas?
Sorry to reply to myself, but another related question; shouldn't the
following produce Ms.Test?
test=# select quote_ident('Ms.Test');
quote_ident
-
Ms.Test
(1 row)
No, it should not. If it did, it would fail to cope with tablenames
containing dots.
Since connectby takes a string parameter (correct?) for the table name,
my advice would be to have it not do quote_ident, but instead expect the
user to include double quotes in the string value if dealing with
mixed-case names. Compare the behavior of nextval() for example:
regression=# select nextval('Foo.Bar');
ERROR: Namespace foo does not exist
regression=# select nextval('Foo.Bar');
ERROR: Namespace Foo does not exist
regression=# select nextval('Foo.Bar');
ERROR: Relation Foo.Bar does not exist
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Interesting thought from an article about Sun technologies
Hi everyone, Was just reading an article regarding Sun technologies on TheRegister: http://www.theregister.co.uk/content/53/28259.html *** The real problem with databases is administrative, he argued, where the DBA must do index rebuilds. Clustra had eliminated that problem because it was doing constant indexing. So the GUI has gone, along with the Rebuild button. *** Is Constant indexing something that sounds interesting for us to look at? :-) Regards and best wishes, Justin Clift -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] value a reserved word
I see we just recently made the word value reserved: http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/parser/keywords.c.diff?r1=1.130r2=1.131 I noticed it because it breaks the contrib/tablefunc regression test. ISTM like this will break quite a few applications. Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] value a reserved word
Joe Conway [EMAIL PROTECTED] writes: I see we just recently made the word value reserved: http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/parser/keywords.c.diff?r1=1.130r2=1.131 I noticed it because it breaks the contrib/tablefunc regression test. ISTM like this will break quite a few applications. I'm not thrilled about it either. I wonder whether we could hack up something so that domain check constraints parse VALUE as a variable name instead of a reserved keyword? Without some such technique I think we're kinda stuck, because the spec is perfectly clear about how to write domain check constraints. (And, to be fair, SQL92 is also perfectly clear that VALUE is a reserved word; people griping about this won't have a lot of ground to stand on. But I agree it'd be worth trying to find an alternative implementation that doesn't reserve the keyword.) 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] value a reserved word
Tom Lane kirjutas L, 23.11.2002 kell 03:43: Joe Conway [EMAIL PROTECTED] writes: I see we just recently made the word value reserved: http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/parser/keywords.c.diff?r1=1.130r2=1.131 I noticed it because it breaks the contrib/tablefunc regression test. ISTM like this will break quite a few applications. I'm not thrilled about it either. I wonder whether we could hack up something so that domain check constraints parse VALUE as a variable name instead of a reserved keyword? Without some such technique I think we're kinda stuck, because the spec is perfectly clear about how to write domain check constraints. (And, to be fair, SQL92 is also perfectly clear that VALUE is a reserved word; people griping about this won't have a lot of ground to stand on. But I agree it'd be worth trying to find an alternative implementation that doesn't reserve the keyword.) I've been playing around just a little in gram.y and I think that we are paying too high price for keeping some keywords somewhat reserved. In light of trying to become fully ISO/ANSI compliant (or even savvy ;) could we not make a jump at say 7.4 to having the same set of reserved keywords as SQL92/SQL99 and be done with it? There is an Estonian proverb about futility of cutting off a dogs tail in a small piece at a time which seems to apply well to postgreSQL syntax. --- Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] value a reserved word
Hannu Krosing [EMAIL PROTECTED] writes: In light of trying to become fully ISO/ANSI compliant (or even savvy ;) could we not make a jump at say 7.4 to having the same set of reserved keywords as SQL92/SQL99 and be done with it? I disagree ... especially for SQL99 keywords that we're not even using. Also, SQL99 keywords that are actually only function names would be outright more difficult to reserve than not to reserve... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Fw: Missing file from CVS?
Patch applied. Thanks. --- Al Sutton wrote: Heres a patch which will create the sql_help.h file if it doesn't already exist using an installed copy of perl. I've tested it using perl v5.6.1 from ActiveState and all appears to work. Can someone commit this for me, or throw back some comments. Thanks, Al. --- src/bin/psql/win32.mak 2002/10/29 04:23:30 1.11 +++ src/bin/psql/win32.mak 2002/11/20 19:44:35 @@ -7,14 +7,16 @@ !ENDIF CPP=cl.exe +PERL=perl.exe OUTDIR=.\Release INTDIR=.\Release +REFDOCDIR= ../../../doc/src/sgml/ref # Begin Custom Macros OutDir=.\Release # End Custom Macros -ALL : $(OUTDIR)\psql.exe +ALL : sql_help.h $(OUTDIR)\psql.exe CLEAN : -@erase $(INTDIR)\command.obj @@ -91,3 +93,7 @@ $(CPP) @ $(CPP_PROJ) $ + +sql_help.h: create_help.pl +$(PERL) create_help.pl $(REFDOCDIR) $@ + - Original Message - From: Al Sutton [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 15, 2002 8:48 PM Subject: Missing file from CVS? All, I've just tried to build the Win32 components under Visual Studio's C++ compiler from the win32.mak CVS archive at :pserver:[EMAIL PROTECTED]:/projects/cvsroot and found that the following file was missing; src\bin\psql\sql_help.h I've copied the file from the the source tree of version 7.2.3 and the compile works with out any problems. Should the file be in CVS? Al. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] regression failures
I'm getting lots of regression failures: 25 of 89 tests failed. all pretty much looking like: SELECT '' AS one, o.* FROM OID_TBL o WHERE o.f1 = 1234; ! ERROR: Relation pg_constraint_contypid_index does not exist SELECT '' AS five, o.* FROM OID_TBL o WHERE o.f1 '1234'; I guess a recent change requires an initdb but no change was forced? (...an initdb does in fact fix the problem -- now only the misc test fails) Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
