[BUGS] Bug #898: client_encoding can't support GB18030 encoding in postgresql 7.3.1
zzhb ([EMAIL PROTECTED]) reports a bug with a severity of 3 The lower the number the more severe it is. Short Description client_encoding can't support GB18030 encoding in postgresql 7.3.1 Long Description When I insert records using files, psql -f install.sql if I set PGCLIENTENCODING=GB18030, psql insert simplifed chinese incorrectly,but if I set PGCLIENTENCODING=GBK or unset PGCLIENTENCODING,psql insert simplifed chinese correctly. But when I use \encoding GB18030 to insert records in interactive psql,it can insert simplifed chinese correctly. The following is my envirnoment. [postgres@hbzhang postgres]$ psql -l List of databases Name| Owner | Encoding ---+--+-- template0 | postgres | UNICODE template1 | postgres | UNICODE test | postgres | UNICODE (3 rows) [postgres@hbzhang postgres]$ [postgres@hbzhang postgres]$ locale LANG=zh_CN.GB18030 LC_CTYPE="zh_CN.GB18030" LC_NUMERIC="zh_CN.GB18030" LC_TIME="zh_CN.GB18030" LC_COLLATE="zh_CN.GB18030" LC_MONETARY="zh_CN.GB18030" LC_MESSAGES="zh_CN.GB18030" LC_PAPER="zh_CN.GB18030" LC_NAME="zh_CN.GB18030" LC_ADDRESS="zh_CN.GB18030" LC_TELEPHONE="zh_CN.GB18030" LC_MEASUREMENT="zh_CN.GB18030" LC_IDENTIFICATION="zh_CN.GB18030" LC_ALL= [postgres@hbzhang postgres]$ [root@hbzhang root]# uname -a Linux hbzhang 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 GNU/Linux [root@hbzhang root]# [root@hbzhang root]# pg_ctl --version pg_ctl (PostgreSQL) 7.3.1 [root@hbzhang root]# Sample Code No file was uploaded with this report ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] Bug #899: pg_dumpall produce erroneous files if the databbase use procedural language.
Laurent Faillie ([EMAIL PROTECTED]) reports a bug with a severity of 2 The lower the number the more severe it is. Short Description pg_dumpall produce erroneous files if the databbase use procedural language. Long Description Hi all, I'm working to migrate my database from postgresql 7.2.2 to 7.3.2 It run on an HP-UX 11 box. So, I use 7.3.2 pg_dumpall binary to extract my previous database using the following command pg_dumpall -c -h eux170 and, then I try to import these data into my new one (on a test server) psql -a -v ON_ERROR_STOP template1 < /datas/depot/tmp/db which fails for 2 problem : First : === -- -- TOC entry 247 (OID 16595) -- Name: plpgsql; Type: ACL; Schema: ; Owner: -- REVOKE ALL ON LANGUAGE plpgsql FROM PUBLIC; ERROR: permission denied GRANT ALL ON LANGUAGE plpgsql TO PUBLIC; ERROR: permission denied Why ? Because, in this step, the user remain as it was in the previous step of the file, and in my case, it's a normal user. I think the fix will be to switch to the super user before doing this. second : -- -- TOC entry 252 (OID 16610) -- Name: arrondi_date (timestamp with time zone); Type: FUNCTION; Schema: ; Owner: faillie -- CREATE FUNCTION arrondi_date (timestamp with time zone) RETURNS date AS ' BEGIN IF EXTRACT(HOUR FROM $1) > 20 THEN RETURN DATE($1) + 1; ELSE RETURN DATE($1); END IF; END; ' LANGUAGE plpgsql; ERROR: language "plpgsql" does not exist Why ? Because plpgsql is not existing at this step ... it will be added to the database ... afterwards. The solution is obviously to create all languages just after database and user creation, but BEFORE doing anything on data and on stored procedure. Best regards, Laurent Sample Code No file was uploaded with this report ---(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] my schema + sql function
Marek "Wróbel" <[EMAIL PROTECTED]> writes: > In my schema : "my" I create sql function > CREATE OR REPLACE FUNCTION my.my_foo(int4) RETURNS varchar AS ' > select k.k_dupa ||coalesce( '' ['' || k.k_number || '']'' ,) > from kont k > where k_id = $1; > ' LANGUAGE 'sql'; > table kont is in schema public. > Everythink working ok, but when I try to restore backup system write : > pg_restore: [archiver (db)] could not execute query: ERROR: Relation "kont" does >not exist > pg_restore: *** aborted because of error Probably table "kont" didn't exist when you first created that function? pg_dump isn't very smart about rearranging the order of object creation to handle such dependencies. You'll need to manually adjust the dump order to deal with this. There are options to pg_restore that you can use to change the order in which it reload objects. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[BUGS] Trigger Crashes Database Back End.
I have reported this bug before. It happerns of 7.3, 7.3.1 and 7.3.2.
Having done some further research on the bug.. I have discovered that the
following tranaction (which can be run from a script) with crash the back
end.
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
UPDATE location set phoneid = NULL, streettype = 1 where id = 3639;
insert into customer
(rating,addressid,switches,bookingmess,dispmess,salesrep,status,company,invoiceformat,invoicecode,paymentmode,terms,servicecharge,bull_desc,waittime,creditlimit,transactionlimit,alias,keep,delme,direct_debit,tariff,paymethod,id,billingaddress,acctype,accnum)
values ('1',3639,FALSE,'','','',0,'PETER
CHILDS',0,1,0,30,15,'',5,0,0,'',FALSE,FALSE,FALSE,1,1,4133,3639,0,992);
COMMIT;
Output from script is
BEGIN
SET CONSTRAINTS
UPDATE 1
psql:crash:4: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
psql:crash:4: connection to server was lost
This only happerns when you have the following trigger (at bottom) on the
database. The trigger is attached to pratically every table. If anyone has
any more questions PLEASE ASK.
This is getting quite fustrating and I would like a answer soon!
Peter Childs
CREATE OR REPLACE FUNCTION history_update() RETURNS TRIGGER AS '
if TD["event"] == "INSERT":
lookup = "new"
elif TD["event"] == "DELETE":
lookup = "old"
else:
lookup = "new"
p = plpy.execute(" SELECT CASE i.indproc WHEN (''-''::pg_catalog.regproc)
THEN a.attname ELSE SUBSTR(pg_catalog.pg_get_indexdef(attrelid),
POSITION(''('' in pg_catalog.pg_get_indexdef(attrelid))) END as pkey,
a.atttypid::int, c2.relname FROM pg_catalog.pg_class c,
pg_catalog.pg_class c2, pg_catalog.pg_index i, pg_catalog.pg_attribute a
WHERE c.oid = " + TD["relid"] + " AND c.oid = i.indrelid AND i.indexrelid
= c2.oid and a.attrelid = i.indexrelid and NOT a.attisdropped and
i.indisprimary ORDER BY i.indisprimary DESC, i.indisunique DESC,
c2.relname;")
if len(p) > 0:
pkey = TD[lookup][p[0]["pkey"]]
ppkey = p[0]["pkey"]
else:
pkey = ""
ppkey = ""
rel = plpy.execute("select relname from pg_class where oid=" + TD["relid"]
+ ";")
relname = rel[0]["relname"]
plan = plpy.prepare("INSERT INTO history
(tab,field,action,before,after,occured,who,key) values
($1,$2,$3,$4,$5,now(),user,$6);",["text","text","text","text","text","text"])
if TD["event"] == "INSERT":
old = ""
new = pkey
plpy.execute(plan,[relname,ppkey,TD["event"],old,new,pkey])
else:
for key in TD[lookup].keys():
dont = 0
if TD["event"] == "INSERT":
old = ""
new = TD["new"][key]
if new == None:
dont = 1
elif TD["event"] == "UPDATE":
old = TD["old"][key]
new = TD["new"][key]
else:
old = TD["old"][key]
new = ""
if old == None:
old = "Null"
if new == None:
new = "Null"
if new == old:
dont = 1
if not(dont):
plpy.execute(plan,[relname,key,TD["event"],old,new,pkey])
' LANGUAGE 'plpython';
CREATE TRIGGER history_update AFTER INSERT OR UPDATE OR DELETE ON
customer
FOR EACH ROW EXECUTE PROCEDURE history_update();
---(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] Trigger Crashes Database Back End.
Peter Childs <[EMAIL PROTECTED]> writes: > This only happerns when you have the following trigger (at bottom) on the > database. The trigger is attached to pratically every table. The plpython trigger support fails if the same trigger is attached to more than one table (unless the tables all have identical rowtypes). This was only realized last week sometime. We've been promised a patch soon. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] MacOS X build --with-pam - bad directory for pam_appl.h
Folks,
This is minor. Nonetheless:
The --with-pam option seeks for pam_appl.h at path
, but this directory does not exist in the stock
Jaguar installation. Please see configure output snippet below. Of
course, --with-libraries can't help here; the security directory is in
the argument of the #include directive.
The PAM header files on Jaguar, including /pam_appl.h, are in
/usr/include/pam.
As I'm just passing through for a build, I do not have a proper fix for
you. However, I did test via (cd /usr/include; ln -s pam security),
and configure runs flawlessly (as does the build).
Thanks,
Phillip C. Dibner
Ecosystem Associates
(650) 948-3537
(650) 948-7895 Fax
config.log:configure:8340: checking security/pam_appl.h usability
config.log:configure:8377:31: security/pam_appl.h: No such file or
directory
config.log:#include
config.log:configure:8371: checking security/pam_appl.h presence
config.log:configure:8374:31: security/pam_appl.h: No such file or
directory
config.log:#include
config.log:configure:8420: checking for security/pam_appl.h
config.log:configure:8434: error: header file is
required for PAM
config.log:ac_cv_header_security_pam_appl_h=no
configure: if test "${ac_cv_header_security_pam_appl_h+set}" = set;
then
configure: echo "$as_me:$LINENO: checking for security/pam_appl.h" >&5
configure:echo $ECHO_N "checking for security/pam_appl.h... $ECHO_C" >&6
configure:if test "${ac_cv_header_security_pam_appl_h+set}" = set; then
configure:echo "$as_me:$LINENO: result:
$ac_cv_header_security_pam_appl_h" >&5
configure:echo "${ECHO_T}$ac_cv_header_security_pam_appl_h" >&6
configure:echo "$as_me:$LINENO: checking security/pam_appl.h usability"
>&5
configure:echo $ECHO_N "checking security/pam_appl.h usability...
$ECHO_C" >&6
configure:#include
configure:echo "$as_me:$LINENO: checking security/pam_appl.h presence"
>&5
configure:echo $ECHO_N "checking security/pam_appl.h presence...
$ECHO_C" >&6
configure:#include
configure:{ echo "$as_me:$LINENO: WARNING: security/pam_appl.h:
accepted by the compiler, rejected by the preprocessor!" >&5
configure:echo "$as_me: WARNING: security/pam_appl.h: accepted by the
compiler, rejected by the preprocessor!" >&2;}
configure:{ echo "$as_me:$LINENO: WARNING: security/pam_appl.h:
proceeding with the preprocessor's result" >&5
configure:echo "$as_me: WARNING: security/pam_appl.h: proceeding with
the preprocessor's result" >&2;};;
configure:{ echo "$as_me:$LINENO: WARNING: security/pam_appl.h:
present but cannot be compiled" >&5
configure:echo "$as_me: WARNING: security/pam_appl.h: present but
cannot be compiled" >&2;}
configure:{ echo "$as_me:$LINENO: WARNING: security/pam_appl.h:
check for missing prerequisite headers?" >&5
configure:echo "$as_me: WARNING: security/pam_appl.h: check for missing
prerequisite headers?" >&2;}
configure:{ echo "$as_me:$LINENO: WARNING: security/pam_appl.h:
proceeding with the preprocessor's result" >&5
configure:echo "$as_me: WARNING: security/pam_appl.h: proceeding with
the preprocessor's result" >&2;};;
configure:echo "$as_me:$LINENO: checking for security/pam_appl.h" >&5
configure:echo $ECHO_N "checking for security/pam_appl.h... $ECHO_C" >&6
configure:if test "${ac_cv_header_security_pam_appl_h+set}" = set; then
configure: ac_cv_header_security_pam_appl_h=$ac_header_preproc
configure:echo "$as_me:$LINENO: result:
$ac_cv_header_security_pam_appl_h" >&5
configure:echo "${ECHO_T}$ac_cv_header_security_pam_appl_h" >&6
configure:if test $ac_cv_header_security_pam_appl_h = yes; then
configure: { { echo "$as_me:$LINENO: error: header file
is required for PAM" >&5
configure:echo "$as_me: error: header file is
required for PAM" >&2;}
configure.in: AC_CHECK_HEADER([security/pam_appl.h], [],
[AC_MSG_ERROR([header file is required for PAM])])
grep: contrib: Is a directory
grep: doc: Is a directory
grep: src: Is a directory
[ecos:/Opt/PostgreSQL/postgresql-7.3.2] pcd% pwd
---(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] MacOS X build --with-pam - bad directory for pam_appl.h
If I get another report, I may backpatch to 7.3.X. In fact, I probably should anyway. --- Phillip C. Dibner wrote: > Great - thanks for your reply! Will advise other Mac OS X colleagues > in the mean time. > > On Wednesday, February 19, 2003, at 01:50 PM, Bruce Momjian wrote: > > > > > 7.4 will look in pam/ directory too for OS X. I didn't backpatch this > > into 7.3.X. > > > > --- > > > > > > Phillip C. Dibner > Ecosystem Associates > (650) 948-3537 > (650) 948-7895 Fax > > -- 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] setting time zone during a transaction causes time warp
While using PostgreSQL 7.2.3, I discovered that if I set the time zone during the transaction, "now" takes on an incorrect value for the remainder of that transaction. Once the transaction is committed, everything goes back to normal. I've reproduced the problem below using the "psql" client. The clock on my machine, which is running RedHat Linux 7.3, is set to UTC. The first, second, and fourth times in the output are correct, but the third one is incorrect by five hours (which, perhaps significantly, also happens to be the difference between the US/Eastern time zone and the UTC time zone). I'm not quite sure what's going on here, but if you know of a way to fix this or have a patch that I could apply, I'd really appreciate it! Thanks, ...Robert P.S. Thanks for writing such great free software! dev:~$ psql Welcome to psql, 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 pyramid=# select 'now'::timestamp; timestamptz -- 2003-02-19 20:10:29.20276+00 (1 row) pyramid=# begin work; BEGIN pyramid=# select 'now'::timestamp; timestamptz --- 2003-02-19 20:10:38.834418+00 (1 row) pyramid=# set time zone 'US/Eastern'; SET VARIABLE pyramid=# select 'now'::timestamp; timestamptz --- 2003-02-19 10:10:38.834418-05 (1 row) pyramid=# commit work; COMMIT pyramid=# select 'now'::timestamp; timestamptz --- 2003-02-19 15:11:14.814469-05 (1 row) pyramid=# \q
Re: [BUGS] MacOS X build --with-pam - bad directory for pam_appl.h
Great - thanks for your reply! Will advise other Mac OS X colleagues in the mean time. On Wednesday, February 19, 2003, at 01:50 PM, Bruce Momjian wrote: 7.4 will look in pam/ directory too for OS X. I didn't backpatch this into 7.3.X. --- Phillip C. Dibner Ecosystem Associates (650) 948-3537 (650) 948-7895 Fax ---(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] setting time zone during a transaction causes time warp
"Robert Haas" <[EMAIL PROTECTED]> writes:
> While using PostgreSQL 7.2.3, I discovered that if I set the time zone
> during the transaction, "now" takes on an incorrect value for the
> remainder of that transaction.
The problem seems to be restricted to the result of 'now'::timestamptz,
and not now() or 'now'::timestamp without time zone. I've applied the
attached patch to 7.3 --- it would probably work in 7.2 as well,
although I think you'd have to hand-patch because of the difference in
the nearby HAVE_INT64_TIMESTAMP #ifdefs.
regards, tom lane
*** src/backend/utils/adt/datetime.c~ Tue Jan 28 20:09:03 2003
--- src/backend/utils/adt/datetime.cThu Feb 20 00:19:50 2003
***
*** 1242,1250
case DTK_NOW:
tmask = (DTK_DATE_M |
DTK_TIME_M | DTK_M(TZ));
*dtype = DTK_DATE;
! GetCurrentTimeUsec(tm,
fsec);
! if (tzp != NULL)
! *tzp =
CTimeZone;
break;
case DTK_YESTERDAY:
--- 1242,1248
case DTK_NOW:
tmask = (DTK_DATE_M |
DTK_TIME_M | DTK_M(TZ));
*dtype = DTK_DATE;
! GetCurrentTimeUsec(tm,
fsec, tzp);
break;
case DTK_YESTERDAY:
***
*** 1958,1964
case DTK_NOW:
tmask = DTK_TIME_M;
*dtype = DTK_TIME;
! GetCurrentTimeUsec(tm,
fsec);
break;
case DTK_ZULU:
--- 1956,1962
case DTK_NOW:
tmask = DTK_TIME_M;
*dtype = DTK_TIME;
! GetCurrentTimeUsec(tm,
fsec, NULL);
break;
case DTK_ZULU:
*** src/backend/utils/adt/nabstime.c~ Thu Dec 12 14:17:04 2002
--- src/backend/utils/adt/nabstime.cThu Feb 20 00:19:50 2003
***
*** 243,267
int tz;
abstime2tm(GetCurrentTransactionStartTime(), &tz, tm, NULL);
-
- return;
} /* GetCurrentDateTime() */
void
! GetCurrentTimeUsec(struct tm * tm, fsec_t *fsec)
{
int tz;
int usec;
abstime2tm(GetCurrentTransactionStartTimeUsec(&usec), &tz, tm, NULL);
#ifdef HAVE_INT64_TIMESTAMP
*fsec = usec;
#else
*fsec = usec * 1.0e-6;
#endif
-
- return;
} /* GetCurrentTimeUsec() */
--- 243,266
int tz;
abstime2tm(GetCurrentTransactionStartTime(), &tz, tm, NULL);
} /* GetCurrentDateTime() */
void
! GetCurrentTimeUsec(struct tm * tm, fsec_t *fsec, int *tzp)
{
int tz;
int usec;
abstime2tm(GetCurrentTransactionStartTimeUsec(&usec), &tz, tm, NULL);
+ /* Note: don't pass NULL tzp directly to abstime2tm */
+ if (tzp != NULL)
+ *tzp = tz;
#ifdef HAVE_INT64_TIMESTAMP
*fsec = usec;
#else
*fsec = usec * 1.0e-6;
#endif
} /* GetCurrentTimeUsec() */
*** src/include/utils/datetime.h~ Wed Jan 15 19:27:17 2003
--- src/include/utils/datetime.hThu Feb 20 00:19:51 2003
***
*** 261,267
extern void GetCurrentDateTime(struct tm * tm);
! extern void GetCurrentTimeUsec(struct tm * tm, fsec_t *fsec);
extern void j2date(int jd, int *year, int *month, int *day);
extern intdate2j(int year, int month, int day);
--- 261,267
extern void GetCurrentDateTime(struct tm * tm);
! extern void GetCurrentTimeUsec(struct tm * tm, fsec_t *fsec, int *tzp);
extern void j2date(int jd, int *year, int *month, int *day);
extern intdate2j(int year, int month, int da
