[BUGS] Can not compile --with-tcl ???
Hello : I have comiple tcl 8.3.2 and tk 8.3.2 complete but I want to compile postgresql 7.1.3 with tcl ./configure --with-tcl --with-tclconfig=/usr/local/tcl8.3.2/unix --with-tkconfig=/usr/local/tk8.3.2/unix make and I got the error gcc -pipe -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -fpic -DPIC -I../../../src/interfaces/libpq -I../../../src/include -c -o pgtcl.o pgtcl.cIn file included from pgtcl.c:19:libpgtcl.h:19: tcl.h: No such file or directoryIn file included from pgtcl.c:20:pgtclCmds.h:17: tcl.h: No such file or directoryIn file included from pgtcl.c:19:libpgtcl.h:21: syntax error before `*'libpgtcl.h:22: syntax error before `*'In file included from pgtcl.c:20:pgtclCmds.h:44: syntax error before `Tcl_Interp'pgtclCmds.h:80: syntax error before `cData'pgtclCmds.h:82: syntax error before `cData'pgtclCmds.h:84: syntax error before `cData'pgtclCmds.h:86: syntax error before `cData'pgtclCmds.h:88: syntax error before `cData'pgtclCmds.h:90: syntax error before `cData'pgtclCmds.h:92: syntax error before `cData'pgtclCmds.h:94: syntax error before `cData'pgtclCmds.h:96: syntax error before `cData'pgtclCmds.h:108: syntax error before `cData'pgtclCmds.h:110: syntax error before `cData'pgtclCmds.h:114: syntax error before `cData'pgtclCmds.h:116: syntax error before `cData'pgtclCmds.h:118: syntax error before `cData'pgtclCmds.h:120: syntax error before `cData'pgtclCmds.h:122: syntax error before `cData'pgtclCmds.h:124: syntax error before `cData'pgtclCmds.h:126: syntax error before `cData'In file included from pgtcl.c:21:pgtclId.h:18: syntax error before `*'pgtclId.h:37: syntax error before `*'pgtclId.h:39: syntax error before `cData'pgtclId.h:40: syntax error before `cData'pgtclId.h:41: syntax error before `cData'pgtclId.h:42: syntax error before `*'pgtclId.h:43: syntax error before `*'pgtclId.h:44: syntax error before `*'pgtclId.h:45: syntax error before `*'pgtclId.h:49: syntax error before `clientData'pgtclId.h:63: syntax error before `Pg_ConnType'pgtclId.h:63: warning: type defaults to `int' in declaration of `Pg_ConnType'pgtclId.h:63: warning: data definition has no type or storage classpgtcl.c:30: syntax error before `*'pgtcl.c:31: warning: no previous prototype for `Pgtcl_Init'pgtcl.c: In function `Pgtcl_Init':pgtcl.c:43: warning: implicit declaration of function `Tcl_CreateCommand'pgtcl.c:43: `interp' undeclared (first use in this function)pgtcl.c:43: (Each undeclared identifier is reported only oncepgtcl.c:43: for each function it appears in.)pgtcl.c:46: `ClientData' undeclared (first use in this function)pgtcl.c:46: syntax error before `0'pgtcl.c:51: syntax error before `0'pgtcl.c:56: syntax error before `0'pgtcl.c:61: syntax error before `0'pgtcl.c:66: syntax error before `0'pgtcl.c:71: syntax error before `0'pgtcl.c:76: syntax error before `0'pgtcl.c:81: syntax error before `0'pgtcl.c:86: syntax error before `0'pgtcl.c:102: syntax error before `0'pgtcl.c:107: syntax error before `0'pgtcl.c:113: syntax error before `0'pgtcl.c:118: syntax error before `0'pgtcl.c:123: syntax error before `0'pgtcl.c:128: syntax error before `0'pgtcl.c:133: syntax error before `0'pgtcl.c:138: syntax error before `0'pgtcl.c:143: syntax error before `0'pgtcl.c:145: warning: implicit declaration of function `Tcl_PkgProvide'pgtcl.c:147: `TCL_OK' undeclared (first use in this function)pgtcl.c:148: warning: control reaches end of non-void functionpgtcl.c: At top level:pgtcl.c:152: syntax error before `*'pgtcl.c:153: warning: no previous prototype for `Pgtcl_SafeInit'pgtcl.c: In function `Pgtcl_SafeInit':pgtcl.c:154: `interp' undeclared (first use in this function)gmake[3]: *** [pgtcl.o] Error 1gmake[3]: Leaving directory `/usr/local/postgresql-7.1.3/src/interfaces/libpgtcl'gmake[2]: *** [all] Error 2gmake[2]: Leaving directory `/usr/local/postgresql-7.1.3/src/interfaces'gmake[1]: *** [all] Error 2gmake[1]: Leaving directory `/usr/local/postgresql-7.1.3/src'gmake: *** [all] Error 2*** Error code 2 thanks ---Young-net Technology Samphone:04-23763808#20e-mail:[EMAIL PROTECTED]
[BUGS] two bugs in 'initdb'
First bug: Cygwin with pgsql version: 7.4.3 ( I have gcc and ipc-daemon2 in my cygwin) When I run ‘initdb –D database’, it shows: The database cluster will be initialized with locale C creating directory database... ok creating directory database/base... ok creating directory database/global... ok creating directory database/pg_xlog... ok creating directory database/pg_clog... ok selecting default max_connections... Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 10 selecting default shared_buffers... Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 Signal 12 50 creating configuration files... ok creating template1 database in database/base/1... Signal 12 initdb: failed initdb: removing data directory "database" 2. I used the ‘initdb’ in pgsql 7.4.3 which I downloaded from postgresql.org: It showed this: The database cluster will be initialized with locale C. creating directory database... ok creating directory database/base... ok creating directory database/global... ok creating directory database/pg_xlog... ok creating directory database/pg_clog... ok selecting default max_connections... 10 selecting default shared_buffers... 50 creating configuration files... ok creating template1 database in database/base/1... FATAL: semctl(35, 16, SETVAL, 536) failed: Invalid argument initdb: failed initdb: removing data directory "database"
[BUGS] BUG #7610: planner get wrong rows estimate with LIKE operator
The following bug has been logged on the website: Bug reference: 7610 Logged by: Sam Wong Email address: s...@hellosam.net PostgreSQL version: 9.2.1 Operating system: Windows 7 64-bit Description: Repro SQL: CREATE TABLE a (id text, primary key (id)); INSERT INTO a SELECT to_char(generate_series, 'FM000') from generate_series(1,100); ANALYZE a; Q1: EXPLAIN ANALYZE SELECT * from a where id like '0005000%'; - Index Only Scan using a_pkey on a (cost=0.00..9.37 rows=100 width=8) (actual time=0.170..0.173 rows=1 loops=1) Index Cond: ((id >= '0005000'::text) AND (id < '0005001'::text)) Filter: (id ~~ '0005000%'::text) Heap Fetches: 1 Total runtime: 0.229 ms (5 rows) Q2: EXPLAIN ANALYZE SELECT * from a where id >= '0005000' and id < '0005001'; - Index Only Scan using a_pkey on a (cost=0.00..9.37 rows=1 width=8) (actual time=0.027..0.028 rows=1 loops=1) Index Cond: ((id >= '0005000'::text) AND (id < '0005001'::text)) Heap Fetches: 1 Total runtime: 0.072 ms (4 rows) Problems: * For Q1, the planner incorrectly estimates that there will be 100 rows. For Q2, it gives a correct estimation. * My actual problem in the production is that - because of the much larger estimation, it prefers a merge/hash join in the later stage of a complex query, instead of a nested loop. The outcome is that query tooks 10 seconds instead of 100ms. Observations: * Q1 and Q2 should be the logically identical. The psql thinks the same (refer to the Index Cond in the anazyle output) * The analyze output says that Q1 not only has the same index condition of Q2, but with an additional filter, yet surprisingly it is estimated to have more rows than Q2. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4327: Primary key not refresh after cascaded deleted
The following bug has been logged online: Bug reference: 4327 Logged by: Sam Wong Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3.1 Operating system: Linux Debian Description:Primary key not refresh after cascaded deleted Details: Say, I have two table A and B. That A has one column: A_primary_col ...and keys: PRIMARY KEY (A_primary_col) That B has two columns: B_primary_col, A_reference ...and keys: PRIMARY KEY (b_primary_col) FOREIGN KEY (A_reference) REFERENCES A(A_primary_col) ON DELETE CASCADE Now I inserted the following records into into A: 1 2 3 into B: A,1 B,2 C,3 Now I delete all records from A: DELETE FROM A; so that both table is empty now. Now when I try to insert the following: into A: 4 Then when I try to insert the following into B: A,4 Expected Behavior: it will insert with no problem Actual Behavior: it will say duplicate key value violates unique constraint "B_primary_column_pkey". Workaround: Analyze the table B. I believe that's a bug that the primary key index is not updated accordingly? Thanks. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Help me to solve this problem!
Great day to you!
I tried to install postgresql 8.3.7 on Linux RedHat 7.2 platform.
Process of "configure" ends normally,
process of "gmake" ends with this output of errors:
{
gmake[3]: Entering directory
`/usr/home/install/postgresql-8.3.7/src/interfaces/
libpq'
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline
-fno-strict-aliasing
-fpic -shared -Wl,-soname,libpq.so.5 -Wl,--version-script=exports.list
fe-aut
h.o fe-connect.o fe-exec.o fe-misc.o fe-print.o fe-lobj.o fe-protocol2.o
fe-prot
ocol3.o pqexpbuffer.o pqsignal.o fe-secure.o md5.o ip.o wchar.o encnames.o
noblo
ck.o pgstrcasecmp.o thread.o strlcpy.o -L../../../src/port -lcrypt
-Wl,-rpath,'
/usr/home/pgsql/lib' -o libpq.so.5.1
/usr/bin/ld:exports.list:1: parse error in VERSION script
collect2: ld returned 1 exit status
gmake[3]: *** [libpq.so.5.1] Error 1
gmake[3]: Leaving directory
`/usr/home/install/postgresql-8.3.7/src/interfaces/l
ibpq'
gmake[2]: *** [all] Error 2
gmake[2]: Leaving directory `/usr/home/install/postgresql-8.3.7/src/interfaces'
gmake[1]: *** [all] Error 2
gmake[1]: Leaving directory `/usr/home/install/postgresql-8.3.7/src'
gmake: *** [all] Error 2
}
If you can, help me to resolve this problem!
Thanks for attention!
--
Re: [BUGS] BUG #5015: MySQL migration wizard does not start
On Wed, Aug 26, 2009 at 02:55:37PM +, Ken Smith wrote: > I also noted > that if I try to run from the commandline that you use '/' in some of the > program paths causing the OS to say it cannot find the file - "C:/Program > Files/Java/jdk1.5.0_16/jre/bin/java.exe". I'm pretty sure that Windows has known about forward slashes being path delimiters since Windows 95 and the start of Win32. I know I normally use forward slashes in XP when I'm at work! -- Sam http://samason.me.uk/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] inconsistent composite type null handling in plpgsql out variable
On Fri, Aug 28, 2009 at 02:06:02PM -0400, Merlin Moncure wrote: > 3) If we decide the sql standard is correct, so that (null, null) is > null == true, then we should observe rule 1 and make things work in > consistent way. This means, for example, that null::foo and (null, > null)::foo should not be distinct. The more awkward case (to me anyway) is that the standard says (1,NULL) IS NULL should evaluate to TRUE. I'd never noticed the ROW / RECORD dichotomy before; could one of these be made SQL compatible and the other use more sane semantics? -- Sam http://samason.me.uk/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] inconsistent composite type null handling in plpgsql out variable
On Mon, Aug 31, 2009 at 07:26:59PM +0200, Pavel Stehule wrote: > 2009/8/31 Sam Mason : > > The more awkward case (to me anyway) is that the standard says (1,NULL) > > IS NULL should evaluate to TRUE. > > what? > > only (NULL, NULL) IS NULL is true Bah, sorry you're right! I was rattling my favorite tin and getting mixed up with the behavior with IS NOT NULL, the negation of which would say this row is null. I.e: SELECT NOT (1,NULL) IS NOT NULL; evaluates to TRUE. I think the consensus is that we should continue to follow the spec on this, but I was getting confused as to which operator contains the EXISTS and FORALL operator. I.e. a value "v" IS NULL iff all elements of "v" are not 'the null value', whereas "v" IS NOT NULL iff an element of "v" is 'the null value'. > p.s. what isn't consistent (maybe - there are more possible > interpretations) is > > (NULL, NULL) IS DISTINCT FROM NULL is true Yup, I'd agree with Merlin that a ROW consisting entirely of 'null values' should itself be 'the null value' (to use the terminology from the copy of the SQL spec I'm reading). I think this should also work recursively: SELECT ROW(ROW(NULL)) IS DISTINCT FROM NULL; should return FALSE, in my understanding. -- Sam http://samason.me.uk/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is "char"
On Tue, Sep 01, 2009 at 04:36:25PM +, Joseph Shraibman wrote: > Description:CASE returns ELSE value always when type is "char" I think it's just silently truncating the literal to a single character. > [local]:playpen=> select c.relkind, CASE c.relkind WHEN 'r' THEN 'table' > WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' > THEN 'special' WHEN 'c' THEN 'composite type' WHEN 't' THEN 'toast' ELSE > c.relkind > playpen-> END from (select 'r'::"char" AS relkind) c; > relkind | relkind > -+- > r | t Here, 'r' maps to the "char" literal 'table' which PG interprets as the value 't'--i.e. PG silently chops of the 'able'. The bug would seem to be in your code, but PG could maybe throw an error to tell you this is what is happening? A possible fix would be to have your ELSE clause as: c.relkind::text As that way the other branches would be interpreted as text and they wouldn't be getting chopped off along the way. -- Sam http://samason.me.uk/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is "char"
On Tue, Sep 01, 2009 at 05:49:25PM +0100, Sam Mason wrote:
> PG could maybe throw an error to tell you this is
> what is happening?
Would something like the included patch be accepted?
--
Sam http://samason.me.uk/
*** src/backend/utils/adt/char.c~ 2009-01-01 17:23:49.0 +
--- src/backend/utils/adt/char.c 2009-09-02 10:11:13.0 +0100
***
*** 34,39
--- 34,45
{
char *ch = PG_GETARG_CSTRING(0);
+ if (ch[1])
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("only a single character is supported in \"char\" literals: \"%s\"",
+ ch)));
+
PG_RETURN_CHAR(ch[0]);
}
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is"char"
On Wed, Sep 02, 2009 at 03:50:05PM +0100, Greg Stark wrote: > Perhaps we should stop thinking of "unknown" as, er, "unknown" and > think of it as "text literal". A text literal has implicit casts to > every data type but a normal text string has to be explicitly cast. How does that help things? You seem to be keeping the semantics and only changing the name, when it's the semantics that you seem to be complaining about. I'm pretty sure it's correct to treat it as "unknown", other type-systems do this and it all works well. The thing that makes things ambiguous is the ad-hoc polymorphism that exists in function calls and operators. With PG's type system you know almost nothing about any types involved in an arbitrary expression, operators are better than functions (given the types of the arguments you know the return type, with default parameters even this knowledge doesn't exist with functions) but still leave things far too open to have any rigor without spelling out types in full everywhere. For example: CREATE FUNCTION add(int,int) RETURNS int LANGUAGE sql AS $$ SELECT $1 + $2; $$; CREATE FUNCTION add(int,int,int DEFAULT NULL) RETURNS text LANGUAGE sql AS $$ SELECT ($1 + $2)::text; $$; What type should it attribute to the result of: SELECT add(1,2); In fact it doesn't seem to want to play ball at all. Even given the apparently unambiguous: SELECT 1+add(1,2); or SELECT 'hi'||add(1,2); It doesn't get anywhere. No need for "text 'hi'" in the second one because || isn't defined for values of integer type. -- Sam http://samason.me.uk/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is"char"
On Wed, Sep 02, 2009 at 12:36:00PM -0400, Robert Haas wrote: > On Wed, Sep 2, 2009 at 11:55 AM, Sam Mason wrote: > > In fact it doesn't seem to want to play ball at all. Even given the > > apparently unambiguous: > > > > SELECT 1+add(1,2); > > or > > SELECT 'hi'||add(1,2); > > > > It doesn't get anywhere. No need for "text 'hi'" in the second one > > because || isn't defined for values of integer type. > > Right. This is exactly the sort of thing that languages with real > type inference have no problem handling. Of course, ML for example > doesn't allow overloading precisely because (AIUI) it makes type > inference difficult. It would be awesome if we could make this work > though. Difficult, but not intractable. Haskell has done this sort of thing for quite a while; although it handles ad-hoc polymorphism differently than PG does. You basically end up saying how much polymorphism you want to allow for each function, for example the return type of an operator (in PG) is determined exactly by the type of its arguments. In Haskell you would have the power to say, if you so wanted, that the type of an operator's RHS is determined exactly by the type of its LHS and return type, or even, in the most general case, that it's parametrized over all three types. Obviously the more types you leave free the more typing you have to do specifying all the types as the type-inference has less leverage to work with. I've been trying to think about how to apply a more modern type system to PG for a while and hence my comments about how things like NULL rows should be handled are based on this and may come across as rather dogmatic sometimes, it's about the only way I can get things to hold together without introducing much more complexity than seems strictly necessary. -- Sam http://samason.me.uk/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is"char"
On Wed, Sep 02, 2009 at 01:19:07PM -0400, Tom Lane wrote: > Sam Mason writes: > > ... For example: > > > CREATE FUNCTION add(int,int) RETURNS int LANGUAGE sql > > AS $$ SELECT $1 + $2; $$; > > > CREATE FUNCTION add(int,int,int DEFAULT NULL) RETURNS text LANGUAGE sql > > AS $$ SELECT ($1 + $2)::text; $$; > > > What type should it attribute to the result of: > > > SELECT add(1,2); > > > In fact it doesn't seem to want to play ball at all. Even given the > > apparently unambiguous: > > > SELECT 1+add(1,2); > > or > > SELECT 'hi'||add(1,2); > > > It doesn't get anywhere. > > Well, no, because our type resolution is bottom-up; it does not consider > context when trying to resolve the overloaded "add()" function > reference. "Unknown" is the only part of the system that allows for any > delay at all in identifying the type of a construct, and even that is > limited to a literal and its first-level surrounding context. OK, I got distracted and my example was bad. > It's interesting that you want to go in 100% the opposite direction from > Kevin, who seems to want to eliminate type inference altogether. Maybe > our current compromise isn't too bad, if it makes everybody unhappy in > opposite directions ;-) The current compromise has worked for a while so there's no immediate reason to change it. I wasn't interpreting Kevin's request directly as I don't think he really means what he's saying and really wants an unambiguous way of writing literals in queries. I think it's easier to get this without specifying types everywhere and introducing constraints elsewhere to maintain what compatibility we've got with the SQL standard. If we did follow Kevin's request directly, should we also be specifying the type of NULL? -- Sam http://samason.me.uk/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is"char"
On Wed, Sep 02, 2009 at 12:54:03PM -0500, Kevin Grittner wrote: > Sam Mason wrote: > > If we did follow Kevin's request directly, should we also be > > specifying the type of NULL? > > I don't *think* the SQL standard requires that, and barring that I > don't see any compelling reason to type NULL. The SQL standard certainly doesn't require it. It's just that you were requiring the types of literals that happened to be enclosed in quotes to have their type ascribed, so why not the NULL literal? > One problem I do see > with the current scheme, however, is that NULL *does* get typed to > text when it makes no sense. In my view, a CASE expression which has > only NULL for its return values, or an abbreviated form of CASE, such > as COALESCE or NULLIF, should be evaluated exactly the same as if they > were replaced by NULL itself. For example, COALESCE(NULL, NULL) > currently yields NULL::text. In my view that's wrong. I view it as a > bug, but that seems to be a hard sell here. Yes, that's because PG does a bottom-up solve of the type constraints. I think it should really result in an unknown type as well. > Likewise, I think that in the query which started this thread, the > cast to "char" is not sensible. I'm not sure how that could be > resolved, but it doesn't seem correct to me. All branches unify with the "char" type (i.e. they're all "char" or unknown) and hence the result of the expression is determined to be of type "char". -- Sam http://samason.me.uk/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is"char"
On Wed, Sep 02, 2009 at 01:27:35PM -0500, Kevin Grittner wrote: > Sam Mason wrote: > > you were requiring the types of literals that happened to be > > enclosed in quotes to have their type ascribed, so why not the NULL > > literal? > > Well, unless things have changed in recent versions of the standard > and I've missed the change, a series of characters enclosed in > apostrophes is what the standard calls a "character string literal" > and defines it to be be related to character based types such as > varchar. As far as I'm aware, considering it to be undefined is a > PostgreSQL extension. If you can point to something in the standard > to show where I'm mistaken, I'll look it over. I'll go looking for > something to back my memories on the topic, too, since my memory seems > to be less reliable than it once was. Sorry, I was referring to your explicit naming of types as in the following: http://archives.postgresql.org/message-id/4a9e337802250002a...@gw.wicourts.gov reading it back again I'm not sure if that's what you meant now. Email is hard work isn't it! -- Sam http://samason.me.uk/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is"char"
On Wed, Sep 02, 2009 at 01:55:28PM -0500, Kevin Grittner wrote: > So the behavior of the "char" type is anomalous in this regard? Other > character-based types behave like varchar (which has the behavior I > would expect here)? That is encouraging. Why isn't the behavior of > "char" in this regard considered a bug to be fixed? I think there are lots of implicit casts going on that muddy the water with respect to what's going on at a type level. Things get promoted to TEXT easily in PG. I posted a patch in this thread: http://archives.postgresql.org/message-id/20090902091654.gl5...@samason.me.uk -- Sam http://samason.me.uk/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is"char"
On Wed, Sep 02, 2009 at 01:37:20PM -0500, Kevin Grittner wrote:
> That still seems to be the case in the draft of the 2003 standard I
> have:
>
> ::=
>
> |
> |
> |
> |
> |
> |
> ::=
> [ ]
> [ ... ]
> [ { [ ... ]
> }... ]
>
> The ball's in your court to show something in the standard to say that
> a character string literal is ever *not* to be taken as a character
> string.
Huh, you're right. I'd always thought '2001-01-01' was a valid date
literal, seems the standard has required it to be prefixed by DATE at
least back to SQL92.
--
Sam http://samason.me.uk/
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is"char"
On Wed, Sep 02, 2009 at 12:54:03PM -0500, Kevin Grittner wrote: > Sam Mason wrote: > > If we did follow Kevin's request directly, should we also be > > specifying the type of NULL? > > I don't *think* the SQL standard requires that, and barring that I > don't see any compelling reason to type NULL. I've just realized that either I'm missing your point entirely (it's happened before :) or this ignores the point entirely. PG wants to assign types to every expression, whether this expression will evaluate to a NULL value at run-time or not is immaterial in this regard. I think SQL wants to do the same, but I don't have as much conviction as Tom here. Once we're ascribing types to expressions then whether it happens to contain the literal "1", "'txt'" or "NULL" we're committed to giving it some type---the only question is which one. We thus need to type expressions consisting of just NULL constants. A fun puzzle to base any inductive solution on is what type to ascribe to the following: CREATE VIEW v (c) AS SELECT NULL; PG allows it, but the resulting view seems somewhat unusable. -- Sam http://samason.me.uk/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is"char"
On Wed, Sep 02, 2009 at 02:41:32PM -0500, Kevin Grittner wrote: > Sam Mason wrote: > > > I'd always thought '2001-01-01' was a valid date literal, seems the > > standard has required it to be prefixed by DATE at least back to > > SQL92. > > Yep. I don't know if it would be remotely feasible, but the > implementation which seems like it would be "standard-safe" but still > give reasonable concessions to those wanting to skip the extra > keystrokes of declaring the type of literals which are not character > based would be to go with the suggestion of having a character string > literal type, and change the semantics such that if there is a valid > interpretation of the statement with the character string literal > taken as text, it should be used; if not, resolve by current "unknown" > rules. Probably not feasible, but it seems likely it would make > everyone reasonably happy if it could be done. Sounds as though that'll introduce more ambiguity into the system than there is already. > That leaves the issue of NULL being forced to type text in the absence > of any type info in CASE, COALESCE, and NULLIF. If there were a way > to say that these could return unknown type, that would be solved. > That doesn't seem as though it would be likely to be massively > difficult, although I could be wrong about that. Would be nice and I'd love it to work like this, but it's quite a big change I think. Currently, once PG has decided on a type it sticks with it against all further evidence. Another example: SELECT NULL AS c UNION SELECT '1' UNION SELECT 2; Once you're doing the above you're into the world of full parametric polymorphism and you're having to do much more complicated things at the type level. When my free time becomes infinite I'll have a chance! -- Sam http://samason.me.uk/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is"char"
On Wed, Sep 02, 2009 at 02:59:54PM -0500, Kevin Grittner wrote: > Sam Mason wrote: > > CREATE VIEW v (c) AS > > SELECT NULL; > > > > PG allows it, but the resulting view seems somewhat unusable. > > I'm not sure whether the only place the standard doesn't require a > cast is on assignment, but this is one place that the standard clearly > does require a cast, and I'm all for that. I'm probably missing something obvious again, but where does it say that? Bear in mind that my simple NULL could be an arbitrarily complex expression, I've just chosen a simple NULL for pedagogic reasons. I can only see a requirement that the chosen type must be compatible. That seems to leave it open to arbitrarily choosing any type in this case. -- Sam http://samason.me.uk/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is "char"
On Fri, Sep 04, 2009 at 10:59:48AM -0500, Kevin Grittner wrote: > Tom Lane wrote: > > I certainly don't want to have "char" emulate the misbegotten > > decision to have explicit and implicit coercions behave differently. > > So it looks to me like the argument to make "char" work like char(1) > > doesn't actually help us much to decide if an error should be thrown > > here or not. On the whole, throwing an error seems better from a > > usability perspective. I'm all for the error being thrown; no particular feelings about whether it only happens during "explicit" casts or everywhere. There's always the substring() function if the user wants it. > I feel that the behavior of "char" in at least this case should match > char(1) (or just plain char): Hum, I'm not sure if that's useful behavior. As far as I can tell, you seem to be wanting in-memory representations of "string like types" to all use the same representation and only use the actual types when saving to/from disk. This would give behavior that is consistent with what you're for asking below. > test=# select case when true then 'xxx' else 'a'::"char" end from t; > case > -- > x > (1 row) With the patch I gave, or something like it, this would throw an error because 'xxx' is being used to initialize a value of "char" type. > test=# select case when true then 'xxx' else 'a'::char(1) end from t; > case > -- > xxx > (1 row) This gives back 'xxx' because the types character and bpchar have an implicit cast defined between them. The result is thus of type bpchar which places no restrictions on the length (that would apply here anyway), with 'a' being cast from character to bpchar implicitly. > test=# select case when true then 'xxx' else 'a'::char end from t; > case > -- > xxx > (1 row) This does the same as above. > Much as the reason for the behavior of "char" may seem clear when > inside the code looking out, it is astonishing for someone writing > application code. I think things would be clearer if an error was thrown in the constructor of "char" types when the length wasn't correct. Implicit casts are muddying the waters as well, which doesn't aid understanding of what's going on here. -- Sam http://samason.me.uk/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is "char"
On Fri, Sep 04, 2009 at 12:26:19PM -0500, Kevin Grittner wrote:
> Sam Mason wrote:
> > Kevin Grittner wrote:
> >> test=# select case when true then 'xxx' else 'a'::"char" end from t;
> >
> > 'xxx' is being used to initialize a value of "char" type.
>
> As I read the semantics of the CASE predicate, it returns one of the
> given values. 'x' is not one of the given values, regardless of type.
You seem to be confused about the difference between literals and
values. Maybe a different example:
SELECT '1'::int;
I get '1' back from that, and not '1'. This is because '1' is
the literal that is parsed into a value of type integer and then the
query is run and this same value is asked to convert itself back into a
literal to be written out to the screen.
Back to your example; you're asking PG to interpret the literal 'xxx'
as a "char" and it does that (but doesn't give any error back when it
chucks data away). This behavior may be confusing because for text
types the literal exactly the same as the value itself, but this is only
a very specific behavior of text types. For example, '{"1"}', '{1}'
and even '{"+001"}' are all literal representations of identical integer
arrays.
> I don't think an error is the right thing, I think returning the
> specified value is the right thing. I don't think it's a good thing
> that the type system decides that the result type for this case
> predicate is "char" and that 'xxx' needs to be coerced to that type.
I fail to see how an error isn't the right thing; if we try with some
other types let see if you think any of these should succeed.
SELECT CASE WHEN TRUE THEN text 'xxx' ELSE 0 END;
SELECT CASE WHEN TRUE THEN text 'xxx' ELSE TRUE END;
SELECT CASE WHEN TRUE THEN text 'xxx' ELSE '{1}'::INT[] END;
SELECT CASE WHEN TRUE THEN text 'xxx' ELSE array [1] END;
"char" is no different other than, by default, it happens to look a lot
like any value of text type. It's a different type (that happens to
have some implicit casts to confuse things) and hence I can't see why
invalid literals should not be thrown out.
--
Sam http://samason.me.uk/
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5028: CASE returns ELSE value always when type is "char"
On Fri, Sep 04, 2009 at 02:01:07PM -0500, Kevin Grittner wrote: > > "char" is no different other than, by default, it happens to look a > > lot like any value of text type. > > So much so that it has the same name as a text type (wrapped in > quotes) and behaves a lot like one: You're getting bitten by implicit casts. Try creating a view of any of your examples and seeing what the resulting types are, or in 8.4 an EXPLAIN VERBOSE seems to show how it's typed the expression. -- Sam http://samason.me.uk/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5053: domain constraints still leak
On Mon, Sep 14, 2009 at 11:16:23AM -0400, Robert Haas wrote: > I haven't read the code in this area, but for what it's worth, I guess > I lean toward the view that treating a row of NULLs as being the same > thing as an undecorated NULL does not make very much sense. I agree; when compared to most languages it doesn't. When compared to the semantics of the other operators in SQL it gets better. I personally think PG should strive to be internally consistent rather than consistency with other (non-SQL based) languages. > If I have > a table row which contains (1, NULL, NULL) and I update the first > column to be NULL, I feel like I now have (NULL, NULL, NULL), not just > NULL. Every other programming language I'm aware of makes this > distinction - for good reasons - and I don't really see any reason why > SQL should do anything different. I'm not aware of any other language that does the automatic "lifting" (to borrow nomenclature from Haskell) that SQL does, allowing NULL appear in *every* type. Java, for example, has null references, but these are very different creatures from nulls in databases--the programmer has to explicitly deal with them all the time and also they only apply to references. Taken another way, each object in a normal imperative language has its own identity, but in a database two rows that "look" the same are the same. Thirdly, IS NULL is defined to look "inside" composite values to see if they're "really" null. Its these differences in semantics that seem to make it all OK. > Under that view, null::test is not itself a test, but denotes the > absence of one. OK, but how can you distinguish NULL from ROW(NULL,NULL)? SELECT v IS NULL, v.a, v.b FROM (SELECT NULL, NULL) v(a,b); Would appear to return the same thing if ROW(NULL,NULL) evaluated to NULL or not. The only time it would show up is when you're trying to save the value into a table and I think this would tend to do the right thing more often. For example: INSERT INTO t (id,rv) SELECT f.id, b FROM foo f LEFT JOIN bar b ON (f.id = b.id); Would fail if any bar's didn't exist, whereas the current behavior is to insert a row with rv containing all null values. You can't test for this case because IS NULL would return the "wrong" thing as it looks inside composites. -- Sam http://samason.me.uk/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5053: domain constraints still leak
On Mon, Sep 14, 2009 at 10:54:07AM -0500, Kevin Grittner wrote: > Sam Mason wrote: > > the deeper problem seems to be that the table was created as: > > > > create table test (a tstdom); > > > > and not as: > > > > create table test (a tstdom not null); > > Given that tstdom is declared as NOT NULL, is this difference > considered a *feature* or is it an implementation quirk? That's why I pointed it out! Based on my reading of the SQL spec (and reading about Codd's descriptions of domains) I'd say it was a bug/implementation quirk. -- Sam http://samason.me.uk/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5053: domain constraints still leak
On Mon, Sep 14, 2009 at 10:22:34AM -0400, Tom Lane wrote: > Robert Haas writes: > > It seems like regardless of this discussion you oughtn't to be able to > > store a NULL into that table column. But maybe I'm just confused. > > The system is relying on the not-unreasonable assumption that if it > extracts a column of type X from someplace, what it has is a valid value > of type X. Yup; the deeper problem seems to be that the table was created as: create table test (a tstdom); and not as: create table test (a tstdom not null); which is how you seem to be treating it. > Depending on what we decide about the whole composite-null > mess, maybe we will be forced to abandon that assumption ... but I'm > sure not going to do so until my back is to the wall. There seems to be a little space yet! This whole issue seems only distantly related to the treatment of null rows to me. I think PG has got its semantics confused along the way somewhere and things need tweaking. The only way I can get it all to work nicely in my head is if ROW(NULL) evaluates to a NULL value (and not a row containing a NULL value, as it does at the moment) and the NULL/NOT NULL constraint on the CREATE DOMAIN is used somehow for the nullness constraint of any columns using this domain. It's the second part that seems to be more critical, but there are various ways of interpreting the meaning. I'm tempted to say that the nullness specified in the domain puts a bound on the amount of nullness available--i.e. it would be impossible to create a nullable column from a domain that specified NOT NULL. The reason it's only a "limit" is that it seems useful to be able to say that a normally nullable domain can't be null for this column in this table. Not sure if this is what people want though. You then get into fun cases like: create domain tstdom as integer; create domain tstdom2 as tstdom; -- Sam http://samason.me.uk/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] should have been HH12:MI, but bug is there anyway
Sorry, I mistyped that query in my bug report, damaging its credibility, but the bug is actually there when you use "MI" for minutes: template1=# select to_char(timestamp '2000-01-01 13:45:00', 'HH12:MI pm'); to_char ------ 01:45 am - Sam Hokin . mailto:[EMAIL PROTECTED] http://www.bsharp.org/sam
[BUGS] bug in to_char(timestamp, text) for times between 12:00 and 14:00
There seems to be a bug in 7.0.2 in the to_char(timestamp,text) function when one uses the "am" or "pm" template for times between 12:00 and 14:00: template1=# select to_char(timestamp '2000-01-01 13:00:00', 'HH12:MM pm'); to_char -- 01:01 am Any times between 12:00 and 14:00 show "am" rather than "pm". It shows "pm" correctly for 14:00 and later. My apologies if you've already addressed this, I haven't searched the lists to see if it's already been reported. - Sam Hokin . mailto:[EMAIL PROTECTED] http://www.bsharp.org/sam
[BUGS] Installation problem on freebsd
Hi,
i have tried to install the postresql 7.0.3 and the
7.1
on my freebsd 4.2 box. However i have found some
problems
with the installation> here is some basic
configuration information
and the error message that i got during making
..
OS: Freebsd 4.2
gmake: 3.79.1
Installation procedure :
$./configure
$gmake
and then i got the message
gmake[4]: Nothing to be done for
`all'.gmake[4]: Leaving directory
`/usr/slocal/src/postgresql-7.1/src/interfaces/libpq'gcc -pipe -O2 -Wall
-Wmissing-prototypes -Wmissing-declarations -I../../../src/interfaces/libpq
-I../../../src/include -c -o pg_dump.o pg_dump.cgcc -pipe -O2
-Wall -Wmissing-prototypes -Wmissing-declarations
-I../../../src/interfaces/libpq -I../../../src/include -c -o
common.o common.cgcc -pipe -O2 -Wall -Wmissing-prototypes
-Wmissing-declarations -I../../../src/interfaces/libpq
-I../../../src/include -c -o pg_backup_archiver.o
pg_backup_archiver.c{standard input}: gcc: Assembler
messages:Internal compiler error: program cc1 got fatal signal
11{standard input}:1711: gmake[3]: Warning: *** [pg_backup_archiver.o] Error
1end of file not at end of a line; newline insertedgmake[3]:*** Deleting
file `pg_backup_archiver.o'gmake[3]: Leaving directory
`/usr/slocal/src/postgresql-7.1/src/bin/pg_dump'gmake[2]: *** [all] Error
2gmake[2]: Leaving directory
`/usr/slocal/src/postgresql-7.1/src/bin'gmake[1]: *** [all] Error
2gmake[1]: Leaving directory `/usr/slocal/src/postgresql-7.1/src'gmake:
*** [all] Error 2
what would be the problem ?
ths
Sam
[BUGS] BUG #2516: group privs do not seem to be honored
The following bug has been logged online:
Bug reference: 2516
Logged by: Sam Howard
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.4 -Deb Etch
Operating system: Linux - Debian Etch
Description:group privs do not seem to be honored
Details:
Trying to implement user level access and security, and am finding
unexpected behavior with respect to group roles.
This sort of user/group structure seems like it should be commonplace, so I
have a hard time believing it is a bug, but I cannot figure out why it is
not working as I expect. Apologies in advance if it is user error. :)
Platform: Debian Etch (testing)
DB: PostgreSQL 8.1.4 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.4
20060507 (prerelease) (Debian 4.0.3-3), pkg version 8.1.4-2
Simple scenerio:
Create a role to own the db objects:
CREATE ROLE dbowner LOGIN
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
Create a group role for the user roles to belong to:
CREATE ROLE db_group
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
Create the application role and add it to the group:
CREATE ROLE appuser LOGIN
ENCRYPTED PASSWORD 'mdblahblahblah'
NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;
GRANT db_group TO appuser;
Create table foo with a serial and varchar column, and matching sequence for
the serial:
CREATE TABLE foo
(
id serial NOT NULL,
data1 varchar(32),
CONSTRAINT foo_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;
ALTER TABLE foo OWNER TO dbowner;
GRANT ALL ON TABLE foo TO dbowner;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE foo TO db_group;
Now, the default privs on the sequence do not include the db_group, so let's
add them now:
GRANT SELECT, UPDATE ON foo_id_seq TO db_group;
With a sample table and associated sequence set up, and group privs to
insert (or update in the case of the sequence), let's try an insert as the
user "appuser":
=> insert into foo (data1) values ('some stuff');
ERROR: permission denied for relation foo
=> \z foo;
Access privileges for database "db"
Schema | Name | Type | Access privileges
+--+---+--
public | foo | table | {dbowner=arwdRxt/dbowner,db_group=arwd/dbowner}
(1 row)
=> \z foo_id_seq;
Access privileges for database "db"
Schema |Name| Type | Access privileges
++--+
public | foo_id_seq | sequence |
{dbowner=arwdRxt/dbowner,db_group=rw/dbowner}
(1 row)
=> \dg;
List of roles
Role name | Superuser | Create role | Create DB | Connections | Member
of
--+---+-+---+-+-
dbowner | no| no | no| no limit|
{db_group}
appuser | no| no | no| no limit|
{db_group}
Based on appuser being in the group role db_group, and db_group having
select, insert, update on the table foo, and select, update on its sequence,
foo_seq_id, I would expect the insert to succeed.
If I specifically grant select, insert, update for the user appuser to the
table foo like:
GRANT SELECT, INSERT, UPDATE ON foo TO appuser;
Then try my INSERT:
=> insert into foo (data1) values ('some stuff');
ERROR: permission denied for sequence foo_id_seq
This continues to make me wonder if the membership of appuser in the group
role db_group is having any effect.
Adding grants to the sequence like:
GRANT SELECT, UPDATE ON foo_id_seq TO appuser;
And then trying the INSERT again:
=> insert into foo (data1) values ('some stuff');
INSERT 0 1
SUCCESS!
Please feel free to direct me to some additional documentation that
addresses this issue, if it is not a bug. I've read over the GRANT manpage
a few times, and I *think* I'm doing it right.
The fact that if I add the specific user rights, it works, makes me hope
it's broken and not me. :)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[BUGS] BUG #3723: dropping an index that doesn't refer to table's columns
The following bug has been logged online: Bug reference: 3723 Logged by: Sam Mason Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.5 Operating system: Linux Description:dropping an index that doesn't refer to table's columns Details: Hi, I've just discovered that an index that doesn't refer to any of its table's columns isn't automatically dropped when its table is. The test case for me is: CREATE TABLE foo ( id INTEGER NOT NULL ); CREATE UNIQUE INDEX foo_id ON foo (id); CREATE UNIQUE INDEX foo_exp ON foo ((1)); DROP TABLE foo; -- foo_id will have gone, but foo_exp will still be there \di foo_id \di foo_exp AndrewSN suggested the following query to show indexes that have missing tables: SELECT indexrelid::regclass FROM pg_index i LEFT JOIN pg_class c ON i.indrelid=c.oid WHERE c.oid IS NULL; He also showed me which system catalogs to change in order to delete these indexes which I'm happy with at the moment. Thanks, Sam p.s. the reason for creating this strange index was to ensure that a maximum of one row was inserted into the table---I can do this different ways for now. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] BUG #3723: dropping an index that doesn't refer to table's columns
On Tue, Nov 06, 2007 at 10:00:43AM -0500, Tom Lane wrote: > Heikki Linnakangas <[EMAIL PROTECTED]> writes: > > Not sure that's enough of a use case to justify not banning it... > > Yeah, it probably is. It's reasonably easy to do this instead: CREATE TABLE foo ( one INTEGER NOT NULL UNIQUE CHECK (one = 1) ); The bug (for me) was that it's possible to get the database into an inconsisant state, with no warning or obvious way to back out. Sam ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #3848: function pg_catalog.substring(date, integer, integer) does not exist
On Tue, Jan 01, 2008 at 08:11:01PM +, Ion wrote: > Email address: [EMAIL PROTECTED] lets hope Ion is subscribed! :) > I tested PostgreSQL 8.3 beta4 with tinyerp and I have this problem: > pg_catalog.substring(date, integer, integer) does not exist This is most likely a bug in tinyerp. substring has only ever been defined for string data types and isn't valid for date types at all. > I have not this problem with postgresql 8.2.5. Prior to PG 8.3, all types would be silently coerced into TEXT types in certain cases, leading to all sorts of strangeness. I've just downloaded the source of tinyerp and had a look though and this practice seems pretty endemic. They should really be using to_char(date_exp, '-MM') instead of substring(date_exp FOR 7) in all their queries. Sam ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #3848: function pg_catalog.substring(date, integer, integer) does not exist
On Tue, Jan 01, 2008 at 04:29:47PM -0500, Tom Lane wrote: > Sam Mason <[EMAIL PROTECTED]> writes: > > I've just downloaded the source of tinyerp and had a look though and > > this practice seems pretty endemic. They should really be using > > to_char(date_exp, '-MM') > > instead of > > substring(date_exp FOR 7) > > in all their queries. > > Or at least explicitly casting the date to text. But your way is > better, since it won't break if the datestyle is something other > than ISO. It also improves code readability is is always a good thing. I've just gone though the code and sent a patch to the tinyerp-devel list with these fixes in. Lets see if it gets incorporated. Sam ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #4035: sql table aliases do not work
On Fri, Mar 14, 2008 at 08:53:08PM +, RGF wrote: > PostgreSQL version: latest do you mean 8.2.6, 8.3.0 or a latest version of some other series? please try a little harder next time!! > Description:sql table aliases do not work they do whenever I use them! by the looks of your SQL you're not even using them where you should be: > Context: SQL statement "update tonodes set tonodes.cost = case when > tonodes.cost is NULL then fromnodes.cost + paths.cost when fromnodes.cost + > paths.cost < tonodes.cost then fromnodes.cost + paths.cost else tonodes.cost > end, tonodes.pathid = paths.pathid from nodes as fromnodes inner join paths > on paths.fromnodeid = fromnodes.nodeid inner join tonodes on tonodes.nodeid > = paths.tonodeid where fromnodes.nodeid = $1 and (tonodes.cost is NULL or > fromnodes.cost + paths.cost < tonodes.cost) and tonodes.calculated = 0" > PL/pgSQL function "dijkstra_resolve" line 53 at SQL statement If we rewrite this to be somewhat readable: update tonodes set tonodes.cost = case when tonodes.cost is NULL then fromnodes.cost + paths.cost when fromnodes.cost + paths.cost < tonodes.cost then fromnodes.cost + paths.cost else tonodes.cost end, tonodes.pathid = paths.pathid from nodes as fromnodes inner join paths on paths.fromnodeid = fromnodes.nodeid inner join tonodes on tonodes.nodeid = paths.tonodeid where fromnodes.nodeid = $1 and (tonodes.cost is NULL or fromnodes.cost + paths.cost < tonodes.cost) and tonodes.calculated = 0; You refer to "tonodes" but never actually say that it's an alias for nodes (I assume, you've not actually said this anywhere). > The tables referenced (nodes and paths) exist and have data. The SQL works > in MS SQL Server 2000 The PG manual[1] has this to say: Some other database systems offer a FROM option in which the target table is supposed to be listed again within FROM. That is not how PostgreSQL interprets FROM. Be careful when porting applications that use this extension. I'd guess this is what MS SQL does. That said, it's easy to rewrite your query to use PG syntax. I've also noticed that your CASE statement is somewhat redundant so I've removed it (it's cases are exactly the same as the WHERE clause). UPDATE nodes f SET cost = f.cost + p.cost, pathid = p.pathid FROM nodes t, paths p WHERE (p.fromnodeid,p.tonodeid) = (f.nodeid,t.nodeid) AND (t.cost IS NULL OR f.cost + p.cost < t.cost) AND t.calculated = 0 AND f.nodeid = $1; Which, to me, is even more readable. For future reference, the pgsql-general mailing list[2] is more appropiate for questions like this. As a side note, do you have exactly one path from each node to another node, or do you run this code several times until it converges on the minimum? In the latter case you'd probably be better off using an aggregation to find the shortest path in a single pass. Sam [1] http://www.postgresql.org/docs/8.3/static/sql-update.html#AEN61013 [2] http://archives.postgresql.org/pgsql-general/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Redundant explicit field name/types description while select from function with return type of record
On Fri, Mar 28, 2008 at 01:43:25PM -0300, Euler Taveira de Oliveira wrote: > [EMAIL PROTECTED] wrote: > >a column definition list is required for functions returning "record" > >It seems a BUG > > > I don't think so. We can say it is a missing feature. As stated in [1], > record types don't have a predefined structure -- they're placeholders. I was having a similar discussion with Gregory Stark about this and hadn't realised that such small amounts of state was recorded with each row. > How do you know the row structure before hand? Its structure can be > changed on-the-fly. Sorry, I don't understand this comment. Could you elaborate? Sam -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4085: No implicit cast after coalesce
On Thu, Apr 03, 2008 at 12:24:17AM +0200, Peter Eisentraut wrote:
> Jeff Dwyer wrote:
> > This works fine:
> > select 1 where current_date between '1900-3-3' and '1900-2-2';
> > This doesn't:
> > select 1 where current_date between coalesce(null,current_date) and
> > coalesce(null, '1900-1-2');
> >
> > This fix works:
> > select 1 where current_date between coalesce(null,current_date) and
> > coalesce(null, date('1900-1-2'));
> >
> > This seems like a bug to me. Why should an explicit cast be necessary after
> > a coalesce?
>
> Because coalesce(null, '1900-1-2') has no other type information attached, so
> it would have picked text by default as result type, and that then clashes
> with the result type of coalesce(null,current_date), which can be derived to
> be date. This is a robustness improvement: 8.2 and earlier would silently
> accept coalesce(null, 'abc') and apply text-semantics comparison.
The types look as though they could be interpreted unambiguously and
correctly very easily. Parametric polymorphism and some basic type
inference would easily be able to resolve this. BETWEEN would have
the following type (very informally presented; lower case characters
stand for type variables, Titlecase for type names, UPPERCASE for
identifiers!):
Boolean (t BETWEEN t AND t)
i.e. when BETWEEN is called all the types must be the same. COALESCE is
also parametrised over a single type:
t COALESCE(t,t)
NULLs could be encoded in the type system in many ways as long it had a
polymorphic type. The type system should realise that "current_date"
is of type Date and because NULL is polymorphic the COALESCEs would
unify, both returning values of type Date, which would in turn unify
with the BETWEEN operator resulting in a value of BOOLEAN type, which is
exactly what the WHERE clause expects.
This sort of type inference has been known (and extensively studied)
for about 50 years now, it always surprises me how little it's known
outside the functional programming community (ML and Haskell being the
old guard). Apparently, according to the fountain of wisdom that is
Wikipedia, It's finally starting to break into very mainstream languages
like the next versions of VB9 and C#3.
Moving an existing implementation over to a new type system is an
entirely non-trivial matter though!
Sam
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Inconsistent shift operator
On Sat, Apr 19, 2008 at 11:26:57AM -0400, Tom Lane wrote:
> Roman Kononov <[EMAIL PROTECTED]> writes:
> > The below test cases show the obvious inconsistency between different
> > integer types.
>
> [ shrug... ] The << and >> operators just expose the behavior of the
> local C compiler's shift operators, and it's clearly stated in the C
> spec that shifting by more than the word width produces unspecified
> results.
I thought that getting the correct answer was more important than
getting the "wrong" answer quickly. The current code also introduces
its own set of strangeness in the 16 bit case on my x86_64 box. It does
something closer to:
int16 shl (int val, int n) {
n %= 32;
return n < 16 ? val << n : 0;
}
This is exactly what the code says, it's just the resulting semantics
aren't very nice for the user.
Wouldn't it be easy to put some code like this in:
if (arg2 < 16)
return PG_RETURN_INT16(arg1 << arg2);
return PG_RETURN_INT16(0);
People would have one less platform specific weirdo to worry about.
As an aside, I thought it would be interesting to see what MySQL did and
it seems to check for and handle this case--albeit only the 64bit case,
but as far as I can tell it only really knows about "long long" ints.
Sam
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Inconsistent shift operator
On Sun, Apr 20, 2008 at 12:27:38PM -0400, Tom Lane wrote: > Sam Mason <[EMAIL PROTECTED]> writes: > > Wouldn't it be easy to put some code like this in: > > if (arg2 < 16) > > return PG_RETURN_INT16(arg1 << arg2); > > return PG_RETURN_INT16(0); > > This is a straw man. It covers *one* of the behaviors left undefined > by the C standard. I quote from C99: [...] wow, I never realised how little semantics C actually defines. I'm a fan of formally defined semantics and the above just seems like a big cop-out. The case of E1 being negative seems completely implementation defined! > We are shifting signed types so we are exposed to every one of these > unspecified behaviors. In particular, since we don't know whether the > behavior of >> will be zero-fill or sign-fill, it's not going to be > exactly trivial to make a consistent extension of it to shift values > greater than the word width. About the only reasonable thing I can think of that would remain within C's spec (at least with respect of shifting) would be to always treat E1 as an unsigned value. This would allow it to be used consistently with the other bit-wise operators, but would cause any non-bitwise interpretation of the result to become implementation defined. Not very nice. > By the time you get done replicating all this for the int2, int4, > and int8 shift operators, it's not looking like such a small patch > anymore. And I still find the premise entirely unconvincing. After an afternoon of getting utterly bogged down looking into what other languages do and getting very distracted with ring theory I'm tempted to reluctantly agree. Maybe the warning about floats could be extended to cover the shift operators as well. Maybe: The bit shift operators only return consistent results when both the RHS is within the bit-size of the arguments' data type and the LHS is positive, in all other cases the behaviour is platform specific. I think that's technically correct, but seems to come across as very pessimistic. > Maybe the user *wants* to see the local behavior of shift, whatever > it might be. It's certainly not impossible that we'd break applications > that worked fine before (at least on the hardware they were being > used on). Yes. It always surprises me how hard getting formal semantics into an existing language seems to be. Sam -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4114: Inconsistent shift operator
On Sun, Apr 20, 2008 at 08:17:50PM +0200, Zdenek Kotala wrote: > Roman Kononov napsal(a): > >The below test cases show the obvious inconsistency between different > >integer types. > > It seems to be OK regarding how C shift operator works. Try Yes, but I interpret this behaviour as not being very useful for people writing SQL code that uses the shift operators. C is a very low level language and you almost always end up writing platform specific code, SQL is supposed to be much higher level and should abstract away system specific differences. I've not been able to think of a nice way of doing this though. Sam -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] PB with postgresql 7.4
On Mon, Apr 28, 2008 at 02:14:17PM +0200, Nicolas ZABOURI wrote: > My server is down and all my dump aren't make. > How can make for re-install the data on a new install of the server ? If you can get another copy of 7.4 installed that's built for the same processor as the database was originally running from and then point it at your old data directory it should be able to use it. I.e. something like: postmaster-7.4 -D /path/to/old/data It is probably worth working with a copy of the data just in case something strange happens. If you want to stay with 7.4 then you're done, if you want to upgrade to a newer release (i.e. 8.2 or 8.3) of PG then you should do a pg_dump from the newer version's dump program. > I don't find a forum who can speak about this ! The [EMAIL PROTECTED] mailing list is probably the best place to ask these sorts of questions. They are described here: http://www.postgresql.org/community/lists/ Sam -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4207: EXTRACT function
On Thu, May 29, 2008 at 07:01:46AM +, Jeferson Kasper wrote:
> the EXTRACT(field FROM source) function allows to use some kind of 'fields',
> and i need to know if a time (like '07:00') is after the midday(12:00) or
> not.
> I know i can resolve it implementing some function like "select
> ('07:00'<'12:00')".. i just want to know if is difficult to implement a new
> field called 'am', to use like.
>
> select extract(AM from '07:00');
What's wrong with just doing:
SELECT extract(hour FROM date) < 12;
Sam
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] drop tempoary table VERY slow
And when I do drop a table CPU usage goes to 99% on one CPU. Sam -Original Message-From: Sam Liddicott Sent: 31 May 2002 10:57To: '[EMAIL PROTECTED]'Subject: drop tempoary table VERY slow I have a DB where this: select 1 into temporary table x; runs quickly, but drop table x; takes many seconds to run. I don't know why. But: begin; select 1 into temporary table x; abort; is very quick. Note the slow dropping applies to automatic dropping of temporary tables when the connection is closed. I now "abort" all my query sessions for speed. I cannot reproduce this on "template1" and it has not always been the case for my DB. Sam Samuel LiddicottSupport Consultant[EMAIL PROTECTED] Direct Dial: +44 (0)113 367 4523Fax: +44 (0)113 367 4680Switchboard: +44 (0)113 367 4600 Ananova LimitedMarshall MillMarshall StreetLeedsLS11 9YJ http://www.ananova.com Registered Office:St James CourtGreat Park RoadAlmondsbury ParkBradley StokeBristol BS32 4QJRegistered in England No.2858918 The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you receive this in error, please contact the sender and delete the material from any computer.
[BUGS] drop tempoary table VERY slow
I have a DB where this: select 1 into temporary table x; runs quickly, but drop table x; takes many seconds to run. I don't know why. But: begin; select 1 into temporary table x; abort; is very quick. Note the slow dropping applies to automatic dropping of temporary tables when the connection is closed. I now "abort" all my query sessions for speed. I cannot reproduce this on "template1" and it has not always been the case for my DB. Sam Samuel LiddicottSupport Consultant[EMAIL PROTECTED] Direct Dial: +44 (0)113 367 4523Fax: +44 (0)113 367 4680Switchboard: +44 (0)113 367 4600 Ananova LimitedMarshall MillMarshall StreetLeedsLS11 9YJ http://www.ananova.com Registered Office:St James CourtGreat Park RoadAlmondsbury ParkBradley StokeBristol BS32 4QJRegistered in England No.2858918 The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you receive this in error, please contact the sender and delete the material from any computer.
Re: [BUGS] drop tempoary table VERY slow
> -Original Message- > From: Andrew McMillan [mailto:[EMAIL PROTECTED]] > Sent: 02 June 2002 11:52 > To: Sam Liddicott > Cc: [EMAIL PROTECTED] > Subject: Re: [BUGS] drop tempoary table VERY slow > > > On Fri, 2002-05-31 at 22:28, Sam Liddicott wrote: > > And when I do drop a table CPU usage goes to 99% on one CPU. > > When did you last do a vacuum? If you are adding and > dropping temporary > tables a lot, perhaps you should vacuum pg_class and > pg_attribute often > as well. I do a vacuum analyse every night on that whole DB, cron logs show pg_ tables are also vacummed, taking 97 seconds for pg_class and 463 seconds for pg_attribute. The DB size is about 10G and we do about 16,000 temporary tables per day. The whole thing has become enourmously faster since we enclosed the queries in an aborting transaction. (If you are interested it serves Ananova TV listings at http://www.ananova.com/tv_listings/_tv_full_listings.html) Sam ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] drop tempoary table VERY slow
> -Original Message- > From: Andrew McMillan [mailto:[EMAIL PROTECTED]] > Sent: 05 June 2002 12:58 > To: Sam Liddicott > Cc: [EMAIL PROTECTED] > Subject: RE: [BUGS] drop tempoary table VERY slow > > Interesting. Those are pretty long times to take for a > vacuum on those > tables - if you are using 7.2.x have you tried more frequent vacuum? > Perhaps with a vacuum full each night? Hmmm. > I think that the aborting transaction approach, since it > works, is most > likely to be your best bet in general, however. > > It would be interesting to see the 'vacuum full analyze' > results for the > system tables in that DB, although perhaps less interesting while you > are running your current solution - maybe a comparison would be > worthwhile. Alas we won't be able to downgrade as it affected the service seriously. In doing a full vacuum I notice such errors as: NOTICE: Index pg_index_indrelid_index NUMBER OF INDEX' TUPLES (92) IS NOT THE SAME AS HEAP' (86). Recreate the index Hmm. It's not my index (of course) I'm not sure how to go about re-creating it. Sam ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[BUGS] pg_dump fails for views with UNION and SELECT DISTINCT
If I create a view like this: CREATE VIEW v AS SELECT i FROM a UNION SELECT DISTINCT i FROM b It functions as expected but it causes pg_dump to produce bad output. "ORDER BY b.i" is added to the view definition. On restore this causes: ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns Full setup and steps taken are below. Regards, Sam My setup: Debian woody. Linux 2.4.18. PostgreSQL 7.2.1 ./configure --with-maxbackends=64 --with-gnu-ld --enable-odbc --enable-syslog What I did: # cat > test.sql << EOF SELECT version(); CREATE TABLE foo (i int); CREATE TABLE bar (i int); CREATE VIEW this_is_ok AS SELECT i FROM foo UNION SELECT i FROM bar; CREATE VIEW this_causes_a_broken_dump AS SELECT i FROM foo UNION SELECT DISTINCT i FROM bar; CREATE VIEW this_causes_a_broken_dump_too AS SELECT i FROM foo UNION SELECT DISTINCT i FROM bar x; EOF # createdb test CREATE DATABASE # psql test < test.sql version --- PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4 (1 row) CREATE CREATE CREATE CREATE CREATE # pg_dump test -- snip -- CREATE VIEW "this_is_ok" as SELECT foo.i FROM foo UNION SELECT bar.i FROM bar; -- snip -- CREATE VIEW "this_causes_a_broken_dump" as SELECT foo.i FROM foo UNION SELECT DISTINCT bar.i FROM bar ORDER BY bar.i; -- snip -- CREATE VIEW "this_causes_a_broken_dump_too" as SELECT foo.i FROM foo UNION SELECT DISTINCT x.i FROM bar x ORDER BY x.i; -- snip -- # createdb test2 CREATE DATABASE # pg_dump test | psql test2 CREATE CREATE CREATE ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns ERROR: Relation "x" does not exist ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] drop tempoary table VERY slow
Sorry for the delays on this that machine actually died recently and had to be rebuit before I could do any more tests. > > > It would be interesting to see the 'vacuum full analyze' > > > results for the > > > system tables in that DB, although perhaps less > interesting while you > > > are running your current solution - maybe a comparison would be > > > worthwhile. Are you very interested in the comparison? I could fake a load of the non-transactioned tempoary table queries if you are really interested. I also noted we sometimes get a load of temporary tables left lying around that look "global" and we have to drop by hand. After rebuilding the machine I did a dump from the other machine and inserted on the new machine (schema, data and all) and the new machine is VERY slow at queries; taking 4 seconds at 100% cpu at times instead of 0.2-0.5 seconds or so. Yet if I copy over the binary files when the DB's are stopped the new machine is very fast at queries. Could this be because the new machine started on 7.2.1 with a different optimiser and so never generated query stats the the old box did while it was still on 7.2 ? Sam ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] BUG #1432: type "cube" does not exist
The following bug has been logged online: Bug reference: 1432 Logged by: Sam Hahn Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Operating system: Windows XP Pro v2002 SP 2 Description:type "cube" does not exist Details: When installing with various contrib options, get the following error: A database command error occured whilst executing "E:\Program Files\PostgreSQL\8.0\share\contrib\earthdistance.sql": ERROR: type "cube" does not exist [OK] ---(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: [BUGS] BUG #1432: type "cube" does not exist
Sorry... I thought I had checked "cube"... Michael Fuhr wrote: On Sat, Jan 22, 2005 at 02:25:27AM +, Sam Hahn wrote: A database command error occured whilst executing "E:\Program Files\PostgreSQL\8.0\share\contrib\earthdistance.sql": ERROR: type "cube" does not exist README.earthdistance mentions that contrib/cube must be installed.
