[BUGS] CONNECT BY
Hello ! Here's the patch ( http://gppl.terminal.ru/ ), which allow PostgreSQL use queries like select id from tab start with = connect by id = prior parent_id as described in SQL standard... Developers can look if it's possible to merge this patch with current CVS. -- best regards, Ruslan A Dautkhanov [EMAIL PROTECTED] smime.p7s Description: S/MIME Cryptographic Signature
Re: [BUGS] CONNECT BY
Ruslan A Dautkhanov <[EMAIL PROTECTED]> writes: > Here's the patch ( http://gppl.terminal.ru/ ), which allow > PostgreSQL use queries like > connect by id = prior parent_id Isn't this the same patch that we rejected a year or two back? > as described in SQL standard... AFAIK there is no such syntax in any version of the SQL standard. The syntax that *is* there in SQL99 uses "WITH" to achieve roughly the same results, but with much syntactic difference from Oracle. If you'd like to submit a patch that implements spec-compatible WITH, I can pass along some preliminary work that Andrew Overholt did at Red Hat. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] PostgreSQL 7.4.1 JDBC driver bug
On Wed, 5 May 2004, Declan Lynch wrote: > PostgreSQL 7.4.1 JDBC driver bug > > In the package > > org.postgresql.geometric > > the class PGcircle contains what I think is an "oversight". > > For classes in this package fields are declared "public" allowing direct > access. This is true for all classes execept PGcircle where the radius > field is declared without a modifier. This makes the class unusable > from a java program without modification, as no accessor method is > defined. > This has been fixed in both the stable and development cvs trees and will be included with the next release. Thanks for the report. Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] BUG #1146: REFERENCES doesn't work on Inherited Tables
The following bug has been logged online:
Bug reference: 1146
Logged by: Gábor Katona
Email address: [EMAIL PROTECTED]
PostgreSQL version: 7.3.4
Operating system: Cygwin
Description:REFERENCES doesn't work on Inherited Tables
Details:
The inserting of a row into a table doesn't reflect in the mother table
when the mother table is referenced from a third one.
Example
'a' is the mother table, 'b' is the child, and there is a reference to 'a'
in table 'c'
create table a( id INT UNIQUE, name VARCHAR);
create table b( foofoo INT) INHERITS(a);
create table c (id INT UNIQUE, bar INT, foo INT REFERENCES "a"("id"));
insert into b VALUES (1,'first',0);
insert into b VALUES (2,'second',3);
Then inserting into 'c'
insert into c values (1,3,2);
and the result is unfortunately:
$1 referential integrity violation - key referenced from c not found in a
which means, that id=2 is not in table 'a'.
---(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] BUG #1147: Getting rid of LD_LIBRARY_PATH
The following bug has been logged online: Bug reference: 1147 Logged by: Harald Fricke Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4 Operating system: Solaris 8 Description:Getting rid of LD_LIBRARY_PATH Details: I've got a problem with the build process on Solaris 8 with gcc 3.3.2, libraries installed in /usr/local. ./configure LDFLAGS="-R /usr/local/lib" --with-tcl --prefix=/soft/pgsql-7.4.2 --with-libs=/usr/local/lib Note the LDFLAGS telling the linker to record /usr/local/lib as library search path in the executables. Building works fine, but pgsql and the other executables fail with "libgcc_s.so.1: not found" unless I set LD_LIBRARY_PATH to /usr/local/lib. ldd -ls psql shows libpq.so.3 to be the culprit: find object=libgcc_s.so.1; required by search path=/soft/pgsql-7.4.2/lib (RPATH from file ../lib/libpq.so.3) trying path=/soft/pgsql-7.4.2/lib/libgcc_s.so.1 search path=/usr/lib (default) trying path=/usr/lib/libgcc_s.so.1 libgcc_s.so.1 => (file not found) It looks as if the -R linker directive is not passed to commands building the shared libraries. Using LD_LIBRARY_PATH is not a good idea for production software (see http://www.visi.com/~barr/ldpath.html) and I would like to get rid of it. Proposed fix: Ideally, all invocations of the linker should set the run-time search path to all paths from which libraries are pulled. I understand this will be difficult for the gcc run-time libraries because there is no API to ask gcc for the location. An easier way would be to append the value of LDFLAGS to the linker commands building the shared libraries, not only to those building the executables. Maybe it would be a good idea to pass a -R arguments to the linker for each --with-libs configure switch. After all, if the user wants to use a library directory, it will probably needed at run-time too. I am afraid that I cannot fix the problem myself. The build process is quite complex and I do not speak autoconf or GNU make. Hope I made myself clear... Regards, Harald Fricke ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1147: Getting rid of LD_LIBRARY_PATH
"PostgreSQL Bugs List" <[EMAIL PROTECTED]> writes: > ./configure LDFLAGS="-R /usr/local/lib" --with-tcl Actually, the way this is intended to be handled is to set "rpath" in the platform-specific Makefile. It looks to me like src/makefiles/Makefile.solaris does so; can you look into it and see why that doesn't work for you? > An easier way would be to append the value of LDFLAGS to the linker commands > building the shared libraries, not only to those building the executables. AFAICS we do that too. So I'm still confused why you have a problem. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] BUG #1148: server restarts depending on stats options
The following bug has been logged online:
Bug reference: 1148
Logged by: Robert Treat
Email address: [EMAIL PROTECTED]
PostgreSQL version: 7.5 Dev
Operating system: Slackware Linux 8.1 (Kernel 2.4.18)
Description:server restarts depending on stats options
Details:
set log_statement_stats = true and any of
log_(executer|planner|parser)_stats = true and you will get a database
restart when attempting to connect.
from my logs:
[EMAIL PROTECTED]:/usr/local/pgsql-7.5dev$ cat data/logfile
LOG: received smart shutdown request
LOG: shutting down
LOG: database system is shut down
LOG: database system was shut down at 2004-05-06 12:59:01 PDT
LOG: checkpoint record is at 0/9DDC68
LOG: redo record is at 0/9DDC68; undo record is at 0/0; shutdown TRUE
LOG: next transaction ID: 460; next OID: 17207
LOG: database system is ready
WARNING: statement-level statistics are disabled because parser, planner,
or executor statistics are on
TRAP: BadState("!(((bool) ((CurrentUserId) != 0)))", File: "miscinit.c",
Line: 252)
LOG: server process (PID 22690) was terminated by signal 6
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted at 2004-05-06 12:59:02 PDT
LOG: checkpoint record is at 0/9DDC68
LOG: redo record is at 0/9DDC68; undo record is at 0/0; shutdown TRUE
LOG: next transaction ID: 460; next OID: 17207
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: record with zero length at 0/9DDCA8
LOG: redo is not required
LOG: database system is ready
this is from a snapshot build on may 5th
---(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 #1148: server restarts depending on stats options
Yes, I am aware of this error and will fix it in a few days.
---
PostgreSQL Bugs List wrote:
>
> The following bug has been logged online:
>
> Bug reference: 1148
> Logged by: Robert Treat
>
> Email address: [EMAIL PROTECTED]
>
> PostgreSQL version: 7.5 Dev
>
> Operating system: Slackware Linux 8.1 (Kernel 2.4.18)
>
> Description:server restarts depending on stats options
>
> Details:
>
> set log_statement_stats = true and any of
> log_(executer|planner|parser)_stats = true and you will get a database
> restart when attempting to connect.
>
> from my logs:
>
> [EMAIL PROTECTED]:/usr/local/pgsql-7.5dev$ cat data/logfile
> LOG: received smart shutdown request
> LOG: shutting down
> LOG: database system is shut down
> LOG: database system was shut down at 2004-05-06 12:59:01 PDT
> LOG: checkpoint record is at 0/9DDC68
> LOG: redo record is at 0/9DDC68; undo record is at 0/0; shutdown TRUE
> LOG: next transaction ID: 460; next OID: 17207
> LOG: database system is ready
> WARNING: statement-level statistics are disabled because parser, planner,
> or executor statistics are on
> TRAP: BadState("!(((bool) ((CurrentUserId) != 0)))", File: "miscinit.c",
> Line: 252)
> LOG: server process (PID 22690) was terminated by signal 6
> LOG: terminating any other active server processes
> WARNING: terminating connection because of crash of another server process
> DETAIL: The postmaster has commanded this server process to roll back the
> current transaction and exit, because another server process exited
> abnormally and possibly corrupted shared memory.
> HINT: In a moment you should be able to reconnect to the database and
> repeat your command.
> LOG: all server processes terminated; reinitializing
> LOG: database system was interrupted at 2004-05-06 12:59:02 PDT
> LOG: checkpoint record is at 0/9DDC68
> LOG: redo record is at 0/9DDC68; undo record is at 0/0; shutdown TRUE
> LOG: next transaction ID: 460; next OID: 17207
> LOG: database system was not properly shut down; automatic recovery in
> progress
> LOG: record with zero length at 0/9DDCA8
> LOG: redo is not required
> LOG: database system is ready
>
>
> this is from a snapshot build on may 5th
>
>
>
>
> ---(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
>
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[BUGS] Postgre 7.3.x Bug with datetime formatting.
Hi, There is a problem with the to_char function when it formats the timestamp; in the example below; The HH is different between both calls. select current_timestamp, to_char(current_timestamp, '-MM-DD HH:MM:SS:US') returns timestamptz to_char -- -- 2004-05-05 13:25:12.332313 2004-05-05 12:05:12:332313 Regards, Safwan
Re: [BUGS] [GENERAL] cache lookup of relation 165058647 failed
Sean Chittenden wrote: I'v find out that this error occurs in: dependency.c file 2004-04-26 11:09:34 ERROR: dependency.c 1621: cache lookup of relation 149064743 failed 2004-04-26 11:09:34 ERROR: Relation "tmp_table1" does not exist 2004-04-26 11:09:34 ERROR: Relation "tmp_table1" does not exist in getRelationDescription(StringInfo buffer, Oid relid) function. Any ideas what can cause this errors. Me too. But, I am suspecting that it's a race condition with the new background writer code. I've started testing a new database design and was able to reproduce this on my laptop nearly 90% of the time, but could only reproduce it about 10% of the time on my production databases until I figured out what the difference was, fsync. temp tables don't use the shared buffer cache, how can this be related to the BG writer? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] [GENERAL] cache lookup of relation 165058647 failed
Sean Chittenden wrote: I'v find out that this error occurs in: dependency.c file 2004-04-26 11:09:34 ERROR: dependency.c 1621: cache lookup of relation 149064743 failed 2004-04-26 11:09:34 ERROR: Relation "tmp_table1" does not exist 2004-04-26 11:09:34 ERROR: Relation "tmp_table1" does not exist in getRelationDescription(StringInfo buffer, Oid relid) function. Any ideas what can cause this errors. Me too. But, I am suspecting that it's a race condition with the new background writer code. I've started testing a new database design and was able to reproduce this on my laptop nearly 90% of the time, but could only reproduce it about 10% of the time on my production databases until I figured out what the difference was, fsync. temp tables don't use the shared buffer cache, how can this be related to the BG writer? Don't the system catalogs use the shared buffer cache? BEGIN; SELECT create_temp_table_func(); -- Inserts a row into pg_class via CREATE TEMP TABLE -- Do other stuff COMMIT; -- After the commit, the row is now visible to other backends -- disconnect -- If the delay between the disconnect and reconnect is small enough -- reconnect -- It's as though there is a race condition that allows the function -- pg_table_is_visible() to assert the "cache lookup of relation" -- error. BEGIN; SELECT create_temp_table_func(); -- Before the CREATE TEMP TABLE, I call /* SELECT TRUE FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = ''footmp''::TEXT AND c.relkind = ''r''::TEXT AND pg_catalog.pg_table_is_visible(c.oid); */ -- But the query fails My guess was that the series of events went something like: proc 0) COMMIT's and the row in pg_class is committed proc 1) bgwriter writer code removes a page for the cache proc 2) queries for the page [*] proc 1) writes it to disk proc 2) queries for the page [*] proc 1) sync's the fd [*] proc 2 queries for the page at either of these points In 7.4, there is no bgwriter or background process mucking with cache, Except for the checkpoint process, which does exactly the same as the bgwriter does, and ALL concurrent backends whenever they feel the need to evict a dirty buffer. If it makes a difference if a pg_class page is dirty in the buffer or copied out to disk with respect to visibility rules of the tuples contained in it, then the whole thing is a way larger bug than the one in MIB. First of all, committed or not, a temp object from one session should NEVER be visible in any other. Jan which is why this works 100% of the time. In 7.5, however, there's a 200ms gap where a race condition appears and pg_table_is_visible() fails its PointerIsValid() check. If I put a sleep in, the sleep gives the bgwriter enough time to commit the pages to disk so that the queries for the page happen after the fd's been sync()'ed. I have no other clue as to why this would be happening though, so believe me when I say, I could very well be quite wrong but this is my best, quasi-educated/grep(1)'ed guess. -sc -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] Postgre 7.3.x Bug with datetime formatting.
"Safwan Hak" <[EMAIL PROTECTED]> writes: > select current_timestamp, to_char(current_timestamp, '-MM-DD > HH:MM:SS:US') > 2004-05-05 13:25:12.332313 2004-05-05 12:05:12:332313 Looks fine to me. I suspect what you are wanting is HH24:MI:SS, not HH:MM:SS. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] invalid byte sequence for encoding "EUC_TW"
Dear Sir, We use postgresql in our company. We found an error when restoring database. We guess the problem is due to the postgresql version.
Source Database: - version: Postgresql 7.3.4 - encoding: EUC_TW - OS: Red Hat 9
Target Database: - version: Postgresql 7.4.2 - encoding: EUC_TW - OS: Red Hat 9
Error message when I use the command - psql:
ERROR: invalid byte sequence for encoding "EUC_TW": 0xae61CONTEXT: COPY items, line 2, column itemdesc: "Raa?û?u¡±@¡±{ (?@) QEF01-0301"ERROR: invalid byte sequence for encoding "EUC_TW": 0xab4fCONTEXT: COPY old_directprog, line 1, column directprogdesc: "aˆY¢XIˆYo?O?jÆØI"ERROR: invalid byte sequence for encoding "EUC_TW": 0xa745CONTEXT: COPY receipt, line 72, column receivedfrom: "¡±E¡±R?A"
Would you please give us comment on how to solve this problem. We are looking forward for your reply. Thank you very much!
Eric TanFrom Hong Kong
[EMAIL PROTECTED]
全港最大手機下載中心
http://mobile.yahoo.com.hk/
