[BUGS] BUG #2456: How to write user defined functions in Postgress sql
The following bug has been logged online: Bug reference: 2456 Logged by: Vivekananda Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1 Operating system: Windows-2000 Description:How to write user defined functions in Postgress sql Details: Hai, I have good knowledge in Oracle-Pl/sql.But,now my client wants to use PGSQL.Now i am sending function as below. create or replace function fun_emp(eno integer) returns varchar as v_name varchar; begin select ename into v_name from emp where empno=eno; return v_name; end; / SQL>Select fun_emp(7788) from dual; SQL>Fun_emp(7788) - SMITH It's work on Oracle-Pl/sql. Now how can i implemnt this function in PGSQL? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #2457: Make fails at copydir.c / copydir.o
The following bug has been logged online: Bug reference: 2457 Logged by: William Gray Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.4 Operating system: Mac OS X Tiger 10.4.6 (PowerPC) Description:Make fails at copydir.c / copydir.o Details: When I run the make for 8.1.4, a number of parse errors are thrown, causing the build to fail. Using GNU bison 2.1 via /usr/local/bin instead of mac os x bison (1.28). This doesn't seem to help. Also saw bug #1959 and other archives not on incompatibility fix for tiger's readline, seems unrelated, didn't help. I've done some looking in the archives for ports and bugs lists, but I haven't been able to find something similar (apologies if this has already been dealt with). Is there a searchable bugs database where I can look up related bugs by platform? My build so far: export CFLAGS="-mtune=G5 -mcpu=G5" ./configure --without-tcl --without-krb5 --with-openssl --with-readline --with-pam --with-bonjour --prefix=/usr/local/postgresql-8.1.4 --with-perl make produces the following error (it's kinda long): gcc -no-cpp-precomp -mtune=G5 -mcpu=G5 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -I../../src/port -DFRONTEND -I../../src/include -c -o copydir.o copydir.c In file included from ../../src/include/postgres.h:48, from copydir.c:19: ../../src/include/c.h:194: error: parse error before '*' token ../../src/include/c.h:194: warning: type defaults to 'int' in declaration of 'BoolPtr' ../../src/include/c.h:194: warning: data definition has no type or storage class In file included from ../../src/include/c.h:822, from ../../src/include/postgres.h:48, from copydir.c:19: ../../src/include/port.h:23: error: parse error before "pg_set_noblock" ../../src/include/port.h:23: warning: type defaults to 'int' in declaration of 'pg_set_noblock' ../../src/include/port.h:23: warning: data definition has no type or storage class ../../src/include/port.h:24: error: parse error before "pg_set_block" ../../src/include/port.h:24: warning: type defaults to 'int' in declaration of 'pg_set_block' ../../src/include/port.h:24: warning: data definition has no type or storage class ../../src/include/port.h:35: error: parse error before "path_contains_parent_reference" ../../src/include/port.h:35: warning: type defaults to 'int' in declaration of 'path_contains_parent_reference' ../../src/include/port.h:35: warning: data definition has no type or storage class ../../src/include/port.h:36: error: parse error before "path_is_prefix_of_path" ../../src/include/port.h:36: warning: type defaults to 'int' in declaration of 'path_is_prefix_of_path' ../../src/include/port.h:36: warning: data definition has no type or storage class ../../src/include/port.h:49: error: parse error before "get_home_path" ../../src/include/port.h:49: warning: type defaults to 'int' in declaration of 'get_home_path' ../../src/include/port.h:49: warning: data definition has no type or storage class ../../src/include/port.h:181: error: parse error before "bool" ../../src/include/port.h:244: error: parse error before "bool" ../../src/include/port.h:246: error: parse error before "rmtree" ../../src/include/port.h:246: error: parse error before "bool" ../../src/include/port.h:246: warning: type defaults to 'int' in declaration of 'rmtree' ../../src/include/port.h:246: warning: data definition has no type or storage class In file included from ../../src/include/postgres.h:49, from copydir.c:19: ../../src/include/utils/elog.h:101: error: parse error before "errstart" ../../src/include/utils/elog.h:102: warning: type defaults to 'int' in declaration of 'errstart' ../../src/include/utils/elog.h:102: warning: data definition has no type or storage class ../../src/include/utils/elog.h:240: error: parse error before "bool" ../../src/include/utils/elog.h:240: warning: no semicolon at end of struct or union ../../src/include/utils/elog.h:241: warning: type defaults to 'int' in declaration of 'output_to_client' ../../src/include/utils/elog.h:241: warning: data definition has no type or storage class ../../src/include/utils/elog.h:242: error: parse error before "show_funcname" ../../src/include/utils/elog.h:242: warning: type defaults to 'int' in declaration of 'show_funcname' ../../src/include/utils/elog.h:242: warning: data definition has no type or storage class ../../src/include/utils/elog.h:255: error: parse error before '}' token ../../src/include/utils/elog.h:255: warning: type defaults to 'int' in declaration of 'ErrorData' ../../src/include/utils/elog.h:255: warning: data definition has no type or storage class ../../src/include/utils/elog.h:258: error: parse error before '*' token ../../src/include/utils/elog.h:258: warning: type defaults to 'int' in declaration of 'CopyErrorData' ../../src/include/utils/elog.h:258: warning: data definition has no ty
[BUGS] BUG #2455: psql failing to restore a table because of a constaint violation.
The following bug has been logged online:
Bug reference: 2455
Logged by: Jeff Ross
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.4
Operating system: OpenBSD 3.9 -current
Description:psql failing to restore a table because of a constaint
violation.
Details:
After the upgrade to 8.1.4, this script I use to sync a development database
with our live database began failing.
Here's the script:
#!/bin/sh
#backup script for postgresql databases
#
DATE=`date +%Y%m%d`
#dump the live wykids database
/usr/local/bin/pg_dumpall -p 5432 -c > \
/home/_postgresql/wykids$DATE.sql
#drop the development wykids database
/usr/local/bin/dropdb -p 5435 wykids
#recreate the development wykids database from the dump file we just made
/usr/local/bin/psql -p 5435 template1 -f \
/home/_postgresql/wykids$DATE.sql
Here's the failure:
psql:/home/_postgresql/wykids20060524.sql:84507: ERROR: new row for
relation "Clearinghouse" violates check
constraint "refnumber_ck"
CONTEXT: COPY Clearinghouse, line 1: "Video Three R's for Special Education
School Age Uniqueness and
Cultural Awareness 0.5 total 49.9500..."
Here's the record it barfs on:
wykids=# select * from "Clearinghouse" where "Training Material"
ilike('%three r%');
-[ RECORD 1 ]-+--
Type | Video
Training Material | Three R's for Special Education
Category | School Age
Section Found In | Uniqueness and Cultural Awareness
Clock Hours | 0.5
Notes | total
Price | 49.95
# books | 1
RefNumber | V207.030
Here's the table structure:
wykids=# \d "Clearinghouse"
Table "public.Clearinghouse"
Column | Type | Modifiers
---+---+---
Type | character varying(50) |
Training Material | character varying(75) |
Category | character varying(50) |
Section Found In | character varying(50) |
Clock Hours | real |
Notes | character varying(50) |
Price | double precision |
# books | character varying(10) |
RefNumber | character varying(30) | not null
Indexes:
"clearinghouse_old_pk" PRIMARY KEY, btree ("RefNumber")
Check constraints:
"refnumber_ck" CHECK ("RefNumber"::text ~
similar_escape('[A-Z]|[0-9]|.'::text, NULL::text))
Rules:
refnumber_uppercase_ins AS
ON INSERT TO "Clearinghouse" DO UPDATE "Clearinghouse" SET
"RefNumber" = upper(new."RefNumber"::text)
WHERE "Clearinghouse"."RefNumber"::text = new."RefNumber"::text
The value in the record cited doesn't violate the constraint, and removing
that record from the .sql file
caused the same failure on the very next record.
Using pg_dump -Fc instead also failed.
As a workaround, we dropped the constraint (not critical) to make sure we
still had backup capability.
Jeff Ross
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2457: Make fails at copydir.c / copydir.o
"William Gray" <[EMAIL PROTECTED]> writes: > When I run the make for 8.1.4, a number of parse errors are thrown, causing > the build to fail. The errors suggest some problem with "bool". Do you have any conflicting #define's for bool in your system headers? FWIW, 8.1.4 builds fine for me on 10.4.6. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2457: Make fails at copydir.c / copydir.o
Tom, I'm not sure how I'd go about determining that. I don't recall messing with any of my system's .h files. Do you know where I might look? Thanks! Billy Tom Lane wrote: > "William Gray" <[EMAIL PROTECTED]> writes: > >> When I run the make for 8.1.4, a number of parse errors are thrown, causing >> the build to fail. >> > > The errors suggest some problem with "bool". Do you have any > conflicting #define's for bool in your system headers? > > FWIW, 8.1.4 builds fine for me on 10.4.6. > > regards, tom lane > begin:vcard fn:William Gray n:Gray;William org:Montclair State University;Systems and Security Group email;internet:[EMAIL PROTECTED] title:Systems Developer tel;cell:[EMAIL PROTECTED] note:SMS: [EMAIL PROTECTED] x-mozilla-html:TRUE url:http://tokyo.montclair.edu/blogs/grayw/ version:2.1 end:vcard ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2457: Make fails at copydir.c / copydir.o
William Gray <[EMAIL PROTECTED]> writes: > I'm not sure how I'd go about determining that. I don't recall messing > with any of my system's .h files. Do you know where I might look? grep through all the files under /usr/include looking for "bool" ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #2424: initdb Did Not Escape the Password
Has anyone notice this? I found that this is not fixed in the 8.1.4
release.
I have made a new patch for 8.1.4. It is attached below. Please
tell me if there is any problem. Thank you.
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
diff -u -r postgresql-8.1.4.orig/src/bin/initdb/initdb.c
postgresql-8.1.4/src/bin/initdb/initdb.c
- --- postgresql-8.1.4.orig/src/bin/initdb/initdb.c 2006-02-24
08:55:27.0 +0800
+++ postgresql-8.1.4/src/bin/initdb/initdb.c2006-05-25 12:30:34.0
+0800
@@ -58,6 +58,7 @@
#include
#endif
+#include "libpq-fe.h"
#include "libpq/pqsignal.h"
#include "mb/pg_wchar.h"
#include "getaddrinfo.h"
@@ -1419,9 +1420,10 @@
{
PG_CMD_DECL;
- - char *pwd1,
+ char *pwd1, *pwdesc,
*pwd2;
charpwdpath[MAXPGPATH];
+ size_t pwdlen;
struct stat statbuf;
if (pwprompt)
@@ -1483,8 +1485,12 @@
PG_CMD_OPEN;
+ pwdlen = strlen(pwd1);
+ pwdesc = (char *)pg_malloc(pwdlen * 2 + 1);
+ PQescapeString(pwdesc, pwd1, pwdlen);
PG_CMD_PRINTF2("ALTER USER \"%s\" WITH PASSWORD '%s';\n",
- -username, pwd1);
+ username, pwdesc);
+ free(pwdesc);
PG_CMD_CLOSE;
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.3 (GNU/Linux)
iD8DBQFEd0dTi9gubzC5S1wRAjM4AJ9gZGZ4IcbzE+CYX9HcOeMa2o9IpQCdFMyT
S5N4shISjXRXmrnN/98zAUs=
=uY5a
-END PGP SIGNATURE-
On Sun, 7 May 2006 06:28:53 GMT
"imacat" <[EMAIL PROTECTED]> wrote:
> The following bug has been logged online:
>
> Bug reference: 2424
> Logged by: imacat
> Email address: [EMAIL PROTECTED]
> PostgreSQL version: 8.1.3
> Operating system: Linux
> Description:initdb Did Not Escape the Password
> Details:
>
> The initdb seems did not escape (PQescapeString) the password. The
> following is my test result, with password: ab'ds)24
>
> [EMAIL PROTECTED] ~ % initdb -D /tmp/postgres -E utf8 --locale=en_US.utf8 -U
> postgres -W
> The files belonging to this database system will be owned by user
> "postgres".
> This user must also own the server process.
>
> The database cluster will be initialized with locale en_US.utf8.
>
> fixing permissions on existing directory /tmp/postgres ... ok
> creating directory /tmp/postgres/global ... ok
> creating directory /tmp/postgres/pg_xlog ... ok
> creating directory /tmp/postgres/pg_xlog/archive_status ... ok
> creating directory /tmp/postgres/pg_clog ... ok
> creating directory /tmp/postgres/pg_subtrans ... ok
> creating directory /tmp/postgres/pg_twophase ... ok
> creating directory /tmp/postgres/pg_multixact/members ... ok
> creating directory /tmp/postgres/pg_multixact/offsets ... ok
> creating directory /tmp/postgres/base ... ok
> creating directory /tmp/postgres/base/1 ... ok
> creating directory /tmp/postgres/pg_tblspc ... ok
> selecting default max_connections ... 100
> selecting default shared_buffers ... 1000
> creating configuration files ... ok
> creating template1 database in /tmp/postgres/base/1 ... ok
> initializing pg_authid ... ok
> Enter new superuser password:
> Enter it again:
> setting password ... FATAL: syntax error at or near "ds" at character 41
> child process exited with exit code 1
> initdb: removing contents of data directory "/tmp/postgres"
> [EMAIL PROTECTED] ~ %
>
> I have attached a patch that seems to solve this issue. It works
> for me. Please tell me if there is any problem.
--
Best regards,
imacat ^_*' <[EMAIL PROTECTED]>
PGP Key: http://www.imacat.idv.tw/me/pgpkey.txt
<> News: http://www.wov.idv.tw/
Tavern IMACAT's: http://www.imacat.idv.tw/
TLUG List Manager: http://lists.linux.org.tw/cgi-bin/mailman/listinfo/tlug
pgpa4uc8Pvwsq.pgp
Description: PGP signature
Re: [BUGS] BUG #2457: Make fails at copydir.c / copydir.o
Tom Lane wrote:
> William Gray <[EMAIL PROTECTED]> writes:
>
>> I'm not sure how I'd go about determining that. I don't recall messing
>> with any of my system's .h files. Do you know where I might look?
>>
>
> grep through all the files under /usr/include looking for "bool" ...
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>choose an index scan if your joining column's datatypes do not
>match
>
Looks like there are quite a few possible culprits, as it seems to be an
often-defined thing (but I must not shrink from the task...)
c.h defines a bool, and this looks correct and un-tampered with (grep
output)
> c.h: * file. Added bool enum from machine/types.h for regular users
> c.h: * that want a real boolean type.
> c.h:#ifndef bool
> c.h:typedef enum{ false = 0, true = 1 } bool;
> c.h:#endif /* bool */
Both curses.h and ncurses.h appear to use whatever the compiler
considers bool, and if absent, defines their own.
There's a file stdbool.h that may be at fault:
> stdbool.h: * $FreeBSD: src/include/stdbool.h,v 1.6 2002/08/16 07:33:14
> alfred Exp $
> stdbool.h:#define __bool_true_false_are_defined 1
> stdbool.h:#define bool_Bool
That is explicitly redefining 'bool' to be of type _Bool.
I feel a little lost here. I'm pretty sure I've never messed with these
files. Is it possible that at some point if I configured some piece of
software with a prefix of /usr that it could have added some header file
to /usr/include that would adversely affect other builds, like postgres?
Thanks for your help,
Bill
begin:vcard
fn:William Gray
n:Gray;William
org:Montclair State University;Systems and Security Group
email;internet:[EMAIL PROTECTED]
title:Systems Developer
tel;cell:[EMAIL PROTECTED]
note:SMS: [EMAIL PROTECTED]
x-mozilla-html:TRUE
url:http://tokyo.montclair.edu/blogs/grayw/
version:2.1
end:vcard
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2455: psql failing to restore a table because of a constaint violation.
"Jeff Ross" <[EMAIL PROTECTED]> writes:
> After the upgrade to 8.1.4, this script I use to sync a development database
> with our live database began failing.
> Here's the failure:
> psql:/home/_postgresql/wykids20060524.sql:84507: ERROR: new row for
> relation "Clearinghouse" violates check
> constraint "refnumber_ck"
> Here's the record it barfs on:
> RefNumber | V207.030
> "refnumber_ck" CHECK ("RefNumber"::text ~
> similar_escape('[A-Z]|[0-9]|.'::text, NULL::text))
> The value in the record cited doesn't violate the constraint,
Actually, yes it does. SIMILAR TO (specifically similar_escape()) was
broken for patterns involving | ... but now it's fixed. The previous
code failed to enforce that the pattern be a match to the entire data
string, but that is what is required by my reading of the SQL99 spec.
So your pattern really says that the data value has to be a *single*
letter, digit, or dot. See
http://archives.postgresql.org/pgsql-bugs/2006-04/msg00139.php
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] "blah" is not a domain error
On Wed, May 24, 2006 at 05:29:34PM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > I know domain support is pretty broken, so maybe this is a known issue... > > What are you concerned about exactly? It looks perfectly reasonable > to me. "money" is a base type not a domain. Oops... I thought we'd removed it. Shouldn't it be an error to create a domain that conflicts with an existing type? bench=# create domain money as numeric(21,2); CREATE DOMAIN bench=# drop domain money; ERROR: "money" is not a domain bench=# create domain money as numeric(21,2); ERROR: type "money" already exists bench=# select version(); version -- PostgreSQL 8.2devel on x86_64-unknown-freebsd6.0, compiled by GCC gcc (GCC) 3.4.4 [FreeBSD] 20050518 (1 row) bench=# -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] "blah" is not a domain error
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > Shouldn't it be an error to create a domain that conflicts with an existing > type? It is, if you create it in the same schema. "money", along with all the other built-in types, is in pg_catalog not public. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #2424: initdb Did Not Escape the Password
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---
imacat wrote:
-- Start of PGP signed section.
> Has anyone notice this? I found that this is not fixed in the 8.1.4
> release.
>
> I have made a new patch for 8.1.4. It is attached below. Please
> tell me if there is any problem. Thank you.
>
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> diff -u -r postgresql-8.1.4.orig/src/bin/initdb/initdb.c
> postgresql-8.1.4/src/bin/initdb/initdb.c
> - --- postgresql-8.1.4.orig/src/bin/initdb/initdb.c 2006-02-24
> 08:55:27.0 +0800
> +++ postgresql-8.1.4/src/bin/initdb/initdb.c 2006-05-25 12:30:34.0
> +0800
> @@ -58,6 +58,7 @@
> #include
> #endif
>
> +#include "libpq-fe.h"
> #include "libpq/pqsignal.h"
> #include "mb/pg_wchar.h"
> #include "getaddrinfo.h"
> @@ -1419,9 +1420,10 @@
> {
> PG_CMD_DECL;
>
> - - char *pwd1,
> + char *pwd1, *pwdesc,
> *pwd2;
> charpwdpath[MAXPGPATH];
> + size_t pwdlen;
> struct stat statbuf;
>
> if (pwprompt)
> @@ -1483,8 +1485,12 @@
>
> PG_CMD_OPEN;
>
> + pwdlen = strlen(pwd1);
> + pwdesc = (char *)pg_malloc(pwdlen * 2 + 1);
> + PQescapeString(pwdesc, pwd1, pwdlen);
> PG_CMD_PRINTF2("ALTER USER \"%s\" WITH PASSWORD '%s';\n",
> - - username, pwd1);
> +username, pwdesc);
> + free(pwdesc);
>
> PG_CMD_CLOSE;
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.3 (GNU/Linux)
>
> iD8DBQFEd0dTi9gubzC5S1wRAjM4AJ9gZGZ4IcbzE+CYX9HcOeMa2o9IpQCdFMyT
> S5N4shISjXRXmrnN/98zAUs=
> =uY5a
> -END PGP SIGNATURE-
>
> On Sun, 7 May 2006 06:28:53 GMT
> "imacat" <[EMAIL PROTECTED]> wrote:
> > The following bug has been logged online:
> >
> > Bug reference: 2424
> > Logged by: imacat
> > Email address: [EMAIL PROTECTED]
> > PostgreSQL version: 8.1.3
> > Operating system: Linux
> > Description:initdb Did Not Escape the Password
> > Details:
> >
> > The initdb seems did not escape (PQescapeString) the password. The
> > following is my test result, with password: ab'ds)24
> >
> > [EMAIL PROTECTED] ~ % initdb -D /tmp/postgres -E utf8 --locale=en_US.utf8 -U
> > postgres -W
> > The files belonging to this database system will be owned by user
> > "postgres".
> > This user must also own the server process.
> >
> > The database cluster will be initialized with locale en_US.utf8.
> >
> > fixing permissions on existing directory /tmp/postgres ... ok
> > creating directory /tmp/postgres/global ... ok
> > creating directory /tmp/postgres/pg_xlog ... ok
> > creating directory /tmp/postgres/pg_xlog/archive_status ... ok
> > creating directory /tmp/postgres/pg_clog ... ok
> > creating directory /tmp/postgres/pg_subtrans ... ok
> > creating directory /tmp/postgres/pg_twophase ... ok
> > creating directory /tmp/postgres/pg_multixact/members ... ok
> > creating directory /tmp/postgres/pg_multixact/offsets ... ok
> > creating directory /tmp/postgres/base ... ok
> > creating directory /tmp/postgres/base/1 ... ok
> > creating directory /tmp/postgres/pg_tblspc ... ok
> > selecting default max_connections ... 100
> > selecting default shared_buffers ... 1000
> > creating configuration files ... ok
> > creating template1 database in /tmp/postgres/base/1 ... ok
> > initializing pg_authid ... ok
> > Enter new superuser password:
> > Enter it again:
> > setting password ... FATAL: syntax error at or near "ds" at character 41
> > child process exited with exit code 1
> > initdb: removing contents of data directory "/tmp/postgres"
> > [EMAIL PROTECTED] ~ %
> >
> > I have attached a patch that seems to solve this issue. It works
> > for me. Please tell me if there is any problem.
>
> --
> Best regards,
> imacat ^_*' <[EMAIL PROTECTED]>
> PGP Key: http://www.imacat.idv.tw/me/pgpkey.txt
>
> <> News: http://www.wov.idv.tw/
> Tavern IMACAT's: http://www.imacat.idv.tw/
> TLUG List Manager: http://lists.linux.org.tw/cgi-bin/mailman/listinfo/tlug
-- End of PGP section, PGP failed!
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #2458: Postgresql crash
The following bug has been logged online: Bug reference: 2458 Logged by: Cstdenis Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.3 Operating system: FreeBSD 6.1-RELEASE Description:Postgresql crash Details: I am running a very busy postgresql based database server. This server had been running for a few days with a load average in the 20's during most of the day but postgres seemed to still be returning results quickly enough. Then somebody reported a "the database system is in recovery mode" error and on investivation I see a crash. May 26 17:25:56 ai postgres[41657]: [73388-1] ERROR: value too long for type character varying(50) May 26 19:26:38 ai kernel: pid 41463 (postgres), uid 70: exited on signal 11 (core dumped) May 26 17:26:40 ai postgres[41712]: [73388-1] WARNING: terminating connection because of crash of another server process May 26 17:26:40 ai postgres[41712]: [73388-2] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server May 26 17:26:40 ai postgres[41712]: [73388-3] process exited abnormally and possibly corrupted shared memory. Here is some more info ai# gdb postgres postgres.core GNU gdb 6.1.1 [FreeBSD] Copyright 2004 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type "show copying" to see the conditions. There is absolutely no warranty for GDB. Type "show warranty" for details. This GDB was configured as "i386-marcel-freebsd"...(no debugging symbols found)... Core was generated by `postgres'. Program terminated with signal 11, Segmentation fault. Reading symbols from /usr/local/lib/libintl.so.6...(no debugging symbols found)...done. Loaded symbols for /usr/local/lib/libintl.so.6 Reading symbols from /usr/lib/libssl.so.4...(no debugging symbols found)...done. Loaded symbols for /usr/lib/libssl.so.4 Reading symbols from /lib/libcrypto.so.4...(no debugging symbols found)...done. Loaded symbols for /lib/libcrypto.so.4 Reading symbols from /lib/libz.so.3...(no debugging symbols found)...done. Loaded symbols for /lib/libz.so.3 Reading symbols from /lib/libreadline.so.6...(no debugging symbols found)...done. Loaded symbols for /lib/libreadline.so.6 Reading symbols from /lib/libcrypt.so.3...(no debugging symbols found)...done. Loaded symbols for /lib/libcrypt.so.3 Reading symbols from /lib/libm.so.4...(no debugging symbols found)...done. Loaded symbols for /lib/libm.so.4 Reading symbols from /lib/libutil.so.5...(no debugging symbols found)...done. Loaded symbols for /lib/libutil.so.5 Reading symbols from /lib/libc.so.6...(no debugging symbols found)...done. Loaded symbols for /lib/libc.so.6 Reading symbols from /usr/local/lib/libiconv.so.3...(no debugging symbols found)...done. Loaded symbols for /usr/local/lib/libiconv.so.3 Reading symbols from /lib/libncurses.so.6...(no debugging symbols found)...done. Loaded symbols for /lib/libncurses.so.6 Reading symbols from /usr/local/lib/postgresql/plpgsql.so...(no debugging symbols found)...done. Loaded symbols for /usr/local/lib/postgresql/plpgsql.so Reading symbols from /usr/local/lib/postgresql/slony1_funcs.so...(no debugging symbols found)...done. Loaded symbols for /usr/local/lib/postgresql/slony1_funcs.so Reading symbols from /usr/local/lib/postgresql/xxid.so...(no debugging symbols found)...done. Loaded symbols for /usr/local/lib/postgresql/xxid.so Reading symbols from /usr/local/lib/postgresql/tsearch2.so...(no debugging symbols found)...done. Loaded symbols for /usr/local/lib/postgresql/tsearch2.so Reading symbols from /libexec/ld-elf.so.1...(no debugging symbols found)...done. Loaded symbols for /libexec/ld-elf.so.1 #0 0x35dfeff0 in plpgsql_xact_cb () from /usr/local/lib/postgresql/plpgsql.so (gdb) bt #0 0x35dfeff0 in plpgsql_xact_cb () from /usr/local/lib/postgresql/plpgsql.so #1 0x080ae891 in ReleaseCurrentSubTransaction () #2 0x080af075 in CommitTransactionCommand () #3 0x08213902 in pg_parse_query () #4 0x08215bb8 in PostgresMain () #5 0x081d8740 in ClosePostmasterPorts () #6 0x081da14b in PostmasterMain () #7 0x0818c1ed in main () (gdb) q ai# postmaster --version postmaster (PostgreSQL) 8.1.3 ai# uname -a FreeBSD ai.ctgameinfo.com 6.1-RELEASE FreeBSD 6.1-RELEASE #0: Sun May 21 13:59:57 CDT 2006 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/AI i386 postgresql.conf changed lines from default listen_addresses = '*' max_connections = 450 shared_buffers = 25600 work_mem = 10240 maintenance_work_mem = 102400 max_fsm_pages = 80 max_fsm_relations = 1000 vacuum_cost_delay = 200 vacuum_cost_limit = 50 fsync = on commit_delay = 6 commit_siblings = 2 checkpoint_segments = 8 checkpoint_warning = 30 geqo_threshold = 14 join_collapse_limit = 11 log_destination = 'syslog' silent_mode = on stats_start_collector = on stats_command_string = on stats_row_level = on stats_res
Re: [BUGS] BUG #2458: Postgresql crash
"Cstdenis" <[EMAIL PROTECTED]> writes: > [ SIGSEGV in plpgsql_xact_cb ] I think this is probably an instance of the bug patched here: http://archives.postgresql.org/pgsql-committers/2006-03/msg00022.php If you're certain that your applications never redefine a plpgsql function that might be in active use, then we might need to look harder. Otherwise, please update to 8.1.4 and see if the problem recurs ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
