[BUGS] BUG #5217: (new) error with VB 6.0 SP 6 and psqlODBC 8.4.1
The following bug has been logged online: Bug reference: 5217 Logged by: Martin Email address: searepo...@aol.at PostgreSQL version: 8.4.1 Operating system: M$ Vista (but does not matter) same on XP, ... Description:(new) error with VB 6.0 SP 6 and psqlODBC 8.4.1 Details: works fine with psqlODBC 8.3.4, error with psqlODBC 8.4.1 Sp.CursorType = adOpenDynamic Sp.Open "SELECT field1, False :: boolean as Field2 from test LIMIT 1;", PGSQL_Connection Sp!Field2 = "1" ' < error I can send mylog_3804.log and psqlodbc_3804.log, if someone is interested. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #2673: run-time error '429'
The following bug has been logged online: Bug reference: 2673 Logged by: Martin Email address: [EMAIL PROTECTED] PostgreSQL version: Sebastian Operating system: windows 98 Description:run-time error '429' Details: run-time error '4'29' Ativex component can't create objct Como lo puedo solucionar eso?? Este cartel me lo pone cuando estoy intentando entrar en un juego llamado mu la paguina del juego es www.powermju.no-ip.org por favor si me pueden ayudar se lo agradeceria muchisimo. Desde ya muchas gracias y espero q me puedan ayudar!! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #2396: SELECT to_number('6500', '999,999.99') != 650
The following bug has been logged online:
Bug reference: 2396
Logged by: Martin
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.3
Operating system: Win XP SP 2 German
Description:SELECT to_number('6500', '999,999.99') != 650
Details:
1.) I have found some mails from Nov 2004 concerning the same problem with
to_number, so is it sitll not solved or not a bug?
SELECT to_number('6500', '999,999,999.99'), to_number('6500',
'9.99'), to_number('6500', '99.999,99'), to_number('6500',
'FM999,999.99');
=>
650 !! 6500 6500 6500
7.3.6 SuSe 8.0
8.1.3 Debian
8.1.3 Win XP
2.) I cant find this feature/bug in the doc's or in
postgresql-8.1.3\src\test\regress\sql\numeric.sql.
Is there a bug-list?
Thank you for your efforts!
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[BUGS] Incorrect cursor behaviour with gist index
Hi,
I'm using PostgreSQL 8.3.1 with PostGIS 1.3.3.
I have the following table:
CREATE TABLE fog_4752 (
description text,
gid integer NOT NULL,
item_class text,
item_id integer,
origin_x double precision,
origin_y double precision,
origin_z double precision,
geometry geometry,
CONSTRAINT enforce_dims_geometry CHECK ((ndims(geometry) = 2)),
CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 27700)) );
INSERT INTO fog_4752 (description, gid, item_class, item_id, origin_x,
origin_y, origin_z, geometry) VALUES ('Polygon', 6, 'Polygon', 6,
270463.5995574299, 660527.33722885954, 0,
'010320346C0100050042098568C0E014411917774DA44F26419475BFC6784608411917774DA44F26419475BFC678460841936EDB0B1901224142098568C0E01441936EDB0B1901224142098568C0E014411917774DA44F2641');
INSERT INTO fog_4752 (description, gid, item_class, item_id, origin_x,
origin_y, origin_z, geometry) VALUES ('Polygon', 4, 'Polygon', 4,
306782.6950348168, 112627.83974142233, 0,
'010320346C01000500288A4FB70C430741E06CA5E47060F240EAA4C6336FD11941E06CA5E47060F240EAA4C6336FD1194130FD41FD044F0241288A4FB70C43074130FD41FD044F0241288A4FB70C430741E06CA5E47060F240');
INSERT INTO fog_4752 (description, gid, item_class, item_id, origin_x,
origin_y, origin_z, geometry) VALUES ('Polygon', 5, 'Polygon', 5,
224805.30810014351, 415632.86486705049, 0,
'010320346C01000500A0BDB7907EBA04415A4590094F4612417256A12EEB1311415A4590094F4612417256A12EEB1311419892D7F01B3B2041A0BDB7907EBA04419892D7F01B3B2041A0BDB7907EBA04415A4590094F461241');
INSERT INTO fog_4752 (description, gid, item_class, item_id, origin_x,
origin_y, origin_z, geometry) VALUES ('Polygon', 1, 'Polygon', 1,
317159.57945692743, 809954.47290725145, 0,
'010320346C0100050018E0648798E71641E6B7DC1478FF2A415CE0AA36489F0F41E6B7DC1478FF2A415CE0AA36489F0F41861465CF1170264118E0648798E71641861465CF1170264118E0648798E71641E6B7DC1478FF2A41');
INSERT INTO fog_4752 (description, gid, item_class, item_id, origin_x,
origin_y, origin_z, geometry) VALUES ('Polygon', 2, 'Polygon', 2,
457247.5191554199, 527703.21662584448, 0,
'010320346C01000500B50BF40E7B642041475B545A4EF4224186DD520906081741475B545A4EF4224186DD5209060817411E9EFD061D821A41B50BF40E7B6420411E9EFD061D821A41B50BF40E7B642041475B545A4EF42241');
INSERT INTO fog_4752 (description, gid, item_class, item_id, origin_x,
origin_y, origin_z, geometry) VALUES ('Polygon', 3, 'Polygon', 3,
567242.49402979179, 197718.29200272885, 0,
'010320346C010005003FCF4C7C885E23415E698CEE51801041BA452CFB42811E415E698CEE51801041BA452CFB42811E41E075E49D8189FE403FCF4C7C885E2341E075E49D8189FE403FCF4C7C885E23415E698CEE51801041');
ALTER TABLE ONLY fog_4752 ADD CONSTRAINT fog_4752_pkey PRIMARY KEY (gid);
CREATE INDEX fog_4752_geometry_sidx ON fog_4752 USING gist (geometry);
Now I'm running these SQL commands:
SET ENABLE_SEQSCAN = OFF;
BEGIN;
DECLARE C63 SCROLL CURSOR FOR
select * from fog_4752
where (geometry && setsrid('BOX(111697.268 85647.94,655446.012
679205.729)'::box2d,27700) and
intersects(geometry,'SRID=27700;POLYGON((655446.011617731
679205.729188659,111697.267899139 679205.729188659,111697.267899139
85647.940243935,655446.011617731 85647.940243935,655446.011617731
679205.729188659))'::geometry));
FETCH ABSOLUTE -1 IN C63;
FETCH ABSOLUTE 1 IN C63;
FETCH FORWARD 10 IN C63;
FETCH ABSOLUTE -1 IN C63;
CLOSE C63;
END;
The query used to create the cursor selects 5 of the 6 rows in the table.
The problem is this: The "FETCH ABSOLUTE -1 IN C63" commands return zero rows,
when clearly they should return one row, namely the last row in the cursor.
As far as I understand, the ENABLE_SEQSCAN = OFF forces the query to use the
gist index, which would otherwise not be used. However, if the cursor would
select a suitably sized subset of a large enough table, then the gist index
would be used regardless of the setting of ENABLE_SEQSCAN. So simply setting
ENABLE_SEQSCAN = ON is not a solution that will work in all cases.
To me this seems to be a bug in PostgreSQL. If it is, can it be fixed?
Regards,
Martin Schäfer
Principal Software Engineer
Cadcorp
Computer Aided Development Corporation Ltd.
1 Heathcock Court, London, WC2R 0NT
[EMAIL PROTECTED]
www.cadcorp.com
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Problem with the pg_dumpall file format
Guys, I apologise for sending a bug report this way, but the bug reporting server seems to be out to lunch at present. Firefox reports being able to contact it but its bug submission wasn't accepted. To avoid loss of the bug report, here it is: Name: Martin Gregorie e-mail: [EMAIL PROTECTED] Postgres: 8.2.10 OS: Linux (Fedora 9) Details: For some reason, when my message table (see below) is dumped, blank lines are introduced between rows. Each row contains the contents of each field. There isn't anything obvious in the final bytea field that might cause this problem. There are longer rows than the one preceding the blank line. The blank lines cause the restore to crash with the message: psql:cluster.sql:146200: ERROR: invalid input syntax for integer: "" CONTEXT: COPY message, line 3, column sdbk: "" The table definition is: create table message ( sdbkint primary key, date_sent timestamp, subject int references subject(sdbk), searchtext text, headers bytea, multipart boolean, content bytea ); If you need data samples, please ask. Best regards, Martin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Fall back to alternative tsearch dictionary directory
Hello all,
as recently mentioned on pg-general@, I am currently working on making
installed myspell/unspell dictionary packages (which install
themselves in /usr/share/myspell/dicts, mostly LATIN encoded)
available to PostgreSQL's tsearch/word stemming in Debian/Ubuntu.
So far I wrote the postgresql-common infrastructure to mangle these
dictionary/affix files to become palatable for PostgreSQL (recoding to
UTF-8, renaming to lowercase, changing file suffix) and install them
into /var/cache/postgresql/dicts/ whenever a {hun,my}spell-* package
is installed or updated.
The remaining bit is teaching postgresql to actually look into
/var/cache/postgresql/dicts/ if it does not find a matching
dictionary/affix file in ${sharepath}/tsearch_data/.
The reasons why I'm not using ${sharepath}/tsearch_data/ in the first
place are that
- it's autogenerated data, as opposed to files statically shipped in
a package
- I do not want to conflict to/overwrite files which the admin
manually put there.
I created an initial demo patch which provides this fallback. It works
great, it passes my test cases (which set up tsearch full text search
and stemming handling) and is pretty simple, too.
However, the path is hardcoded so far, which is of course bad for
upstream inclusion. So this should either become a ./configure option
--with-tsearch-dict-fallback=path (or similar), or even a new optional
configuration parameter for postgresql.conf.
However, before I work on that, I'd like to collect some opinions
about the general idea, and whether you prefer autoconf option or
postgresql.conf, or whether you wouldn't accept it at all?
Thanks a lot in advance!
Martin
--
Martin Pitt| http://www.piware.de
Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org)
# Description: If a tsearch/stem dictionary is not found in /usr/share/postgresql/VERSION/tsearch_data/, fall back to /var/cache/postgresql/dicts/, where postgresql-common creates them from system directories.
# Ubuntu: https://launchpad.net/bugs/301770
--- ./src/backend/tsearch/ts_utils.c.orig 2008-12-01 06:47:28.0 -0800
+++ ./src/backend/tsearch/ts_utils.c 2008-12-01 07:05:14.0 -0800
@@ -15,6 +15,7 @@
#include "postgres.h"
#include
+#include
#include "miscadmin.h"
#include "tsearch/ts_locale.h"
@@ -36,7 +37,7 @@
const char *extension)
{
char sharepath[MAXPGPATH];
- char *result;
+ char *result, *system_result;
/*
* We limit the basename to contain a-z, 0-9, and underscores. This may
@@ -58,6 +59,21 @@
snprintf(result, MAXPGPATH, "%s/tsearch_data/%s.%s",
sharepath, basename, extension);
+ /* fall back to /var/cache/postgresql/dicts/ */
+ if (access(result, R_OK) != 0)
+ {
+ system_result = palloc(MAXPGPATH);
+ snprintf(system_result, MAXPGPATH, "/var/cache/postgresql/dicts/%s.%s",
+ basename, extension);
+ if (access(system_result, R_OK) == 0)
+ {
+ pfree(result);
+ result = system_result;
+ }
+ else
+ pfree(system_result);
+ }
+
return result;
}
signature.asc
Description: Digital signature
Re: [BUGS] Fall back to alternative tsearch dictionary directory
Hi Tom,
Tom Lane [2008-12-01 19:51 -0500]:
> I can't see any reason whatever to not put them into
> ${sharepath}/tsearch_data/. It's not like you're expecting to be
> able to share them with other applications.
No, not for sharing. I just don't like them to be in /usr, but that's
by and large a stylistic preference, and I won't dwell on it.
> Seems like it'd be quite sufficient to choose a specialized naming
> policy within tsearch_data, say es_ES.aff -> system_es_es.aff.
Works for me, too.
> I don't think moving stuff into a different subdirectory makes
> conflicts a non-problem; it just means that half the world will be
> unhappy with the search order you chose.
IMHO there is really just one sensible ordering here. Always prefer
the ones installed by hand, and only if they are not present, fall
back to the system defaults. The other way around would mean that the
admin couldn't do local overriding any more.
Thanks,
Martin
--
Martin Pitt| http://www.piware.de
Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org)
signature.asc
Description: Digital signature
Re: [BUGS] Fall back to alternative tsearch dictionary directory
Tom Lane [2008-12-01 19:51 -0500]:
> I can't see any reason whatever to not put them into
> ${sharepath}/tsearch_data/. It's not like you're expecting to be
> able to share them with other applications.
Oh, forgot yesterday, there is one case: the data can be shared
between the 8.3, 8.4, and any future version. (In Debian/Ubuntu you
can install different 8.x versions in parallel)
But that can easily be achieved in the distro packaging by adding
symlinks, so if you prefer just looking for
${sharedir}/tsearch_data/system_ll_cc.affix, that would still work for
me.
Thanks!
Martin
--
Martin Pitt| http://www.piware.de
Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Fall back to alternative tsearch dictionary directory
Martin Pitt [2008-12-02 5:29 -0800]:
> Tom Lane [2008-12-01 19:51 -0500]:
> > I can't see any reason whatever to not put them into
> > ${sharepath}/tsearch_data/. It's not like you're expecting to be
> > able to share them with other applications.
>
> Oh, forgot yesterday, there is one case: the data can be shared
> between the 8.3, 8.4, and any future version. (In Debian/Ubuntu you
> can install different 8.x versions in parallel)
>
> But that can easily be achieved in the distro packaging by adding
> symlinks, so if you prefer just looking for
> ${sharedir}/tsearch_data/system_ll_cc.affix, that would still work for
> me.
Right, so I changed the patch accordingly.
Thanks,
Martin
--
Martin Pitt| http://www.piware.de
Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org)
# Description: If a tsearch/stem dictionary is not found in sharedir/tsearch_data/ll_cc.{dict,affix}, fall back to sharedir/tsearch_data/system_ll_cc.{dict,affix}, where postgresql-common creates them from system directories.
# Ubuntu: https://launchpad.net/bugs/301770
diff -Nur -x '*.orig' -x '*~' postgresql-8.3/build-tree/postgresql-8.3.5/src/backend/tsearch/ts_utils.c postgresql-8.3.new/build-tree/postgresql-8.3.5/src/backend/tsearch/ts_utils.c
--- postgresql-8.3.5/src/backend/tsearch/ts_utils.c 2008-06-19 09:52:31.0 -0700
+++ postgresql-8.3.5/src/backend/tsearch/ts_utils.c 2008-12-05 12:48:02.0 -0800
@@ -15,6 +15,7 @@
#include "postgres.h"
#include
+#include
#include "miscadmin.h"
#include "tsearch/ts_locale.h"
@@ -36,7 +37,7 @@
const char *extension)
{
char sharepath[MAXPGPATH];
- char *result;
+ char *result, *system_result;
/*
* We limit the basename to contain a-z, 0-9, and underscores. This may
@@ -58,6 +59,21 @@
snprintf(result, MAXPGPATH, "%s/tsearch_data/%s.%s",
sharepath, basename, extension);
+ /* fall back to a system-supplied one */
+ if (access(result, R_OK) != 0)
+ {
+ system_result = palloc(MAXPGPATH);
+ snprintf(system_result, MAXPGPATH, "%s/tsearch_data/system_%s.%s",
+ sharepath, basename, extension);
+ if (access(system_result, R_OK) == 0)
+ {
+ pfree(result);
+ result = system_result;
+ }
+ else
+ pfree(system_result);
+ }
+
return result;
}
signature.asc
Description: Digital signature
[BUGS] Debian Bug#506196: postgresql: consume too much power when idle (>10 wakeups/second)
Hello PostgreSQL developers, first, happy new year to you all! I recently got this bug report through Debian. I can confirm that on an otherwise idle system, and with no connections to PostgreSQL at all, I get 8,5% ( 11,5) postgres : schedule_hrtimeout_range (hrtimer_wakeup) in powertop. Now, 11 wakeups per minute is not dramatic, and with PostgreSQL being a server application, perfect power management is certainly the least concern for you. However, it would be interesting to know whether those wakeups are intended and necessary, or if they would be easy or hard to fix. (I have no problem with closing the bug as wontfix, but I'd like to give a rationale). Thank you! Martin - Forwarded message from Xavier Bestel - Subject: Bug#506196: postgresql: consume too much power when idle (>10 wakeups/second) Reply-To: Xavier Bestel , 506...@bugs.debian.org From: Xavier Bestel To: Debian Bug Tracking System Date: Wed, 19 Nov 2008 10:12:05 +0100 Package: postgresql Version: 8.3.5-1 Severity: minor Hi, postgresql is installed on my machine because it was pulled by another package (can't remember which one). It doesn't do anything special, but it still does more then 10 wakeups/second on that system, uselessly. If idle, it shouldn't even appear in powertop's profile. Thanks, Xav - End forwarded message - -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) signature.asc Description: Digital signature
Re: [BUGS] Debian Bug#506196: postgresql: consume too much power when idle (>10 wakeups/second)
Hi Alvaro, Alvaro Herrera [2009-01-04 18:05 -0300]: > How many databases are there? It's a freshly created instance, thus just "template[01]" and "postgres". Standard 8.3 autovacuum is enabled. Thanks, Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: Bug#506196: [BUGS] Debian Bug#506196: postgresql: consume too much power when idle (>10 wakeups/second)
Hi Simon, Simon Riggs [2009-01-05 12:13 +]: > Seems consistent with wal_writer_delay = 200ms and bgwriter_delay = > 200ms, plus some other minor noise. Ah, thanks. > So its not a "bug" and won't get "fixed". Right, it's not a bug in the sense of "does not behave as intended". Purely a wishlist thingy. Thanks for your investigations, Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: Bug#506196: [BUGS] Debian Bug#506196: postgresql: consume too much power when idle (>10 wakeups/second)
Hi Simon, Simon Riggs [2009-01-05 10:57 +]: > Is this 11 per minute, or 11 per second? Per second. Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Fall back to alternative tsearch dictionary directory
Hi Tom,
Tom Lane [2009-01-14 20:56 -0500]:
> Bruce Momjian writes:
> > Uh, would someone eyeball and apply this? Thanks.
>
> I thought we had come to the conclusion that no patch was needed
> because there's no convincing reason to look anyplace except
> ${sharepath}/tsearch_data/.
That's what the current patch does now: It falls back to
system_basename.extension if there is no basename.extension. This
avoids overwriting the admin's own installed dictionaries with
automatically generated ones, and allows telling apart the ones that
the system can update automatically (system_) from the ones that we
should not touch (without system_ prefix).
Martin
--
Martin Pitt| http://www.piware.de
Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4629: PL/pgSQL issue
The following bug has been logged online: Bug reference: 4629 Logged by: Martin Blazek Email address: mbla...@8bc.com PostgreSQL version: 8.3.5 Operating system: Windows XP Description:PL/pgSQL issue Details: I try to create the following rule. It doesn't make much sense, but the syntax is ok and if the table "test" exists, it is created. CREATE RULE "rule" AS ON INSERT TO "test" DO INSTEAD INSERT INTO "test" VALUES (1); The next step is creating a function that contains only the following command: CREATE FUNCTION test() RETURNS integer AS $$ BEGIN CREATE RULE "rule" AS ON INSERT TO "test" DO INSTEAD INSERT INTO "test" VALUES (1); END;$$ LANGUAGE plpgsql; Wow! Here's the result (already on function create, not during runtime): ERROR: syntax error at ""test"" DETAIL: Expected record variable, row variable, or list of scalar variables following INTO. KONTEXT: compile of PL/pgSQL function "test" near line 2 It appears that only insert rules have this issue - update and delete work as expected. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4724: Array index out of bounds
The following bug has been logged online: Bug reference: 4724 Logged by: Ett Martin Email address: ettl.mar...@gmx.de PostgreSQL version: 8.3.7 Operating system: Linux Description:Array index out of bounds Details: I have checked the sources with a static code analysis tool cppcheck: cppcheck -q -a -j2 postgresql-8.3.7 [postgresql-8.3.7/contrib/cube/cube.c:1418]: (all) Array index out of bounds [postgresql-8.3.7/contrib/cube/cube.c:1437]: (all) Array index out of bounds Best regards Ettl Martin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] shared_buffers/SHMMAX defaults?
Hello fellow PostgreSQL packagers, recently, I started to get quite a bunch of bug reports a la "PostgreSQL fails to start due to too little shared memory" [1]. I have never seen this before, neither in Debian, so I guess the SHMMAX defaults changed somewhat in Linux 2.6.27. It seems that with other components, such as X.org, using large amounts of shared memory as well, startup sometimes works and sometimes doesn't. Now I wonder what I should do about it. I see these options: (1) Ignore + no hidden magic - very inconvenient, package installation does not create default cluster sometimes, or the default cluster fails to start on system boot Best solution for admin control freaks. (2) Be more conservative about initdb's default setting + no hidden magic + upstream compatible solution - suboptimal performance by default (3) Change SHMMAX in postgresql's init script if necessary + Always works - Unexpected, works behind admin's back. Currently I tend towards (2), but I'd like to hear some more opinions about it. Does anyone else have seen this problem as well? Thanks, Martin [1] https://launchpad.net/bugs/264336 -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) signature.asc Description: Digital signature
Re: [BUGS] shared_buffers/SHMMAX defaults?
Tom Lane [2009-03-30 20:29 -0400]: > Consider > (4) Lobby your kernel packagers to install saner SHMMAX/SHMALL > defaults. Sounds good. :-) Those are the current defaults: kernel.shmmax = 33554432 kernel.shmall = 2097152 kernel.shmmni = 4096 Thanks, Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt
Hello all, I have been packaging cvs snapshots, and now 8.4 beta 1 for Debian recently, and hammered on postgresql-common enough to make it work with 8.4 now (some changed semantics, migration of obsolete/renamed postgresql.conf settings, etc.). Almost all of the tests pass now, so it's generally working great. The test suite detected one regression in libpq, though: Setting $PGHOST now complains about a missing root.crt, although this is only relevant on the server side (or did I misunderstood this?) $ PGHOST=127.0.0.1 /usr/lib/postgresql/8.4/bin/psql -l psql: root certificate file "/home/martin/.postgresql/root.crt" does not exist Thank you! Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) signature.asc Description: Digital signature
Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt
Tom Lane [2009-04-10 1:15 -0400]: > Martin Pitt writesyuqhom#3: > > The test suite detected one regression in libpq, though: Setting > > $PGHOST now complains about a missing root.crt, although this is only > > relevant on the server side (or did I misunderstood this?) > > No, that's a progression: the client wants to validate the server's > cert, too. Indeed it is nice to see this feature (great to prevent spoofing), but if I don't have a ~/.postgresql/root.crt at all, it shouldn't certainly break completely? (which it does now). libpq did not bump the SONAME, thus this breaks backwards compatibility with previous PostgreSQL versions which also used libpq.so.5, i. e. from 8.2 on. (Oh, and just for the record: I'm not advocating at all to bump the soname; with thousands of packages linking against it, those are always painful library transitions). Thanks, Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt
Peter Eisentraut [2009-04-10 14:56 +0300]: > I assume the server has the snakeoil certificate installed? It is a self-signed certificate indeed (Debian's ssl-cert package). > In that case, it is correct that the client refuses to proceed, > although the exact manner of breaking could perhaps be improved. That may be true for 8.4, and I'm could stop configuring the snakeoil certificate by default. That would make configuring a server for a real SSL certificate harder than it needs to be, though. However, we can't afford to break existing installations. If a user has 8.4 installed locally, he'll use libpq from 8.4, and suddenly he could not connect to a remote SSL 8.3 cluster any more. So the check needs at least be turned into a warning for connecting to a pre-8.4 server. Also, the error message needs to be much clearer. Right now it just tells you that it couldn't find a per-user root.crt and fails. So as an user, I wonder: What is that file? I don't have one, where should I get it from? And why does each user need to have its own? html/libpq-ssl.html describes it fairly well: "When the sslverify parameter is set to cn or cert, libpq will verify that the server certificate is trustworthy by checking the certificate chain up to a CA. For this to work, place the certificate of a trusted CA in the file ~/.postgresql/root.crt in the user's home directory. libpq will then verify that the server's certificate is signed by one of the trusted certificate authorities." Nowhere does it say that the connection will fail immediately if you do not have a root.crt. man psql(1) does not have any word about it, like how to set the sslverify argument. I do see the benefit of failing to connect to an SSL-enabled server *if* I have a root.crt which doesn't match. But why fail if I don't have one? Thanks for considering, Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) signature.asc Description: Digital signature
Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt
Peter Eisentraut [2009-04-10 22:46 +0300]: > This whole debate hinges on the argument that encryption without > anti-spoofing > is *not* useful. I don't disagree, but it is not *worse* than having no encryption at all. The reason why Debian/Ubuntu install a snakeoil SSL certificate and configure all packages to use it by default is not because we think that this default configuration is "secure" in any way. The reason is that configuring it that way is that it becomes darn easy to make your entire server with all daemons such as postgresql, postfix, dovecot, etc. trusted by simply replacing that central certificate. You can still configure individual services to use a different one. Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt
Peter Eisentraut [2009-04-10 14:56 +0300]: > I assume the server has the snakeoil certificate installed? In that case, it > is correct that the client refuses to proceed, although the exact manner of > breaking could perhaps be improved. Is it really refusing self-signed certificates? That would be strange. I had thought it checks whether the user has the server signing certificate of the server installed on his client home directory (which, BTW, seems like a strange place to default to, and thus keep it). Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt
Magnus Hagander [2009-04-10 19:14 +0200]: > It's "secure by default". Without it, most people will *never* get > protected by verifying the certificate because they will not manually > copy root certificates there. The problem and fallacy with security is that if you make it too tight, people will just disable it. I'd be the happiest man on the world if the internet would stop using bad SSL certificates, and all those browsers which try to educate the users about exceptions could just refuse the site and do nothing. But unfortunately the world doesn't work that way. Similarly, my concern is that people would rather disable SSL than trying to get all their db users to put a certificate into their home directory (t least this should be configurable at the system-wide level, like checking whether a cert in /etc/ssl/certs/* matches; or making this more flexible to configure the default on a system level at least.) So the nice thing about a warning is that it will stay around and nag people, instead of dragging them into a kneejerk reaction to "fix" their systems which suddenly got "broken". But thanks to everyone for chiming in. Initially I thought it was just a subtle regression. Since it doesn't seem to be, I'll just adapt my test suite if this is going to stay like it is right now. I'm still concerned about the potential confusion, though. Thanks, Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt
Tom Lane [2009-04-10 19:01 -0400]: > This seems a bit handwavy --- there's a difference between the machine's > own cert and what it thinks is a root cert. Sure. > How do you deal with that? If the root cert is real, how do you put > in self-signed server certs? I'm afraid I don't understand. If an admin replaces the default snakeoil cert with a real one which he got signed by a CA, then of course he would replace the standard system SSL cert (which all the servers default to, and which is initially the snakeoil one) with the "good" certificate. I don't see a reason why an admin would replace a self-signed cert with another self-signed cert? Thanks, Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt
Magnus Hagander [2009-04-12 0:29 +0200]: > The option is there already, it's called "none". That's what people are > asking for - they don't care who they are connecting to, just that the > traffic is encrypted (be it legitimate or hacked traffic, at least it's > encrypted). For the record, I don't agree. SSL certificate validation is good, and should be done as long as you have a cert installed. Encryption without authentication is not worth a lot, after all. Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt
Magnus Hagander [2009-04-11 11:50 +0200]: > It treats self-signed certificates the same way it treats anything else. > In the case of a self-signed one, the certificate and the CA certificate > are the same. Thus, you have to copy the server certificate to the client. Right, that's what I had expected. Thanks for confirming. > > I had thought it checks whether the user has the server signing > > certificate of the server installed on his client home directory > > (which, BTW, seems like a strange place to default to, and thus keep > > it). > > That has just been brought up from previous versions. Perhaps we need to > have a system wide root store as well - then you could point that to > whatever snakeoil store you have, and it would find the cert correctly? We couldn't set this up by default, of course, since each installed machine will have a different snakeoil cert (it gets generated during installation). But at least the servers I know often use something like /etc/ssl/certs/.crt and point their services (like apache, postfix, etc.) to this. However, right now the client side psql does not have any system wide configuration files, so adding something like this will need some careful design. Thanks, Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt
Magnus Hagander [2009-04-12 0:58 +0200]: > Which means that every time I connect, I need to first to make sure that > the file is there, and that the proper user has permissions to read the > file, *before* I connect. Arguably the connection should fail if the file is present, but cannot be read because of permission or syntax errors. That's exactly how the server side behaves as well, and IMHO it's the right thing to do. Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt
Hello Bruce, Bruce Momjian [2009-04-11 8:33 -0400]: > I noticed you didn't quote the next sentence: > > The SSL connection will fail if the server does not present a trusted > certificate. Indeed. When I read it first, it seemed unrelatead to me, but now I understand where this was aiming at. > Which clearly explains _a_ failure, but doesn't link it well to the > behavior. I agree the wording needs improvement so I have update the > doc paragraph to mention "requires" at the beginning": > > I will now look at improving the libpq error message. I saw your patches. Many thanks, this is much clearer now. Bruce Momjian [2009-04-11 17:42 -0400]: > The only other approach would be to add an sslverify value of > 'try' that tries only if root.crt exists. The semantics of this sound like a good default to me, since it would enforce a good cert as soon as I start caring, i. e. when I actually have a root.crt to verify against. This is the kind of "single-action SSL enabling" workflow I was already looking for when configuring the snakeoil SSL cert by default. Perhaps it shouldn't be called "try", though, maybe "cert" should have above semantics, and "force" should have the currently implemented one (i. e. fail if not present)? But that gets us dangerously close to bikeshedding now... Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt
Stephen Frost [2009-04-14 9:09 -0400]: > I disagree, and you *can* do authentication without SSL! I know. But then you do have authentication as well, which was exactly my point. Also, I said "not a lot better", not "totally useless". Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) signature.asc Description: Digital signature
Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt
Stephen Frost [2009-04-14 9:18 -0400]: > * Martin Pitt (mp...@debian.org) wrote: > > We couldn't set this up by default, of course, since each installed > > machine will have a different snakeoil cert (it gets generated during > > installation). > > It's worse than that.. Obviously, you can have the client installed on > systems which aren't where the server is (we do this alot..) and there's > no way for a packaging system to pull the cert from the server. Of course I assumed that the server and client are on different systems. If they are on the same, then we just use the Unix socket and don't need all this SSL fuss at all. > If we're going to do something along those lines, we should start by > supporting a CA cert directory or similar. We could then recommend > ca-certificates and default config the client to use those. Of course, > anyone who actually cares about security probably wouldn't install > ca-certificates, but it's what the browsers use. Hm, that sounds like opening a can of worms, TBH. But yes, once the final defaults in psql are agreed upon, we can discuss the packaging. Thanks, Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) signature.asc Description: Digital signature
[BUGS] 8.4 dropped Russian server translations
Hello all, is there any reason why 8.4 dropped src/backend/po/ru.po? It was still present in 8.3, and still works. It might perhaps not be entirely up to date, but 90% coverage is certainly better than 0? Or were there any other problems with it? Thanks in advance, Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) signature.asc Description: Digital signature
Re: [BUGS] 8.4 dropped Russian server translations
Tom Lane [2009-09-06 10:24 -0400]: > According to > http://babel.postgresql.org/ > it's more like 38%. Ah, thanks for the link! Makes sense then. Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5041: Changing data_directory problem
Jonas [2009-09-06 17:29 +]: > After changing data_directory - in the same way as I did for postgres 8.3 - The official way is to move the directory and set "data_directory" in postgresql.conf. (Or create a new cluster with the -d/--datadir option). Is that what you did? Please give us the output of "pg_lsclusters". Thanks, Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5043: Stored procedure returning different results for same arguments
The following bug has been logged online: Bug reference: 5043 Logged by: Martin Edlman Email address: edl...@fortech.cz PostgreSQL version: 8.2.0 Operating system: Linux (RHEL 4.4) Description:Stored procedure returning different results for same arguments Details: I have a stored procedure (SP) get_schemebind_date(int, date) which looks up a date of validity of a payment scheme. Table schemebind contains valid_from (date), schemeid (int), contractid (int). There are these records in schemebind '2008-11-01', 123, 1004 '2009-09-01', 456, 1004 When I call "select get_schemebind_date(1004,'2009-09-01')" from psql I get correct result, which is 2009-09-01. But when I call it from within another sp (let's call it xfunc(int, date, varchar), I get wrong result 2008-11-01. The date passed to xfunc() is then passed to get_schemebind_date(). I enabled RAISE NOTICE to see the parameters and the result, in case I call get_schemebind_date() from console I see NOTICE: get_schemebind_date(1004, 2009-09-01) = 2009-09-01 In case get_schemebind_date() is called from xfunc() I see NOTICE: get_schemebind_date(1004, 2009-09-01) = 2008-11-01 I really don't understand it. All parameters are of type date, column valid_from is of type date as well. If you need full code of xfunc, I can send it to you. CREATE OR REPLACE FUNCTION get_schemebind_date(integer, date) RETURNS date AS $BODY$ DECLARE rec RECORD; con ALIAS FOR $1; dat ALIAS FOR $2; BEGIN SELECT max(valid_from) AS vf INTO rec FROM schemebind WHERE valid_from <= dat AND contractid = con; RAISE NOTICE 'get_schemebind_date(%, %) = %', con, dat, rec.vf; IF FOUND THEN RETURN rec.vf; END IF; RETURN NULL; END $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION get_schemebind_date(integer, date) OWNER TO postgres; -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5043: Stored procedure returning different results for same arguments
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello, > please send function xfunc. Your code looks well. > please try sql function > ... > has it same behave like plpgsql function? Yes, result is the same - it returns 2008-11-01 I made further investigation - it seems the problem is here between the keyboard and the chair :-) As I wrote an answer for you I realized the problem. The xfunc() and therefore get_schemebind_date() is called from a trigger AFTER DELETE ON schemebind. So the trigger deletes the '2009-09-01' record, get_schemebind_date() then returns correct result '2008-11-01'. But xfunc() then fails and the trigger operation is rolled back and I see the '2009-09-01' record again... Sorry guys for bothering you and taking your time. Regards, - -- Martin Edlman Fortech, spol. s r.o, Ropkova 51, 57001 Litomyšl Public GPG key: http://edas.visaci.cz/#gpgkeys -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/ iEYEARECAAYFAkqnfE4ACgkQqmMakYm+VJ8iswCggwTcu9pZQOaAjAIjW0D22kTs HYIAni3mLdNilwgxNeQsGFxNogBg6OCL =ZVLv -END PGP SIGNATURE- -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5055: Invalid page header error
The following bug has been logged online: Bug reference: 5055 Logged by: john martin Email address: postgres_...@live.com PostgreSQL version: 8.3.6 Operating system: Centos 5.2 32 bit Description:Invalid page header error Details: All of a sudden we started seeing page header errors in certain queries. The messages are in the form of "ERROR: invalid page header in block of relation ". The query fails. I found may previous messages in the archives. Most, if not all, replies seemed to indicate hardware errors. I have run all the disk/memory tests like fsync and memtest86 but nothing was found. I have also rebooted is multiple times . I found an unsatisfactory work around that causes, ahem, data loss. We went ahead with it anyway fortunately because the error happened in our dev environment. IOW, we could tolerate the data loss. The work around consists of adding the following parameter to postgresql.conf and restarting postgres. "zero_damaged_pages=TRUE" We no longer see the error messages with the above work around. Needless to say, the work around cannot be used in production. But the database is running on the SAME HARDWARE. Is it possible that it is a postgres bug? I found the issue reported 5 years back to my surprise. http://archives.postgresql.org/pgsql-hackers/2004-09/msg00869.php I am urging the community to investigate the possibility that it may not be hardware related, especially since it was first reported at least 5 years back. Or may be you have decided not to fix if the number of people reporting is very less. I have a very good opinion of postgres quality. While I am not 100% sure it is a bug (only circumstantial evidence), I do think it improves the product quality to fix an annoying old bug. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Small syntax error in fmgr.h
Hello PostgreSQL developers, https://launchpad.net/bugs/458020 reports a small syntax error in ./src/include/fmgr.h:338: extern int no_such_variable The trailing semicolon is missing: "I had to make the changes in the attached patch file in order to use the PG_MODULE_MAGIC macro. From a quick scan it looks as though there may also be a similar problem with PG_FUNCTION_INFO_V1 also on line 329. " Thanks, Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5204: ODBC connection NOT working
The following bug has been logged online: Bug reference: 5204 Logged by: Alan Martin Email address: alan.mar...@mightyautoparts.com PostgreSQL version: 08.04.0100 Operating system: Windows 7 64-bit Description:ODBC connection NOT working Details: Good morning. I've been struggling with this install for most of a day now. Basically, the program INSTALLS fine, but doesn't work. It's listed under installed applications, but when you go to Admin/ODBC and check Drivers, there's no listing for PostgreSQL. Because the driver's not showing, I can't create a data source, which is necessary to access some important internal applications. I've looked around on the 'net for a solution, and the only one that looked promising (installing PostgreSQL version 8.0) isn't possible since the installer has been taken down. Any help would be appreciated! -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] UTF-8 encoding failure
Hi,
Is this the right place to report bugs?
The attached program creates a table with Japanese column names. When the
column names are retrieved in a query using PQfname, and invalid string is
returned. The invalid column names can also be seen in PGAdmin III. This only
seems to happen with one (or more?) specific Japanese character.
Operating System: Windows Vista Ultimate SP2 32bit
PostgreSQL server versions: 8.4.2 and 8.3.3 both fail
libpq version: 8.3.6
Database encoding: UTF8
Client encoding:
- UTF8: does not return the same column name used to create the table.
- SJIS: "select * from table" fails with error:
ERROR: invalid byte sequence for encoding "UTF8": 0xe59eff
HINT: This error can also happen if the byte sequence does not match the
encoding expected by the server, which is controlled by "client_encoding".
>From what I can see the character in question has the following encodings:
SJIS: 8c b4
UTF-16: 9f 53
UTF-8: e5 8e 9f
Any chance this problem could be fixed soon?
Bye,
Martin Schäfer
Principal Software Engineer
Cadcorp
Computer Aided Development Corporation Ltd.
1 Heathcock Court, London, WC2R 0NT
martin.schae...@cadcorp.com
www.cadcorp.com
This email is confidential and may be privileged and should not be used, read
or copied by anyone who is not the original intended recipient. If you have
received this email in error please inform the sender and delete it from
your mailbox or any other storage mechanism. Unless specifically stated,
nothing in this email constitutes an offer by Cadcorp and Cadcorp does not
warrant that any information contained in this email is accurate.
Cadcorp cannot accept liability for any statements made which are clearly the
sender's own and not expressly made on behalf of Cadcorp or one of its agents.
Please rely on your own virus check. No responsibility is taken by Cadcorp
for any damage arising out of any bug or virus infection.
ÿþ# i n c l u d e " s t d a f x . h "
# i n c l u d e < m b c t y p e . h > / / _ K A N J I _ C P
# i n c l u d e < p o s t g r e s q l \ l i b p q . h >
/ / T h e o n e a n d o n l y a p p l i c a t i o n o b j e c t
C W i n A p p t h e A p p ;
i n t t e s t ( b o o l b S h i f t J I S )
{
w c h a r _ t w s t r T e x t [ 1 0 2 4 ] ;
c h a r s t r T e x t [ 1 0 2 4 ] ;
/ / C o n n e c t
P G c o n n * p C o n n = P Q s e t d b L o g i n ( N U L L ,
N U L L , N U L L , N U L L , " D E V 3 0 8 " , " p o s t g r e s " ,
" * * * * * * * * " ) ;
i f ( ! p C o n n ) r e t u r n _ _ L I N E _ _ ;
i f ( P Q s t a t u s ( p C o n n ) ! =
C O N N E C T I O N _ O K ) r e t u r n _ _ L I N E _ _ ;
/ / S e t C l i e n t e n c o d i n g
U I N T c o d e p a g e ;
i f ( b S h i f t J I S ) {
i f ( P Q s e t C l i e n t E n c o d i n g ( p C o n n ,
" S J I S " ) ! = 0 ) r e t u r n _ _ L I N E _ _ ;
c o d e p a g e = _ K A N J I _ C P ;
} e l s e {
i f ( P Q s e t C l i e n t E n c o d i n g ( p C o n n ,
" U T F - 8 " ) ! = 0 ) r e t u r n _ _ L I N E _ _ ;
c o d e p a g e = C P _ U T F 8 ;
}
/ / C r e a t e t a b l e
c o n s t w c h a r _ t w s t r C o m m a n d [ ] =
L " c r e a t e t a b l e ÛV[( g i d s e r i a l P R I M A R Y
K E Y , ¬f t e x t , ¢0¤0Æ0à0i d i n t e g e r , ¢0¤0Æ0à0¯0é0¹0
t e x t , S¹px d o u b l e p r e c i s i o n , S¹py d o u b l e
p r e c i s i o n , S¹pz d o u b l e p r e c i s i o n ) " ;
i f ( : : W i d e C h a r T o M u l t i B y t e ( c o d e p a g e ,
0 , w s t r C o m m a n d , - 1 , s t r T e x t ,
_ c o u n t o f ( s t r T e x t ) , N U L L , N U L L ) = = 0 )
r e t u r n _ _ L I N E _ _ ;
P G r e s u l t * p R e s u l t = P Q e x e c ( p C o n n ,
s t r T e x t ) ;
i f ( ! p R e s u l t ) r e t u r n _ _ L I N E _ _ ;
i n t s t a t = P Q r e s u l t S t a t u s ( p R e s u l t ) ;
i f ( s t a t ! = P G R E S _ E M P T Y _ Q U E R Y & &
s t a t ! = P G R E S _ C O M M A N D _ O K & & s t a t ! =
P G R E S _ T U P L E S _ O K ) r e t u r n _ _ L I N E _ _ ;
P Q c l e a r ( p R e s u l t ) ;
/ / Q u e r y t a b l e
c o n s t w c h a r _ t w s t r C o m m a n d 2 [ ] =
Re: [BUGS] UTF-8 encoding failure
Unfortunately there are encoding problems on my machine. I'm getting this output: Testing with encoding UTF-8: PQfname(pResult, 4) returned incorrect field name. PQfname(pResult, 5) returned incorrect field name. PQfname(pResult, 6) returned incorrect field name. Testing with encoding SJIS: Query failed with error: ERROR: invalid byte sequence for encoding "UTF8": 0xe59eff HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". Instead of a Japanese OS, I used an English (UK) OS and set the system locale to Japanese, but that should be good enough I think. I also used a new install of PostgreSQL, with only the PostGIS module added. Okay, I find it difficult to imagine that a bug like this would not have been found before. But if it works fine on your machines, why doesn't it work on my machine? Regards, Martin Schäfer Principal Software Engineer Cadcorp Computer Aided Development Corporation Ltd. 1 Heathcock Court, London, WC2R 0NT martin.schae...@cadcorp.com www.cadcorp.com > -Original Message- > From: Takahiro Itagaki [mailto:itagaki.takah...@oss.ntt.co.jp] > Sent: Friday, March 26, 2010 3:07 AM > To: Martin Schäfer; Hiroshi Saito > Cc: pgsql-bugs@postgresql.org > Subject: Re: [BUGS] UTF-8 encoding failure > > "Hiroshi Saito" wrote: > > Probably it is not a bug. > > > > Testing with encoding UTF-8: > > NOTICE: CREATE TABLE will create implicit sequence > "蝗帛ｮ誉gid_seq" for serial column "蝗帛ｮ・gid" > > Testing with encoding SJIS: > > NOTICE: CREATE TABLE will create implicit sequence > "四宗_gid_seq" for serial column "四宗.gid" > > > From: "Martin Schafer" > > - UTF8: does not return the same column name used to create > the table. > > Yeah, there are no encoding problems in libpq and postgres, > but you need to print NOTICE messages in the same codepage > with your console. > You probably need to replace routines to display NOTICE > messages in libpq. > PQsetNoticeReceiver() would be a help. > > Or, you could just change the codepage of your console to UTF-8. > However, there is a known problem in Windows console that console with > UTF-8 codepage cannot display any kanji characters. I'm not > sure what we can do to fix it ... some font settings in the registory? > > Regards, > --- > Takahiro Itagaki > NTT Open Source Software Center > > > > This email is confidential and may be privileged and should not be used, read or copied by anyone who is not the original intended recipient. If you have received this email in error please inform the sender and delete it from your mailbox or any other storage mechanism. Unless specifically stated, nothing in this email constitutes an offer by Cadcorp and Cadcorp does not warrant that any information contained in this email is accurate. Cadcorp cannot accept liability for any statements made which are clearly the sender's own and not expressly made on behalf of Cadcorp or one of its agents. Please rely on your own virus check. No responsibility is taken by Cadcorp for any damage arising out of any bug or virus infection. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] UTF-8 encoding failure
Hello Hiroshi-san, The only differences I found between my source file and yours are the file encoding (Unicode/SJIS), different #include-s and different parameters to PQsetdblogin. I had to undo the last two changes to compile and run on my machine. And the changed file encoding made no difference. It still fails on my machine. Regards, Martin > -Original Message- > From: Hiroshi Saito [mailto:z-sa...@guitar.ocn.ne.jp] > Sent: Friday, March 26, 2010 2:58 PM > To: Martin Schäfer; 'Takahiro Itagaki' > Cc: pgsql-bugs@postgresql.org > Subject: Re: [BUGS] UTF-8 encoding failure > > Hi Martin-san. > > Umm, I sent what corrected some of your TestPostgreSQLJapanese.cpp. > http://archives.postgresql.org/pgsql-bugs/2010-03/msg00188.php > Did you use it? > > Regards, > Hiroshi Saito > > - Original Message - > From: "Martin Schäfer" > > > > Unfortunately there are encoding problems on my machine. > I'm getting this output: > > > > Testing with encoding UTF-8: > > PQfname(pResult, 4) returned incorrect field name. > > PQfname(pResult, 5) returned incorrect field name. > > PQfname(pResult, 6) returned incorrect field name. > > > > Testing with encoding SJIS: > > Query failed with error: > > ERROR: invalid byte sequence for encoding "UTF8": 0xe59eff > > HINT: This error can also happen if the byte sequence does > not match the encoding > > expected by the server, which is controlled by "client_encoding". > > > > Instead of a Japanese OS, I used an English (UK) OS and set > the system locale to Japanese, > > but that should be good enough I think. > > I also used a new install of PostgreSQL, with only the > PostGIS module added. > > > > Okay, I find it difficult to imagine that a bug like this > would not have been found > > before. But if it works fine on your machines, why doesn't > it work on my machine? > > > > Regards, > > > > Martin Schäfer > > Principal Software Engineer > > Cadcorp > > Computer Aided Development Corporation Ltd. > > 1 Heathcock Court, London, WC2R 0NT > > martin.schae...@cadcorp.com > > www.cadcorp.com > > > >> -Original Message- > >> From: Takahiro Itagaki [mailto:itagaki.takah...@oss.ntt.co.jp] > >> Sent: Friday, March 26, 2010 3:07 AM > >> To: Martin Schäfer; Hiroshi Saito > >> Cc: pgsql-bugs@postgresql.org > >> Subject: Re: [BUGS] UTF-8 encoding failure > >> > >> "Hiroshi Saito" wrote: > >> > Probably it is not a bug. > >> > > >> > Testing with encoding UTF-8: > >> > NOTICE: CREATE TABLE will create implicit sequence > >> "蝗帛ｮ誉gid_seq" for serial column "蝗帛ｮ・gid" > >> > Testing with encoding SJIS: > >> > NOTICE: CREATE TABLE will create implicit sequence > >> "四宗_gid_seq" for serial column "四宗.gid" > >> > >> > From: "Martin Schafer" > >> > - UTF8: does not return the same column name used to create > >> the table. > >> > >> Yeah, there are no encoding problems in libpq and postgres, > >> but you need to print NOTICE messages in the same codepage > >> with your console. > >> You probably need to replace routines to display NOTICE > >> messages in libpq. > >> PQsetNoticeReceiver() would be a help. > >> > >> Or, you could just change the codepage of your console to UTF-8. > >> However, there is a known problem in Windows console that > console with > >> UTF-8 codepage cannot display any kanji characters. I'm not > >> sure what we can do to fix it ... some font settings in > the registory? > >> > >> Regards, > >> --- > >> Takahiro Itagaki > >> NTT Open Source Software Center > >> > >> > >> > >> > > This email is confidential and may be privileged and should not be used, read or copied by anyone who is not the original intended recipient. If you have received this email in error please inform the sender and delete it from your mailbox or any other storage mechanism. Unless specifically stated, nothing in this email constitutes an offer by Cadcorp and Cadcorp does not warrant that any information contained in this email is accurate. Cadcorp cannot accept liability for any statements made which are clearly the sender's own and not expressly made on behalf of Cadcorp or one of its agents. Please rely on your own virus check. No responsibility is taken by Cadcorp for any damage arising out of any bug or virus infection. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] UTF-8 encoding failure
Hi, This worked fine, as you can see in the attached result. However, I can see that my database has a different locale (English_United Kingdom.1252) from yours (C). Can this cause the problem? Regards, Martin > -Original Message- > From: Hiroshi Saito [mailto:z-sa...@guitar.ocn.ne.jp] > Sent: Friday, March 26, 2010 3:36 PM > To: Martin Schäfer; 'Takahiro Itagaki' > Cc: pgsql-bugs@postgresql.org > Subject: Re: [BUGS] UTF-8 encoding failure > > Hi. > > Ahh ok, Would you try this. > > C:\home\HIROSHI>"C:\Program > Files\PostgreSQL\8.4\bin\psql.exe" -f SJIS_TEST.SQL > -p5433 postgres postgres > > データベ > ース一覧 > \x96\xBC\x91O | \x8F\x8A\x97L\x8E | > \x83G\x83\x93\x83R\x81[\x83f\x83B\x83\x93\x > 83O | \x8Fﾆ構x87\x8F\x87\x8F\x98 | > Ctype(\x95ﾏ浬xB7\x89\x89\x8EZ\x8Eq) | \x83A\x 83N\x83Z\x83X\x8C\xA0 > ---+---+-- > -- > ---+---+ > +---+- > ---+-- > +---+- > ---+-- > +---+- > ---+-- > +---+- > ---+-- > +---+- > ---+-- > > postgres | postgres | UTF8 > | C | C | > template0 | postgres | UTF8 > | C | C >| =c/postgr > es > > >: postgres= CTc/postgres > template1 | postgres | UTF8 > | C | C >| =c/postgr > es > > >: postgres= CTc/postgres > (3 行) > > > UTF8 > SJIS > psql:SJIS_TEST.SQL:5: NOTICE: CREATE TABLE / PRIMARY KEY > will create implicit i ndex "漢字テーブル_pkey" for table "漢字テーブル" > CREATE TABLE > INSERT 0 1 > INSERT 0 1 > へんかん | どうですか > + > さいとう | ひろし > こんにちは | ひろしです > (2 行) > > > DROP TABLE > > - Original Message - > From: "Martin Schäfer" > > > > Hello Hiroshi-san, > > > > The only differences I found between my source file and > yours are the file encoding > > (Unicode/SJIS), different #include-s and different > parameters to PQsetdblogin. I had to > > undo the last two changes to compile and run on my machine. > And the changed file encoding > > made no difference. It still fails on my machine. > > > > Regards, > > > > Martin > This email is confidential and may be privileged and should not be used, read or copied by anyone who is not the original intended recipient. If you have received this email in error please inform the sender and delete it from your mailbox or any other storage mechanism. Unless specifically stated, nothing in this email constitutes an offer by Cadcorp and Cadcorp does not warrant that any information contained in this email is accurate. Cadcorp cannot accept liability for any statements made which are clearly the sender's own and not expressly made on behalf of Cadcorp or one of its agents. Please rely on your own virus check. No responsibility is taken by Cadcorp for any damage arising out of any bug or virus infection. f[^x[Xê \x96\xBC\x91O | \x8F\x8A\x97L\x8E | \x83G\x83\x93\x83R\x81[\x83f\x83B\x83\x93\x83O | \x8FÆ\x87\x8F\x87\x8F\x98 | Ctype(\x95Ï\xB7\x89\x89\x8EZ\x8Eq) | \x83A\x83N\x83Z\x83X\x8C\xA0 --+---++-++-- dev308 | postgres | UTF8 | English_United Kingdom.1252 | English_United Kingdom.1252| dev308_pg141 | postgres | UTF8 | English_United Kingdom.1252 | English_United Kingdom.1252| postgres | postgres | UTF8 | English_United Kingdom.1252 | English_United Kingdom.1252| templat
Re: [BUGS] UTF-8 encoding failure
Can you copy libpq.dll to the same destination please? I linked libpq as a static library into my project. Martin > -Original Message- > From: Hiroshi Saito [mailto:z-sa...@guitar.ocn.ne.jp] > Sent: Friday, March 26, 2010 4:35 PM > To: Martin Schäfer; 'Takahiro Itagaki' > Cc: pgsql-bugs@postgresql.org > Subject: Re: [BUGS] UTF-8 encoding failure > > Hi. > > I think that the locale is not desirable. However, It is not > related to this problem. > Can you test without editing this program? > http://winpg.jp/~saito/PGSQL_SJIS/ > I think that it passes. > > Regards, > Hiroshi Saito > > - Original Message - > From: "Martin Schäfer" > To: "'Hiroshi Saito'" ; "'Takahiro > Itagaki'" > > Cc: > Sent: Friday, March 26, 2010 11:49 PM > Subject: Re: [BUGS] UTF-8 encoding failure > > > > Hi, > > > > This worked fine, as you can see in the attached result. > However, I can see that my > > database has a different locale (English_United > Kingdom.1252) from yours (C). Can this > > cause the problem? > > > > Regards, > > > > Martin > > > >> -Original Message- > >> From: Hiroshi Saito [mailto:z-sa...@guitar.ocn.ne.jp] > >> Sent: Friday, March 26, 2010 3:36 PM > >> To: Martin Schäfer; 'Takahiro Itagaki' > >> Cc: pgsql-bugs@postgresql.org > >> Subject: Re: [BUGS] UTF-8 encoding failure > >> > >> Hi. > >> > >> Ahh ok, Would you try this. > >> > >> C:\home\HIROSHI>"C:\Program > >> Files\PostgreSQL\8.4\bin\psql.exe" -f SJIS_TEST.SQL > >> -p5433 postgres postgres > >> > >> データベ > >> ース一覧 > >> \x96\xBC\x91O | \x8F\x8A\x97L\x8E | > >> \x83G\x83\x93\x83R\x81[\x83f\x83B\x83\x93\x > >> 83O | \x8Fﾆ構x87\x8F\x87\x8F\x98 | > >> Ctype(\x95ﾏ浬xB7\x89\x89\x8EZ\x8Eq) | \x83A\x 83N\x83Z\x83X\x8C\xA0 > >> ---+---+-- > >> -- > >> ---+---+ > >> +---+- > >> ---+-- > >> +---+- > >> ---+-- > >> +---+- > >> ---+-- > >> +---+- > >> ---+-- > >> +---+- > >> ---+-- > >> > >> postgres | postgres | UTF8 > >> | C | C > | > >> template0 | postgres | UTF8 > >> | C | C > >>| =c/postgr > >> es > >> > >> > >>: postgres= CTc/postgres > >> template1 | postgres | UTF8 > >> | C | C > >>| =c/postgr > >> es > >> > >> > >>: postgres= CTc/postgres > >> (3 行) > >> > >> > >> UTF8 > >> SJIS > >> psql:SJIS_TEST.SQL:5: NOTICE: CREATE TABLE / PRIMARY KEY > >> will create implicit i ndex "漢字テーブル_pkey" for table "漢字テーブル" > >> CREATE TABLE > >> INSERT 0 1 > >> INSERT 0 1 > >> へんかん | どうですか > >> + > >> さいとう | ひろし > >> こんにちは | ひろしです > >> (2 行) > >> > >> > >> DROP TABLE > >> > >> - Original Message - > >> From: "Martin Schäfer" > >> > >> > >> > Hello Hiroshi-san, > >> > > >> > The only differences I found between my source file and > >> yours are the file encoding > >> > (Unicode/SJIS), different #include-s and different > >> parameters to PQsetdblogin. I had to > >> > undo the last two changes to compile and run on my machine. > >> And the changed file encoding > >> > made no difference. It still fails on my machine. > >> > > >> > Regards, > >> > > >> > Martin > >> > > > ** > ** > > This email is confidential and may be privileged and should > not be used, read > > or copied by anyone who is not the original intended
Re: [BUGS] UTF-8 encoding failure
Okay, got it now. Unfortunately, the output is exactly the same as before: G:\Test\TestPostgreSQLJapanese>PostgreSQLJapaneseSJIS.exe localhost 5432 dev308 postgres postgres localhost 5432 dev308 postgres postgres Testing with encoding UTF-8: NOTICE: CREATE TABLE will create implicit sequence "蝗帛ｮ誉gid_seq" for serial column "蝗帛ｮ・gid" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "蝗帛ｮ誉pkey" for table "蝗帛ｮ・ PQfname(pResult, 4) returned incorrect field name. PQfname(pResult, 5) returned incorrect field name. PQfname(pResult, 6) returned incorrect field name. Testing with encoding SJIS: NOTICE: CREATE TABLE will create implicit sequence "四宗_gid_seq" for serial column "四宗.gid" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "四宗_pkey" for table "四宗" Query failed with error: ERROR: invalid byte sequence for encoding "UTF8": 0xe59eff HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". I'm beginning to believe that the problem is indeed in the combination of UK English operating system with Japanese locale. Maybe someone else could test this combination? Regards, Martin > -Original Message----- > From: Hiroshi Saito [mailto:z-sa...@guitar.ocn.ne.jp] > Sent: Friday, March 26, 2010 5:54 PM > To: Martin Schäfer; 'Takahiro Itagaki' > Cc: pgsql-bugs@postgresql.org > Subject: Re: [BUGS] UTF-8 encoding failure > > Hi. > > Eh, Have I missed something?_? > set Path=%Path%;C:\Program Files\PostgreSQL\8.4\bin; It is not this. > > Regards, > Hiroshi Saito > > - Original Message - > From: "Martin Schäfer" > To: "'Hiroshi Saito'" ; "'Takahiro > Itagaki'" > > Cc: > Sent: Saturday, March 27, 2010 12:46 AM > Subject: Re: [BUGS] UTF-8 encoding failure > > > > Can you copy libpq.dll to the same destination please? I > linked libpq as a static library > > into my project. > > > > Martin > > > >> -Original Message- > >> From: Hiroshi Saito [mailto:z-sa...@guitar.ocn.ne.jp] > >> Sent: Friday, March 26, 2010 4:35 PM > >> To: Martin Schäfer; 'Takahiro Itagaki' > >> Cc: pgsql-bugs@postgresql.org > >> Subject: Re: [BUGS] UTF-8 encoding failure > >> > >> Hi. > >> > >> I think that the locale is not desirable. However, It is not > >> related to this problem. > >> Can you test without editing this program? > >> http://winpg.jp/~saito/PGSQL_SJIS/ > >> I think that it passes. > >> > >> Regards, > >> Hiroshi Saito > >> > >> - Original Message - > >> From: "Martin Schäfer" > >> To: "'Hiroshi Saito'" ; "'Takahiro > >> Itagaki'" > >> > >> Cc: > >> Sent: Friday, March 26, 2010 11:49 PM > >> Subject: Re: [BUGS] UTF-8 encoding failure > >> > >> > >> > Hi, > >> > > >> > This worked fine, as you can see in the attached result. > >> However, I can see that my > >> > database has a different locale (English_United > >> Kingdom.1252) from yours (C). Can this > >> > cause the problem? > >> > > >> > Regards, > >> > > >> > Martin > >> > > >> >> -Original Message- > >> >> From: Hiroshi Saito [mailto:z-sa...@guitar.ocn.ne.jp] > >> >> Sent: Friday, March 26, 2010 3:36 PM > >> >> To: Martin Schäfer; 'Takahiro Itagaki' > >> >> Cc: pgsql-bugs@postgresql.org > >> >> Subject: Re: [BUGS] UTF-8 encoding failure > >> >> > >> >> Hi. > >> >> > >> >> Ahh ok, Would you try this. > >> >> > >> >> C:\home\HIROSHI>"C:\Program > >> >> Files\PostgreSQL\8.4\bin\psql.exe" -f SJIS_TEST.SQL > >> >> -p5433 postgres postgres > >> >> > >> >> データベ > >> >> ース一覧 > >> >> \x96\xBC\x91O | \x8F\x8A\x97L\x8E | > >> >> \x83G\x83\x93\x83R\x81[\x83f\x83B\x83\x93\x > >> >> 83O | \x8Fﾆ構x87\x8F\x87\x8F\x98 | > >> >> Ctype(\x95ﾏ浬xB7\x89\x89\x8EZ\x8Eq) | \x83A\x > 83N\x83Z\x83X\x8C\xA0 > >> >> ---+---+-- > >> >> -- > >> >> ---+-
Re: [BUGS] UTF-8 encoding failure
Hi Hiroshi-san, Now I'm convinced that my installation is messed up somehow and that this is not a bug in PostgreSQL. Thank you very much for taking the time trying to reproduce my problem. Regards, Martin -Original Message- From: Hiroshi Saito [mailto:z-sa...@guitar.ocn.ne.jp] Sent: 02 April 2010 17:34 To: Martin Schäfer; 'Takahiro Itagaki' Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] UTF-8 encoding failure Hi Martin-san. I prepared the environment which was alike in order to reproduce your problem. And a very comfortable result is obtained. http://winpg.jp/~saito/PGSQL_SJIS/InternationalVersionWindows/ Probably some of Japanese language setup is lacking. Regards, Hiroshi Saito - Original Message - From: "Hiroshi Saito" > Hi. > > Ummm, it is very strange > Can you do chcp command? > > C:\home\HIROSHI>chcp 932 > 現在のコード ページ: 932 > > Anyway, I want to prepare and check the same environment as you. > It may be that there is a shortage of environment for changing some. > the following things , but, It is hard to consider a problem... > http://download.microsoft.com/download/6/2/e/62e61f10-e106-4f4c-a862-63eec2b41efb/readme.htm > However, time is required. > > Regards, > Hiroshi Saito > > - Original Message - > From: "Martin Schäfer" > > >> Okay, got it now. >> >> Unfortunately, the output is exactly the same as before: >> >> G:\Test\TestPostgreSQLJapanese>PostgreSQLJapaneseSJIS.exe localhost 5432 >> dev308 postgres >> postgres >> localhost 5432 dev308 postgres postgres >> Testing with encoding UTF-8: >> NOTICE: CREATE TABLE will create implicit sequence "蝗帛ｮ誉gid_seq" for serial >> column "蝗帛ｮ・gid" >> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "蝗帛ｮ誉pkey" >> for table "蝗帛ｮ・ >> PQfname(pResult, 4) returned incorrect field name. >> PQfname(pResult, 5) returned incorrect field name. >> PQfname(pResult, 6) returned incorrect field name. >> >> Testing with encoding SJIS: >> NOTICE: CREATE TABLE will create implicit sequence "四宗_gid_seq" for serial >> column "四宗.gid" >> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "四宗_pkey" for >> table "四宗" >> Query failed with error: >> ERROR: invalid byte sequence for encoding "UTF8": 0xe59eff >> HINT: This error can also happen if the byte sequence does not match the >> encoding >> expected by the server, which is controlled by "client_encoding". >> >> I'm beginning to believe that the problem is indeed in the combination of UK >> English >> operating system with Japanese locale. Maybe someone else could test this >> combination? >> >> Regards, >> >> Martin > This email is confidential and may be privileged and should not be used, read or copied by anyone who is not the original intended recipient. If you have received this email in error please inform the sender and delete it from your mailbox or any other storage mechanism. Unless specifically stated, nothing in this email constitutes an offer by Cadcorp and Cadcorp does not warrant that any information contained in this email is accurate. Cadcorp cannot accept liability for any statements made which are clearly the sender's own and not expressly made on behalf of Cadcorp or one of its agents. Please rely on your own virus check. No responsibility is taken by Cadcorp for any damage arising out of any bug or virus infection. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] [9.0beta5/cvs head] build failure due to unchecked results
Hello PostgreSQL developers,
9.0beta5 seems to enable -Werror by default (which is a good thing,
thanks!). FORTIFY_SOURCE catches a few places where the result of
write() and fgets() is not checked, and thus the build fails with
gcc -g -O2 -g -Wall -O2 -fPIC -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g
-Werror -I../../../../src/include -D_GNU_SOURCE -I/usr/include/libxml2
-I/usr/include/tcl8.5 -c -o elog.o elog.c
cc1: warnings being treated as errors
elog.c: In function 'write_console':
elog.c:1698: error: ignoring return value of 'write', declared with attribute
warn_unused_result
elog.c: In function 'write_pipe_chunks':
elog.c:2390: error: ignoring return value of 'write', declared with attribute
warn_unused_result
elog.c:2399: error: ignoring return value of 'write', declared with attribute
warn_unused_result
make[4]: *** [elog.o] Error 1
[...]
gcc -g -O2 -g -Wall -O2 -fPIC -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g
-Werror -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -fpic
-DFRONTEND -DUNSAFE_STAT_OK -I. -I../../../src/include -D_GNU_SOURCE
-I/usr/include/libxml2 -I/usr/include/tcl8.5 -I../../../src/port
-I../../../src/port -DSO_MAJOR_VERSION=5 -c -o fe-auth.o fe-auth.c
gcc -g -O2 -g -Wall -O2 -fPIC -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g
-Werror -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -fpic
-DFRONTEND -DUNSAFE_STAT_OK -I. -I../../../src/include -D_GNU_SOURCE
-I/usr/include/libxml2 -I/usr/include/tcl8.5 -I../../../src/port
-I../../../src/port -DSO_MAJOR_VERSION=5 -c -o fe-connect.o fe-connect.c
cc1: warnings being treated as errors
fe-connect.c: In function ‘PasswordFromFile’:
fe-connect.c:4403: error: ignoring return value of ‘fgets’, declared with
attribute warn_unused_result
etc.
I attach a patch (against git head) to check the results of those. For
src/bin/psql/common.c this is really just an "ignore the result", but
in src/bin/psql/prompt.c it actually fixes a potential crash.
Thank you for considering!
Martin
--
Martin Pitt| http://www.piware.de
Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org)
From 398fc97d911cfedea5204eba584ce1e589d2b2b0 Mon Sep 17 00:00:00 2001
From: Martin Pitt
Date: Fri, 30 Apr 2010 12:43:13 +0200
Subject: [PATCH] Check results from fgets() and write() calls.
While most of them are harmless, they lead to a build failure with -Werror
(which is enabled by default in alpha releases). The one in fe-connect.c fixes
a real potential crasher, though.
---
src/backend/utils/error/elog.c| 12 +---
src/bin/psql/common.c |6 +-
src/bin/psql/prompt.c |3 ++-
src/interfaces/libpq/fe-connect.c |3 ++-
4 files changed, 18 insertions(+), 6 deletions(-)
diff --git a/src/backend/utils/error/elog.c b/src/backend/utils/error/elog.c
index 1b1e3e9..59c85f8 100644
--- a/src/backend/utils/error/elog.c
+++ b/src/backend/utils/error/elog.c
@@ -1653,6 +1653,8 @@ write_eventlog(int level, const char *line, int len)
static void
write_console(const char *line, int len)
{
+ int res;
+
#ifdef WIN32
/*
@@ -1695,7 +1697,8 @@ write_console(const char *line, int len)
*/
#endif
- write(fileno(stderr), line, len);
+ res = write(fileno(stderr), line, len);
+ Assert(res == len);
}
/*
@@ -2375,6 +2378,7 @@ write_pipe_chunks(char *data, int len, int dest)
PipeProtoChunk p;
int fd = fileno(stderr);
+ int res;
Assert(len > 0);
@@ -2387,7 +2391,8 @@ write_pipe_chunks(char *data, int len, int dest)
p.proto.is_last = (dest == LOG_DESTINATION_CSVLOG ? 'F' : 'f');
p.proto.len = PIPE_MAX_PAYLOAD;
memcpy(p.proto.data, data, PIPE_MAX_PAYLOAD);
- write(fd, &p, PIPE_HEADER_SIZE + PIPE_MAX_PAYLOAD);
+ res = write(fd, &p, PIPE_HEADER_SIZE + PIPE_MAX_PAYLOAD);
+ Assert(res == PIPE_HEADER_SIZE + PIPE_MAX_PAYLOAD);
data += PIPE_MAX_PAYLOAD;
len -= PIPE_MAX_PAYLOAD;
}
@@ -2396,7 +2401,8 @@ write_pipe_chunks(char *data, int len, int dest)
p.proto.is_last = (dest == LOG_DESTINATION_CSVLOG ? 'T' : 't');
p.proto.len = len;
memcpy(p.proto.data, data, len);
- write(fd, &p, PIPE_HEADER_SIZE + len);
+ res = write(fd, &p, PIPE_HEADER_SIZE + len);
+ Assert(res == PIPE_HEADER_SIZE + len);
}
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index f605c97..695817e 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -219,7 +219,9 @@ static PGcancel *volatile cancelConn = NULL;
static CRITICAL_SECTION cancelConnLock;
#endif
-#define write_stderr(str) write(fileno(stderr), str, strlen(str))
+ /* ignore result of write(); it can't
Re: [BUGS] [9.0beta5/cvs head] build failure due to unchecked results
Peter Eisentraut [2010-04-30 14:56 +0300]: > You probably mean alpha5, unless you come from the future. ;-) FYI, those are next week's lottery numbers: 12, 19, ... Right, of course I mean alpha-5, sorry. > That was actually a mistake in the packaging Oh, I see. Well, for a mistake the code is surprisingly well-behaved. Those three or four patch hunks is all it takes to make it build, and sometimes those warnings are indeed useful; if for nothing else, then to make you think explicitly why it's okay to ignore a short or failed write(). > I suggest you hold off for a day or two and wait for beta1. Sure, thank you! Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) signature.asc Description: Digital signature
Re: [BUGS] [9.0beta5/cvs head] build failure due to unchecked results
Tom Lane [2010-04-30 12:51 -0400]: > I concur, those two changes look worthwhile. The proposed Assert() > additions are right out, though, as they would turn write failures > into database crashes. Right, that might be too strong. > The current code doesn't even think that such a failure is worth > testing for, so that's surely an overreaction. (And in any case, if > Asserts are disabled, this change would fail to suppress the > warning, no?) It seems gcc is happy enough if you assign the returned value to a variable. At least I have done a build without --enable-cassert (where the entire Assert() was thrown away), and it didn't complain about the unchecked result any more. I guess that heuristics gets it only so far.. Thanks, Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] bug report
POSTGRESQL BUG REPORT TEMPLATE Your name : Martin Renters Your email address : [EMAIL PROTECTED] System Configuration - Architecture (example: Intel Pentium) : Intel Pentium Operating System (example: Linux 2.0.26 ELF) : Solaris X86 2.6 PostgreSQL version (example: PostgreSQL-6.5.1): PostgreSQL-7.0.0 snapshot 2000/04/27 Compiler used (example: gcc 2.8.0) : gcc 2.7.2.3 Please enter a FULL description of your problem: Backend crash with attached script The original issue that triggered this was an attempt to implement field auditing in 7.0beta3. It worked fine as long as the fields were not NULL. I've simplified the code as much as possible to only print values rather than writing them into an audit table. In particular, in the trigger the values printed for old and new are correct if the field being updated is non NULL. If either the old or new value for the field is NULL then the values printed in the notice are wrong. i.e. originalnew log 1 2 old=1, new=2 NULL3 old=, new= 4 NULLold=, new= The actual value stored as a result of the update is correct, only the messages printed are wrong (as are any calculations done inside the function). Please describe a way to repeat the problem. Please try to provide a concise reproducible example, if at all possible: -- DROP FUNCTION CMP_INT(INTEGER, INTEGER); CREATE FUNCTION CMP_INT(INTEGER, INTEGER) RETURNS BOOL AS ' BEGIN RAISE NOTICE ''cmp_int:old value=%, new value=%'', $1, $2; return ''t''; END; ' LANGUAGE 'plpgsql'; DROP TABLE TEST; CREATE TABLE TEST ( DF_RECIDINTEGER, DF_MODIDINTEGER ); DROP FUNCTION TEST_AUDIT_PROC(); CREATE FUNCTION TEST_AUDIT_PROC() RETURNS OPAQUE AS ' BEGIN RAISE NOTICE ''test_audit_proc():old=%, new=%'', OLD.DF_RECID, NEW.DF_RECID; PERFORM CMP_INT(OLD.DF_RECID, NEW.DF_RECID); return new; end; ' language 'plpgsql'; CREATE TRIGGER TEST_AUDIT BEFORE UPDATE ON TEST FOR EACH ROW EXECUTE PROCEDURE TEST_AUDIT_PROC(); INSERT INTO TEST VALUES(1,1); INSERT INTO TEST VALUES(2,2); INSERT INTO TEST VALUES(NULL, 3); SELECT * FROM TEST; UPDATE TEST SET DF_RECID=123 WHERE DF_MODID=1; SELECT * FROM TEST; UPDATE TEST SET DF_RECID=456 WHERE DF_MODID=3; If you know how this problem might be fixed, list the solution below: -
[BUGS] Number of tuples in pg_class - bug or misunderstanding?
I'm just digging a little bit in PostgreSQL internals and I found the following thing. Shouldn't the reltuples field in pg_class contain the real number of tuples? It is always a little bit to low ... irc=# SELECT reltuples FROM pg_class WHERE relname = 'events'; reltuples --- 188 (1 row) irc=# SELECT COUNT(*) FROM events; count --- 195 (1 row) This annoys me a little bit as I can do SELECT reltuples FROM pg_class WHERE relname = $1; from plpgsql, but not SELECT COUNT(*) FROM $1; -- Martin Neumann Appartement 201, Emil-Figge-Str. 9, 44227 Dortmund, Germany [EMAIL PROTECTED] - http://www.mne.de/ - PGP key @ http://www.mne.de/mne/pgp.txt
[BUGS] CLUSTER makes table vanish :(
Hi everybody, I don't seem to have a copy of the bug template, so here goes. In the process of trying to optimize some queries I'm doing, I tried a CLUSTER command: cluster track_instance_idx2 on track_instance; It failed with this error: ERROR: temp_258c0 is an index relation Now I wasn't all that surprised because it was the first time I've tried to use CLUSTER. The surprising thing is that after doing this, the track_instance table vanished. Any reference to it gives me ERROR: Relation 'track_instance' does not exist This is somewhat troubling, as I'm lazy and don't have a recent dump handy. it appears to have dissapeared from pg_tables: djukebox=# select * from pg_tables where tablename = 'track_instance'; tablename | tableowner | hasindexes | hasrules | hastriggers ---+++--+- (0 rows) But is still in /usr/local/pgsql/data/base/djukebox: garth:/usr/local/pgsql/data/base/djukebox# ls track_instance* track_instance So the file is still there, but none of my indices for that table are. (They all started with track_instance as well). Can anyone shed some light on this situation? I'm running pogstres 7.0.0 on a reasonably up to date debian gnu/linux system (2.2.16 kernel, libc 2.0.7, etc). thanks, Brian
Re: [BUGS] Error with union in sub-selects
Hello, some additional informations: - if i execute the subselect alone, it works fine !. - The same select statement works with sybase and oracle, so i think its a legal statement. - After reading in the sql2-standard, i have found nothing which restricts unions in sub-selects. (I don't want to use another rdbms ... i want to use PostgreSQL :-) If somebody has a workaround, then i am the happiest person for the day. cu Martin -- Martin Neimeier Ingenieur-Buero Neimeier Schwarzach / Germany mailto:[EMAIL PROTECTED] / http://www.ibn.de (under heavy reconstruction) Tel:+49(6262)912344 / Fax:+49(6262)912347
[BUGS] 7.1 released on 2000-09-01?
Hi, [[EMAIL PROTECTED] doesn't seem to accept this report, so I send it to you.] The PostgreSQL Administrator's Guide says in the release notes: Release 7.1 Release date 2000-09-01. I wish that was true! Unfortunately, everwhere else on the site the latest release is 7.0.3. See for your self: http://www.postgresql.org/docs/admin/release.htm I guess you better correct this (of explain it). Bye, Edwin Martin. --- Surf Edwin Martin's brainwaves: http://www.bitstorm.org/edwin/
[BUGS] Comment bug in psql
Hi!
I just found a bug in psql 7.0.3 (a FreeBSD 4.2 port).
1 db=# /* Foo bar
2 db*#gazonk's gurka
3 db'#2001*/
4 db'# CREATE TABLE foo (bar INTEGER);
5 db'# '
6 db*# */
7 db-# CREATE TABLE gazonk (blirp INTEGER);
8 ERROR: Unterminated quoted string
1 Prompt is =, ok
Start a comment
2 Prompt changes to *, ok
Write more comments and include an apostrofe (')
3 Prompt changes to ', ERROR! You shouldn't enter this mode because of an
apostrofe in commented text.
Write more comments and end them, ERROR! The end of the comments aren't
recognized.
The rest, 4-8, will be interpreted incorrectly.
/Martin Olsson Sentor AB
"There is at least one incorrect statement in this text,
but it may be this one!"
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[BUGS] triggers
Hi, I have a problem I am using postgresql database and I want to enforce >triggers between my related tables ( tables with relationship), one table >has a foreign key and the other the a primary key, I would like to use >triggers to update, delete, and insert in this tables but the table with the >foreign key should first check in the other table before any action is >taken, Which is the simplest sql or C language script can I use to enforce >triggers within my tables, please demonstrate a simple scenerio thanks Martin Wainaina Kuria Programmer/Database Administrator Bsc. Computer Science Kenyaweb.com Ltd www.kenyaweb.com _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] postgres crash
hi, we had a server crash today - had to fsck and postgres is corrupted now if i try a pg_dump i get: inetmain:/tmp/tests$ pg_dump -c -v -d factline1 > /tmp/factline1_20011025_144800.sql -- saving database definition getDatabase(): SELECT failed. Explanation from backend: 'ERROR: pg_atoi: error in "t": can't parse "t" '. i get the same error if i try to dump a table. i deleted all indexes as there were lot's of problems with them but this didn't help ether. i changed src/backend/access/transam/xlog.c from if (XLByteLT(LogwrtResult.Flush, record)) elog(STOP, "XLogFlush: request is not satisfied"); break; to if (XLByteLT(LogwrtResult.Flush, record)) elog(DEBUG, "XLogFlush: request (%u, %u) is not satisfied --- flushed to (%u, %u)", record.xlogid, record.xrecoff, LogwrtResult.Flush.xlogid, LogwrtResult.Flush.xrecoff); break; because otherwise postgres wouldn't even start. my question: how can i either get a dump of my data or a running database back. i did a backup of /var/lib/postgresql/data before removing indexes so i still have the version after the crash. tia martin -- factline Krisper Fabro Harnoncourt OEG (www.factline.com) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] problems after server crash
hi, 1. unfortunately i can't subscribe to the mailinglist - it doesn't send me the confirmation mail so please cc me. 2. we had a server crash today - had to fsck and postgres is corrupted now if i try a pg_dump i get: inetmain:/tmp/tests$ pg_dump -c -v -d factline1 > /tmp/factline1_20011025_144800.sql -- saving database definition getDatabase(): SELECT failed. Explanation from backend: 'ERROR: pg_atoi: error in "t": can't parse "t" '. i get the same error if i try to dump a table. i deleted all indexes as there were lot's of problems with them but this didn't help ether. i changed src/backend/access/transam/xlog.c from if (XLByteLT(LogwrtResult.Flush, record)) elog(STOP, "XLogFlush: request is not satisfied"); break; to if (XLByteLT(LogwrtResult.Flush, record)) elog(DEBUG, "XLogFlush: request (%u, %u) is not satisfied --- flushed to (%u, %u)", record.xlogid, record.xrecoff, LogwrtResult.Flush.xlogid, LogwrtResult.Flush.xrecoff); break; because otherwise postgres wouldn't even start. my question: how can i either get a dump of my data or a running database back. i did a backup of /var/lib/postgresql/data before removing indexes so i still have the version after the crash. tia martin -- factline Krisper Fabro Harnoncourt OEG (www.factline.com) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] help!
I would just want to ask if what is the default password of postgresql after installing it... coz when i type su postgres it ask me a password which i dont know what? Thnx -- ___ Sign-up for your own FREE Personalized E-mail at Email.com http://www.email.com/?sr=signup ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[BUGS] stopping server does not remove external_pid_file
Hi PostgreSQL developers, I just played around with external_pid_file in 8.1.4. I noticed that the file is created and filled properly, but it is not removed again when the server is stopped. Thank you, Martin -- Martin Pitthttp://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org In a world without walls and fences, who needs Windows and Gates? signature.asc Description: Digital signature
[BUGS] Fwd: Bug#372115: Last security update of postgresql-contrib breaks database replication with DBMirror.pl
Hi PostgreSQL gurus,
we recently received this bug report after we upgraded Debian's stable
release to the equivalent of 7.4.13 (with the fixes for quote
escaping).
Does anyone know DBMirror.pl? The proposed fix seems wrong since it
just reverts the behavior to the old quote escaping style.
Thank you in advance for any idea,
Martin
- Forwarded message from Olivier Bornet <[EMAIL PROTECTED]> -
Subject: Bug#372115: Last security update of postgresql-contrib breaks
database replication with DBMirror.pl
Reply-To: Olivier Bornet <[EMAIL PROTECTED]>, [EMAIL PROTECTED]
From: Olivier Bornet <[EMAIL PROTECTED]>
To: Debian Bug Tracking System <[EMAIL PROTECTED]>
Date: Thu, 08 Jun 2006 12:30:55 +0200
X-Spam-Status: No, score=1.3 required=4.0 tests=BAYES_50,DATE_IN_FUTURE_06_12
autolearn=no version=3.0.3
Package: postgresql-contrib
Version: 7.4.7-6sarge2
Severity: critical
Justification: causes serious data loss
Hello,
using version 7.4.7-6sarge2 of postgresql-contrib cause trouble in
database replication using /usr/lib/postgresql/bin/DBMirror.pl
The problem I have found is if there is a ' character (the single quote)
in the data. In this case, the single quote (') is replaced by two
single quotes ('') in the table PendingData. This cause the replication
process to stop with a message "Error in PendingData Sequence Id XXX".
To replicate the non-replicated data, I have run a patched version of
DBMirror.pl. Here is my patch (mainly replacing the two single quotes by
a backslash and one single quote, this mean '' -> \'. Execepted if there
is a equal before, this mean don't replace ='') :
--- /usr/lib/postgresql/bin/DBMirror.pl 2005-05-18 10:33:34.0 +0200
+++ ./DBMirror.pl 2006-06-08 11:53:39.0 +0200
@@ -827,6 +827,9 @@
$fnumber = 4;
my $dataField = $pendingResult->getvalue($currentTuple,$fnumber);
+ # replace all the '' to \' in the texts
+ $dataField =~ s/([^=])\'\'/\1\\\'/g;
+
while(length($dataField)>0) {
# Extract the field name that is surronded by double quotes
$dataField =~ m/(\".*?\")/s;
I'm sure this patch is not enough, because this don't take in account if
the data has in it something like "=''". I think the part to patch is
not the DBMirror.pl, but the "recordchange" procedure called by the
trigger on each data change.
Reverting postgresql-contrib to version 7.4.7-6sarge1 correct the
problem only if you have nothing in the Pending table.
Thanks for your attention, and have a nice day.
Oliver
- End forwarded message -
--
Martin Pitthttp://www.piware.de
Ubuntu Developer http://www.ubuntu.com
Debian Developer http://www.debian.org
In a world without walls and fences, who needs Windows and Gates?
signature.asc
Description: Digital signature
Re: [BUGS] Bug#372115: Last security update of postgresql-contrib breaks database replication with DBMirror.pl
Hi PostgreSQL gurus, hi Olivier,
Martin Pitt [2006-06-16 0:15 +0200]:
> Upstream confirmed my reply in the last mail in [1]: the complete
> escaping logic in DBMirror.pl is seriously screwew.
>
> [1] http://archives.postgresql.org/pgsql-bugs/2006-06/msg00065.php
I finally found some time to debug this, and I think I found a better
patch than the one you proposed. Mine is still hackish and is still a
workaround around a proper quoting solution, but at least it repairs
the parsing without introducing the \' quoting again.
I consider this a band-aid patch to fix the recent security update.
PostgreSQL gurus, would you consider applying this until a better
solution is found for DBMirror.pl?
Olivier, can you please confirm that the patch works for you, too?
Thank you,
Martin
--
Martin Pitthttp://www.piware.de
Ubuntu Developer http://www.ubuntu.com
Debian Developer http://www.debian.org
In a world without walls and fences, who needs Windows and Gates?
--- /usr/lib/postgresql/bin/DBMirror.pl 2006-06-27 20:39:34.0 +0200
+++ DBMirror.pl 2006-06-27 22:21:05.0 +0200
@@ -852,7 +852,7 @@
$matchString = $1;
$value .= substr $matchString,0,length($matchString)-1;
- if($matchString =~ m/(\'$)/s) {
+ if($matchString =~ m/(\'$)/s and (substr
$dataField,length($matchString),1) ne "'") {
# $1 runs to the end of the field value.
$dataField = substr $dataField,length($matchString)+1;
last;
signature.asc
Description: Digital signature
[BUGS] Fwd: Bug#380047: libpq-dev: no declaration for pg_encoding_to_char
Hi PostgreSQL developers,
I recently got this bug report about a missing declaration of
pg_encoding_to_char() in the official client library headers
(PostgreSQL 8.1.4). This causes some build problems of depending
packages like the ruby bindings [1].
So, should this function be declared in libpq-fe.h (or pg_wchar.h
included into the postgresql client development package), or is this
function obsolete?
Thank you!
Martin
[1] http://bugs.debian.org/18858
- Forwarded message from Matej Vela <[EMAIL PROTECTED]> -
Subject: Bug#380047: libpq-dev: no declaration for pg_encoding_to_char
Reply-To: Matej Vela <[EMAIL PROTECTED]>, [EMAIL PROTECTED]
From: Matej Vela <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Date: Thu, 27 Jul 2006 06:45:37 +0200
X-Spam-Status: No, score=2.7 required=4.0 tests=AWL,BAYES_50,
DATE_IN_FUTURE_06_12 autolearn=no version=3.0.3
Package: libpq-dev
Version: 8.1.4-5
Chapter 21.2.3 ("Automatic Character Set Conversion Between Server and
Client") documents several functions:
int PQsetClientEncoding(PGconn *conn, const char *encoding);
where conn is a connection to the server, and encoding is the
encoding you want to use. If the function successfully sets the
encoding, it returns 0, otherwise -1. The current encoding for this
connection can be determined by using:
int PQclientEncoding(const PGconn *conn);
Note that it returns the encoding ID, not a symbolic string such as
EUC_JP. To convert an encoding ID to an encoding name, you can use:
char *pg_encoding_to_char(int encoding_id);
The first two are declared in , but pg_encoding_to_char()
seems to be in , which is not included in the package.
If this interface is private or deprecated, consider this a
documentation bug.
Thanks,
Matej
- End forwarded message -
--
Martin Pitthttp://www.piware.de
Ubuntu Developer http://www.ubuntu.com
Debian Developer http://www.debian.org
In a world without walls and fences, who needs Windows and Gates?
signature.asc
Description: Digital signature
Re: [BUGS] Fwd: Bug#380047: libpq-dev: no declaration for pg_encoding_to_char
Hi Alvaro, Alvaro Herrera [2006-07-29 21:41 -0400]: > The correct solution seems to be that the libpq-dev package must include > mb/pg_wchar.h. Many thanks for your explanations. This is trivial to do. > > [1] http://bugs.debian.org/18858 > > I find this one more closely related to the topic at hand: > > http://bugs.debian.org/318858 Erm, yes, yay for my typos. :/ /me looks on the floor for a dropped '3'. Have a nice Sunday, Martin -- Martin Pitt http://www.piware.de Ubuntu Developer http://www.ubuntulinux.org Debian Developerhttp://www.debian.org signature.asc Description: Digital signature
[BUGS] Fwd: Bug#390730: postgresql-8.1: segfaults on huge litteral IN clauses
Hi PostgreSQL developers, I recently got the Debian bug report below, a server crash with huge IN clauses. Benoît did not give a concrete example of how to actually cause the crash, just the recipe. I was able to reproduce the crash with echo "select count(*) from foo where id in (`seq -s ',' 1 10`)" | psql test with 8.1.4. I also tried it with 8.2 beta 1, which worked just fine (I did not even need to tweak the max stack size). It also works fine with 8.2b1 with s/count(*)/*/. To be absolutely sure I wrote a small perl script which produces a (bad) random permutation and tested again: echo "select count(*) from foo where id in (`./perm.pl 10`)" | psql test still works with 8.2. So, it's not the worst bug in the world and seemingly fixed in 8.2, but depending on whether a local authenticated crash is considered a security issue, it might be worth fixing in 8.1 (even if it's just a small test for an upper bound for IN clauses?) Thank you in advance, Martin - Forwarded message from Benoît Dejean <[EMAIL PROTECTED]> - Subject: Bug#390730: postgresql-8.1: segfaults on huge litteral IN clauses Reply-To: Benoît Dejean <[EMAIL PROTECTED]>, [EMAIL PROTECTED] From: Benoît Dejean <[EMAIL PROTECTED]> To: Debian Bug Tracking System <[EMAIL PROTECTED]> Date: Mon, 02 Oct 2006 21:11:52 +0200 X-Spam-Status: No, score=0.0 required=4.0 tests=BAYES_50 autolearn=no version=3.0.3 Package: postgresql-8.1 Version: 8.1.4-7 Severity: normal Hi, when doing HUGE IN, i get a segfault on the server. By HUGE i mean 100k. At work, we have developped a (poor) db mapping which makes intensive use of litteral IN. Like select Foo from Bar where id in (1, 5, 3, 8); where the in clause is too big, the server segfaults. Here's a simple testcase : CREATE TABLE foo (id SERIAL PRIMARY KEY, v INTEGER); CREATE FUNCTION fill_foo(INTEGER) RETURNS INTEGER AS $$ DECLARE n ALIAS FOR $1; BEGIN FOR i in 1..n LOOP INSERT INTO foo (v) values(i); END LOOP; RETURN COUNT(*) FROM foo; END; $$ LANGUAGE plpgsql; select * from fill_foo(100); then i generated a huge (100k elements) and shuffled list of ids : select * from foo where id in (134391, 680297, 90974, 305561, 319569, 411883, ... On first run, i got an error : psql:./foo:1: ERREUR: dépassement de limite (en profondeur) de la pile HINT: Augmenter le paramètre «max_stack_depth».) so i increased max_stack_depth to 8192. That the only change is did to debian standard configuration. then : LC_ALL=C psql -U benoit benoit -f ./foo psql:./foo:1: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. psql:./foo:1: connection to server was lost and the server log is : LOG: processus serveur (PID 19640) a été arrêté par le signal 11OB LOG: Arrêt des autres processus serveur actifs LOG: Tous les processus serveur se sont arrêtés, réinitialisation LOG: le système de bases de données a été interrompu à 2006-10-02 20:36:59 CEST LOG: l'enregistrement du point de vérification est à 0/7A3BCB8 LOG: ré-exécution de l'enregistrement à 0/7A3BCB8 ; l'annulation de l'enregistrement est à 0/0 ; arrêt TRUE LOG: prochain identifiant de transaction : 852 ; prochain OID : 16459 LOG: prochain MultiXactId: 1; prochain MultiXactOffset: 0 LOG: le système de bases de données n'a pas été arrêté proprement ; restauration automatique en cours LOG: enregistrement de longueur nulle sur 0/7A3BD00 LOG: la ré-exécution n'est pas requise LOG: le système de bases de données est prêt LOG: La limite de réinitialisation de l'ID de transaction est 2147484146, limité par la base de données «postgres» sorry, it's in french but the first line means that serveur process got killed by signal 11. Only a child dies, the server remains functionnal. I can reproduce it on this sid/ppc and i386/etch. I know this kind of huge IN clause is a bit silly but silly SQL shouldnot crash my dear postgres :) Thanks; Offtopic : i understand that postgres does N bitmap scans when running a IN(1, 4, 3) clause. This is painfully slow. Why don't postgres optimize this in order to perform a seq scan ? benoit=> EXPLAIN ANALYZE SELECT * FROM foo WHERE id in (1, 2, 3); QUERY PLAN - Bitmap Heap Scan on foo (cost=6.01..17.57 rows=3 width=8) (actual time=31.979..31.990 rows=3 loops=1) Recheck Cond: ((id = 1) OR (id = 2) OR (id = 3)) -> BitmapOr (cost=6.01..6.01 rows=3 width=0) (actual time=23.462..23.462 rows=0 loops=1) -> Bitmap Index Scan on foo_pkey (cost=0.00..2.00 rows=1 width=0) (actual time=23.367..23.367 rows=1 loops=1) Index Cond: (id
Re: [BUGS] BUG #2830: Wrong results for prepared statements while clustering target table
The short answer is "don't CLUSTER while the table is in live use" ... This is kind of difficult on a busy database, more so if it's a 24x7 environment. And unfortunately there aren't any good alternatives either. The difference between EXECUTE and SELECT behavior here is just a chance matter of exactly where the snap is taken during the parse/execute code path --- your SELECT works because it blocks for AccessShareLock on the table before it sets the snap. But SELECT would fail just the same way within a serializable transaction that had already set its snapshot. Ok, makes sense. The same reasoning probably applies to INSERT and UPDATE as well. Still the problem remains - how to cluster a table on a busy system without losing data or getting wrong results. Perhaps the issue should be documented, although a fix would be preferrable ;) Martin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #2971: 8.1.7/8.2.2 break constraint checking for 'update'
The following bug has been logged online:
Bug reference: 2971
Logged by: Martin Pitt
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.2.2
Operating system: Linux
Description:8.1.7/8.2.2 break constraint checking for 'update'
Details:
Originally reported on https://launchpad.net/bugs/83505:
db> create table foo (bar VARCHAR(20) NOT NULL check (bar in
('FOO','BAR')));
CREATE TABLE
db> insert into foo (bar) values ('FOO');
INSERT 0 1
db> update foo set bar = 'BAR';
ERROR: attribute 1 has wrong type
DETAIL: Table has type character varying, but query expects character
varying.
This worked in the previous versions (8.2.1/8.1.6). I checked that it does
not affect 7.4.16.
Thank you!
---(end of broadcast)---
TIP 1: 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] Strange "Table has type character varying, but query expects character varying" errors
Hi Ümit, Ümit Öztosun [2007-02-06 17:57 +0200]: > Today two of our production servers developed the following error(s): > > ERROR: attribute X(semi-random number here) has wrong type > DETAIL: Table has type character varying, but query expects character > varying. > > They were running PostgreSQL 8.1.4 on Ubuntu Dapper, dual-core Intel > Machines. Right, that is the same issue I already reported to this list, and is already reported in Launchpad, too: https://launchpad.net/bugs/83505 Martin -- Martin Pitthttp://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org signature.asc Description: Digital signature
Re: [BUGS] BUG #2971: 8.1.7/8.2.2 break constraint checking for 'update'
Hi Tom,
Tom Lane [2007-02-06 11:25 -0500]:
> "Martin Pitt" <[EMAIL PROTECTED]> writes:
> > db> create table foo (bar VARCHAR(20) NOT NULL check (bar in
> > ('FOO','BAR')));
> > CREATE TABLE
>
> > db> insert into foo (bar) values ('FOO');
> > INSERT 0 1
>
> > db> update foo set bar = 'BAR';
> > ERROR: attribute 1 has wrong type
> > DETAIL: Table has type character varying, but query expects character
> > varying.
>
> Sigh. The trouble with security patches is that by nature they can't
> get very wide testing :-(. I think we shall have to do something like
> the attached. Arguably this problem is exposing bugs elsewhere in the
> system, but for now ExecEvalVar() is going to have to be less
> aggressive.
I confirm that this patch fixes the problem.
Thanks for the fast reaction!
Martin
--
Martin Pitthttp://www.piware.de
Ubuntu Developer http://www.ubuntu.com
Debian Developer http://www.debian.org
signature.asc
Description: Digital signature
Re: [BUGS] Strange "Table has type character varying, but query expects character varying" errors
Hi Ümit, Ümit Öztosun [2007-02-06 17:57 +0200]: > Today two of our production servers developed the following error(s): > > ERROR: attribute X(semi-random number here) has wrong type > DETAIL: Table has type character varying, but query expects character > varying. > > They were running PostgreSQL 8.1.4 on Ubuntu Dapper, dual-core Intel > Machines. Right, that is the same issue I already reported to this list, and is already reported in Launchpad, too: https://launchpad.net/bugs/83505 Martin -- Martin Pitthttp://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org signature.asc Description: Digital signature
[BUGS] postmaster 8.2 eternally hangs in sempaphore lock acquiring
Hi, Since our Launchpad developers switched from 8.1 to 8.2.3, they often encounter a situation when the postmaster gets stuck and needs to be restarted. This happens on various CREATE commands (FUNCTION, DATABASE, not consistently). The backtraces show that the process doing the CREATION, another client connection that InitPostgres(), and the autovacuum daemon all hang eternally in PGSemaphoreLock(), so I suspect that there is a race condition of some sort? I tried to reproduce this with an hour-long run of CREATE/DROP DATABASE, but without success. It occurred to me that the process which does the CREATE is in _bt_vacuum_cycleid(), is that also related to the autovacuum daemon? I asked the original reporter (Mark Shuttleworth, in CC) to disable the autovacuum daemon for testing. https://launchpad.net/bugs/93042 has symbolic gdb backtraces of all three processes that are involved. At this point I am not sure which further information I could provide. Mark would be happy provide more information and/or directly talk with you. Thanks in advance, Martin -- Martin Pitthttp://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org signature.asc Description: Digital signature
Re: [BUGS] postmaster 8.2 eternally hangs in sempaphore lock acquiring
Hi Tom, hi Mark, Tom, thank you for having a look into this! Tom Lane [2007-03-29 13:49 -0400]: > Martin Pitt <[EMAIL PROTECTED]> writes: > > https://launchpad.net/bugs/93042 has symbolic gdb backtraces of all > > three processes that are involved. > > Are these really all the processes involved? The createdb process and > the autovac process are both waiting for someone else to give up the > BtreeVacuumLock, but that is never held for any long period, and it's > certainly not held by the guy trying to do InitPostgres. There are more processes, unfortunately I don't have backtraces of them. I got this from my IRC log: 15928 ?Ss 0:00 postgres: mark launchpad_ftest_template [local] CREATE DATABASE 15956 ?Ss 0:00 postgres: session session_dev [local] idle 15957 ?Ss 0:00 postgres: launchpad launchpad_dev [local] idle 15958 ?Ss 0:00 postgres: session session_dev [local] idle 15969 ?Ss 0:00 postgres: launchpad launchpad_dev [local] idle 16014 ?Ss 0:00 postgres: launchpad launchpad_dev [local] idle 16273 ?Ss 0:00 postgres: mark launchpad_ftest_template [local] startup waiting > I believe that the guy trying to do InitPostgres is blocked by the > createdb process --- it looks like he's trying to attach to the same > DB being used as a template for the createdb, and as of 8.2 we lock out > new entries to a template DB until the copy is complete. > > It's possible that this is not a deadlock per se, but the aftermath of > someone having errored out without releasing the BtreeVacuumLock --- but > I don't entirely see how that could happen either, at least not without > a core dump scenario. > > Is there anything in the postmaster log when this happens? Errors out > of _bt_start_vacuum would be particularly interesting... I believe Mark's postgres runs with fully verbose logging. Mark, can you please have a look? Thanks, Martin -- Martin Pitthttp://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org signature.asc Description: Digital signature
Re: [BUGS] postmaster 8.2 eternally hangs in sempaphore lock acquiring
Hi Heikki, Heikki Linnakangas [2007-03-30 8:57 +0100]: > Martin: Would it be possible for you to reproduce the problem with a > patched version? I cannot reproduce the problem myself, but I can easily build a package with this patch, hand it to Mark, and ask him to test it. Thanks a lot! Martin -- Martin Pitthttp://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org signature.asc Description: Digital signature
[BUGS] Fwd: Bug#425125: postgresql-8.1: localhost does not work for unix sockets in ~/.pgpass
Hello PostgreSQL developers, I just got the bug report below from a Debian user. The comment on http://www.postgresql.org/docs/8.1/interactive/libpq-pgpass.html confirms this, it describes the exact same problem. Thank you, Martin - Forwarded message from David <[EMAIL PROTECTED]> - X-Spam-Status: No, score=0.0 required=4.0 tests=BAYES_50,UNPARSEABLE_RELAY autolearn=no version=3.1.7-deb Subject: Bug#425125: postgresql-8.1: localhost does not work for unix sockets in ~/.pgpass Reply-To: David <[EMAIL PROTECTED]>, [EMAIL PROTECTED] Date: Sat, 19 May 2007 12:10:18 +0200 From: David <[EMAIL PROTECTED]> To: Debian Bug Tracking System <[EMAIL PROTECTED]> Package: postgresql-8.1 Version: 8.1.8-1 Severity: normal According to this page: http://www.postgresql.org/docs/8.1/interactive/libpq-pgpass.html "A hostname of localhost matches both host (TCP) and local (Unix domain socket) connections coming from the local machine." However that does not work for me. I get prompted for a password. I have to put '*' in the hostname section to allow passwordless local unix socket connections. eg: Works: *:*:david:david:moo Does not work: localhost:*:david:david:moo -- System Information: Debian Release: lenny/sid APT prefers testing APT policy: (990, 'testing') Architecture: i386 (i686) Kernel: Linux 2.6.18-4-686 (SMP w/1 CPU core) Locale: LANG=en_ZA, LC_CTYPE=en_ZA (charmap=ISO-8859-1) Shell: /bin/sh linked to /bin/bash -- no debconf information - End forwarded message - -- Martin Pitthttp://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org ---(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
[BUGS] psql CLI: warn about ongoing transaction on exit
Hello PostgreSQL developers, we recently got a wishlist Debian bug [1] that asks for the psql command line tool to "warn about an ongoing transaction on exit: users=> begin; BEGIN [...] users=> \q $ ... It would be really nice if psql prompted me whether I wanted to do this. As it stands, it just rolls back the transaction." Does this stand a chance to get implemented/accepted? I'm happy to look into this and craft a patch if you would adopt it upstream. Thanks, Martin [1] http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=439943 -- Martin Pitthttp://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] psql CLI: warn about ongoing transaction on exit
Hi, Neil Conway [2007-09-17 10:16 -0700]: > At a minimum, I think we could make the fact that the transaction > has been rolled back more obvious (e.g. echo a ROLLBACK command tag > to the output or a similar warning message). Asking for confirmation > might be too chatty, and it's certainly not possible in > non-interactive mode. Unless you do isatty(STDIN_FILENO). Nothing should change for noninteractive mode. However, I agree that it might be annoying. If you just output a rollback command on exit, then it is already too late to rescue the pending transaction, so I'm not sure whether that would help this use case so much. What do you think about displaying a different prompt when a transaction is pending, like showing the number of open transactions in parentheses or so? '(2 transactions pending) mydb$' is way too long, and '(T2) mydb$' is not intuitive. Hmm.. Thanks, Martin -- Martin Pitthttp://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org ---(end of broadcast)--- TIP 1: 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] Provide a way to not ask for a password in psql
Hello,
first, congratulations for 8.3beta1. I built some initial
Debian/Ubuntu packages which went very smoothly.
I am now walking through the failures of my postgresql-common test
suite. One particular regression is that there seems to be no way any
more to inhibit the password prompt in psql. This is particularly bad
for noninteractive scripts. For example, "psql -l" is a convenient
method to check whether the postmaster is running at all,
finished with startup and ready for connections.
There is a command line switch -W which forces the password prompt,
but not an inverse switch to avoid it. So those three obvious
workarounds came to my mind:
(1) psql -l < /dev/null
Does not work because simple_prompt() reads from /dev/tty.
psql could check the already existing pset.notty and not enter the
do-while loop for asking for the password if it is True.
(2) PGPASSFILE=/dev/null psql -l
With /dev/null I get a segfault (I'll probably send a patch for
that later). With an empty dummy file it cannot find a matching
password and thus prompt me again. Admittedly this behaviour does
make sense, so it should not be altered.
(3) PGPASSWORD=foo psql -l
This trick with specifying an invalid password worked up until 8.2.
Unfortunately it stopped working now due to a slight code change:
if (PQstatus(pset.db) == CONNECTION_BAD &&
- strcmp(PQerrorMessage(pset.db), PQnoPasswordSupplied) == 0 &&
+ PQconnectionUsedPassword(pset.db) &&
To get back the earlier behaviour, this could be reverted, or the
case could me made explicit with
-password == NULL &&
+password == NULL && !getenv("PGPASSWORD") &&
My current workaround is to use the dodgy patch in (3), but I'd
prefer to use an official upstream sanctioned method.
Thank you!
Martin
--
Martin Pitthttp://www.piware.de
Ubuntu Developer http://www.ubuntu.com
Debian Developer http://www.debian.org
signature.asc
Description: Digital signature
[BUGS] libpq crash fix [was: Provide a way to not ask for a password in psql]
Hi again,
Martin Pitt [2007-10-09 15:56 +0200]:
> (2) PGPASSFILE=/dev/null psql -l
>
>With /dev/null I get a segfault (I'll probably send a patch for
>that later).
Ah, it tried to free(pgpassfile) in PasswordFromFile(), but that is a
local stack variable.
Can you please apply this upstream?
Thanks,
Martin
--
Martin Pitthttp://www.piware.de
Ubuntu Developer http://www.ubuntu.com
Debian Developer http://www.debian.org
diff -Nur postgresql-8.3/build-tree/postgresql-8.3beta1/src/interfaces/libpq/fe-connect.c postgresql-8.3.new/build-tree/postgresql-8.3beta1/src/interfaces/libpq/fe-connect.c
--- postgresql-8.3beta1/src/interfaces/libpq/fe-connect.c 2007-07-23 19:52:06.0 +0200
+++ postgresql-8.3beta1/src/interfaces/libpq/fe-connect.c 2007-10-09 16:22:41.0 +0200
@@ -3723,7 +3723,6 @@
fprintf(stderr,
libpq_gettext("WARNING: password file \"%s\" is not a plain file\n"),
pgpassfile);
- free(pgpassfile);
return NULL;
}
signature.asc
Description: Digital signature
Re: [BUGS] libpq5 8.3 breaks 8.2 compatibility with encodings
Hi, Tom Lane [2007-10-12 13:23 -0400]: > I'm becoming more and more convinced that this is initdb's bug not > libpq's. The problem stems from initdb using libpq's functions and > assuming that its numbers match up with pg_wchar.h. But note that > pg_wchar.h is not exported by libpq. Sounds convincing. The hard part is that this then also a bug in 8.2's initdb, which cannot be changed, so at least for this case we'll need a workaround. Martin -- Martin Pitthttp://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] libpq5 8.3 breaks 8.2 compatibility with encodings
Hi,
Tom Lane [2007-10-12 12:02 -0400]:
> Does anything other than initdb get weird?
It's hard to tell, my test suite concentrates on hammering initdbs
with various locales, encodings, getting a chain of 7.4->8.{0,1,2,3}
upgrades and testing the conversion of postgresql.conf arguments, etc.
I do not do that much of locale juggling (only some particular tests
to check for the infamous CVE-2006-2313/4).
I'm just afraid there might be other lurking regressions. I can do
some tests with psql and set client_encoding, etc.
> For the most part I believe it's the case that libpq's idea of the enum
> values is independent of the backend's. I think the issue here is that
> initdb is (mis) using libpq's pg_char_to_encoding, etc, and combining
> those functions with its own idea of the meanings of the enum values.
>
> Maybe we should stop exporting pg_char_to_encoding and so on from libpq,
> though I wonder if that would break any clients.
Hm, at least that sounds like a good method to find out what other
parts of the code use this array directly.
Thanks,
Martin
--
Martin Pitthttp://www.piware.de
Ubuntu Developer http://www.ubuntu.com
Debian Developer http://www.debian.org
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [BUGS] libpq5 8.3 breaks 8.2 compatibility with encodings
Hi,
Martin Pitt [2007-10-12 16:33 +0200]:
> I'm currently hunting down the last postgresql-common test case
> failure that I see with 8.3beta1. It seems the 8.3 version of libpq
> changes some internal encoding lists?
Ah, got it. The ordering in pg_enc2name_tbl[] changed, which makes the
indices jump around. This was introduced in [1], in particular in
those two bits:
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/mb/pg_wchar.h.diff?r1=1.71;r2=1.72
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/mb/encnames.c.diff?r1=1.32;r2=1.33
With attached patch (which restores the previous ordering)
compatibility with 8.2 is restored. This has two drawbacks:
* The enum cannot be nicely sorted by internal and client-only
encodings until libpq bumps soname again. This is only a cosmetical
problem, though.
* This patch needs another catalog bump (to "unbump" the
one in [1]). That's unfortunate, but the catalog number got bumped
in between beta and release in earlier versions, too, so I hope
it's not too bad.
The pg_enc2name_tbl declaration should probably have a comment saying
to never alter the order, but only append new stuff at the end. For
encodings which became obsolete (should that happen) there should be
an constant like "INVALID" or "DEPRECATED".
Thank you!
Martin
[1] http://archives.postgresql.org/pgsql-committers/2007-04/msg00198.php
--
Martin Pitthttp://www.piware.de
Ubuntu Developer http://www.ubuntu.com
Debian Developer http://www.debian.org
diff -Nur postgresql-8.3/build-tree/postgresql-8.3beta1/src/backend/utils/mb/encnames.c postgresql-8.3.new/build-tree/postgresql-8.3beta1/src/backend/utils/mb/encnames.c
--- postgresql-8.3beta1/src/backend/utils/mb/encnames.c 2007-04-16 20:50:49.0 +0200
+++ postgresql-8.3beta1/src/backend/utils/mb/encnames.c 2007-10-12 16:56:35.0 +0200
@@ -314,6 +314,9 @@
"EUC_TW", PG_EUC_TW
},
{
+ "JOHAB", PG_JOHAB
+ },
+ {
"UTF8", PG_UTF8
},
{
@@ -413,9 +416,6 @@
"UHC", PG_UHC
},
{
- "JOHAB", PG_JOHAB
- },
- {
"GB18030", PG_GB18030
},
{
diff -Nur postgresql-8.3/build-tree/postgresql-8.3beta1/src/include/catalog/catversion.h postgresql-8.3.new/build-tree/postgresql-8.3beta1/src/include/catalog/catversion.h
--- postgresql-8.3beta1/src/include/catalog/catversion.h 2007-09-30 21:54:58.0 +0200
+++ postgresql-8.3beta1/src/include/catalog/catversion.h 2007-10-12 16:56:47.0 +0200
@@ -53,6 +53,6 @@
*/
/* mmddN */
-#define CATALOG_VERSION_NO 200709301
+#define CATALOG_VERSION_NO 200710121
#endif
diff -Nur postgresql-8.3/build-tree/postgresql-8.3beta1/src/include/mb/pg_wchar.h postgresql-8.3.new/build-tree/postgresql-8.3beta1/src/include/mb/pg_wchar.h
--- postgresql-8.3beta1/src/include/mb/pg_wchar.h 2007-09-18 19:41:17.0 +0200
+++ postgresql-8.3beta1/src/include/mb/pg_wchar.h 2007-10-12 16:56:35.0 +0200
@@ -158,6 +158,7 @@
PG_EUC_CN, /* EUC for Chinese */
PG_EUC_KR, /* EUC for Korean */
PG_EUC_TW, /* EUC for Taiwan */
+ PG_JOHAB, /* EUC for Korean JOHAB; client encoding only, but must stay at this position for 8.2 compat */
PG_UTF8, /* Unicode UTF8 */
PG_MULE_INTERNAL, /* Mule internal code */
PG_LATIN1, /* ISO-8859-1 Latin 1 */
@@ -194,7 +195,6 @@
PG_BIG5, /* Big5 (Windows-950) */
PG_GBK, /* GBK (Windows-936) */
PG_UHC, /* UHC (Windows-949) */
- PG_JOHAB, /* EUC for Korean JOHAB */
PG_GB18030, /* GB18030 */
PG_SHIFT_JIS_2004, /* Shift-JIS-2004 */
_PG_LAST_ENCODING_ /* mark only */
signature.asc
Description: Digital signature
[BUGS] libpq5 8.3 breaks 8.2 compatibility with encodings
Hi PostgreSQL developers, I'm currently hunting down the last postgresql-common test case failure that I see with 8.3beta1. It seems the 8.3 version of libpq changes some internal encoding lists? If I use the 8.2 programs with the 8.2 library, all is well: $ LC_ALL=en_US.UTF-8 /usr/lib/postgresql/8.2/bin/initdb --encoding UTF8 -D /tmp/x [...] The database cluster will be initialized with locale en_US.UTF-8. [...] $ /usr/lib/postgresql/8.2/bin/postgres -D /tmp/x -k /tmp & $ /usr/lib/postgresql/8.2/bin/psql -Alth /tmp postgres|martin|UTF8 template0|martin|UTF8 template1|martin|UTF8 However, if I use 8.2 programs with the 8.3 library, things start to become weird: $ # kill postgres instance $ rm -rf /tmp/x; LC_ALL=en_US.UTF-8 /usr/lib/postgresql/8.2/bin/initdb --encoding UTF8 -D /tmp/x [...] The database cluster will be initialized with locale en_US.UTF-8. initdb: warning: encoding mismatch The encoding you selected (UTF8) and the encoding that the selected locale uses (UTF-8) are not known to match. This may lead to misbehavior in various character string processing functions. To fix this situation, rerun initdb and either do not specify an encoding explicitly, or choose a matching combination. [...] $ /usr/lib/postgresql/8.2/bin/postgres -D /tmp/x -k /tmp & $ /usr/lib/postgresql/8.2/bin/psql -Alth /tmp postgres|martin|JOHAB template0|martin|JOHAB template1|martin|JOHAB In the latter configuration, when I do not explicitly specify an encoding, the initdb output still looks weird, but at least the result seems to be correct: $ rm -rf /tmp/x; LC_ALL=en_US.UTF-8 /usr/lib/postgresql/8.2/bin/initdb -D /tmp/x [...] The database cluster will be initialized with locale en_US.UTF-8. The default database encoding has accordingly been set to MULE_INTERNAL. [...] $ /usr/lib/postgresql/8.2/bin/postgres -D /tmp/x -k /tmp & $ /usr/lib/postgresql/8.2/bin/psql -Alth /tmp postgres|martin|UTF8 template0|martin|UTF8 template1|martin|UTF8 This is a bit unfortunate, since it breaks ABI compatibility without announcing it in the SONAME. From the previous discussion it is quite clear that a soname bump is a pain, so could this be changed somehow to accomodate new encodings while remaining binary compatibility with earlier releases? Thanks, Martin -- Martin Pitthttp://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org signature.asc Description: Digital signature
Re: [BUGS] libpq5 8.3 breaks 8.2 compatibility with encodings
Hi, Tom Lane [2007-10-12 11:50 -0400]: > Martin Pitt <[EMAIL PROTECTED]> writes: > > Ah, got it. The ordering in pg_enc2name_tbl[] changed, which makes the > > indices jump around. > > Sorry, you don't get to put JOHAB back into the portion of the list that > is backend-legal encodings. Ah, the PG_ENCODING_BE_LAST magic. > It's a bit nasty that this enum is exposed as part of the ABI, but I'm > afraid we may be stuck with that decision. Well, then I see two options for 8.3: (1) Change the PG_ENCODING_IS_CLIENT_ONLY and PG_VALID_BE_ENCODING macros to expliticy disallow encodings which have become client-only while soname is not bumped. This is a bit ugly, but should work until the table gets restructured to have a per-locale flag of internal/clientonly, or the mapping stops being index-based. I'm happy to check all 9 other places where pg_enc is used for whether they need adaptions for dropped JOHAB (i. e. make assumptions about the structure without using above macros). (2) Bump the soname. That's definitively a huge PITA for distributors, but it's still better than silently breaking the ABI. So, with my distro hat on I'd definitively prefer (1), but if you want (2) for cleanliness' sake, we have to follow and bite the bullet. But we can't just let it stay like this. Thank you, and have a good weekend! Martin -- Martin Pitthttp://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org signature.asc Description: Digital signature
[BUGS] Test suite fails on alpha architecture
Hello PostgreSQL developers, The testsuite of 8.3 beta 2 fails on the Alpha architecture (versions up to 8.2 worked fine). Apparently there is some disagreement about how to report divisions by zero: float8.out: - ERROR: value out of range: overflow + ERROR: invalid argument for power function errors.out: - ERROR: division by zero + ERROR: floating-point exception + DETAIL: An invalid floating-point operation was signaled. This probably means an out-of-range result or an invalid operation, such as division by zero. and some more (case, transactions, guc, plpgsql). The full build log including diffs and initdb/postmaster logs is on http://experimental.ftbfs.de/fetch.php?&pkg=postgresql-8.3&ver=8.3%7Ebeta2-1&arch=alpha&stamp=1193991806&file=log&as=raw Thank you! Martin -- Martin Pitt http://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org signature.asc Description: Digital signature
Re: [BUGS] Test suite fails on alpha architecture
Hi, Tom Lane [2007-11-03 14:27 -0400]: > Martin Pitt <[EMAIL PROTECTED]> writes: > > The testsuite of 8.3 beta 2 fails on the Alpha architecture (versions > > up to 8.2 worked fine). > > We redid some of the float error handling for 8.3, in hopes of getting > closer to the IEEE standard behavior for NaNs and infinities and so on. > I guess that isn't working on your Alpha. I have a vague recollection > that Alphas use non-IEEE floats so maybe this is not too surprising. > > Can you grant one of us access to the machine to work on it? I don't own any alpha machine, but maybe Frank, Steven, or anyone from the Debian alpha porter list can create a temporary account for you? > Or poke into it yourself? There is no developer accessible alpha porter box for Debian unfortunately. :( Thank you, Martin -- Martin Pitt http://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] Test suite fails on alpha architecture
Hi Tom, Tom Lane [2007-11-07 13:49 -0500]: > Bottom line is that I see nothing here that the Postgres project can > fix --- these are library and compiler bugs. Thank you for your detailled analysis! I'll file bugs to the appropriate places then. Thanks, Martin -- Martin Pitt http://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org signature.asc Description: Digital signature
Re: [BUGS] Test suite fails on alpha architecture
Hi,
Tom Lane [2007-11-07 13:49 -0500]:
> All the other diffs that Martin showed are divide-by-zero failures,
> and I do not see any of them on Gentoo's machine. I think that this
> must be a compiler bug. The first example in his diffs is just
> "select 1/0", which executes this code:
>
> int32arg1 = PG_GETARG_INT32(0);
> int32arg2 = PG_GETARG_INT32(1);
> int32result;
>
> if (arg2 == 0)
> ereport(ERROR,
> (errcode(ERRCODE_DIVISION_BY_ZERO),
> errmsg("division by zero")));
>
> result = arg1 / arg2;
>
> It looks to me like Debian's compiler must be allowing the division
> instruction to be speculatively executed before the if-test branch
> is taken. Perhaps it is supposing that this is OK because control
> will return from ereport(), when in fact it will not (the routine
> throws a longjmp). Since we've not seen such behavior on any other
> platform, however, I suspect this is just a bug and not intentional.
I tried this on a Debian Alpha porter box (thanks, Steve, for pointing
me at it) with Debian's gcc 4.2.2. Latest sid indeed still has this
bug (the floor() one is confirmed fixed), not only on Alpha, but also
on sparc.
Since the simple test case did not reproduce the error, I tried to
make a more sophisticated one which resembles more closely what
PostgreSQL does (sigsetjmp/siglongjmp instead of exit(), some macros,
etc.). Unfortunately in vain, since the test case still works
perfectly with both no compiler options and also the ones used for
PostgreSQL. I attach it here nevertheless just in case someone has
more luck than me.
So I tried to approach it from the other side: Building postgresql
with CFLAGS="-O0 -g" or "-O1 -g" works correctly, but with "-O2 -g" I
get above bug.
So I guess I'll build with -O1 for the time being on sparc and alpha
to get correct binaries until this is sorted out. Any idea what else I
could try?
Thanks,
Martin
--
Martin Pitthttp://www.piware.de
Ubuntu Developer http://www.ubuntu.com
Debian Developer http://www.debian.org
#include
#include
#include
#define ERROR 20
#define ereport(elevel, rest) \
(errstart(elevel, __FILE__, __LINE__, __func__) ? \
(errfinish rest) : (void) 0)
#define PG_RE_THROW() \
siglongjmp(PG_exception_stack, 1)
sigjmp_buf PG_exception_stack;
int errstart(int elevel, const char *filename, int lineno,
const char *funcname)
{
printf("error: level %i %s:%i function %s\n", elevel, filename, lineno, funcname);
return 1;
}
void errfinish(int dummy, const char* msg)
{
puts(msg);
PG_RE_THROW();
}
int
do_div(char** argv)
{
int arg1 = atoi(argv[1]);
int arg2 = atoi(argv[2]);
int result;
if (arg2 == 0)
ereport(ERROR, (1, "division by zero"));
result = arg1 / arg2;
return result;
}
int
main(int argc, char **argv)
{
if (sigsetjmp(PG_exception_stack, 0) == 0) {
int result = do_div(argv);
printf("%d\n", result);
} else {
printf("caught error, aborting\n");
return 1;
}
return 0;
}
signature.asc
Description: Digital signature
Re: [BUGS] Test suite fails on alpha architecture
Martin Pitt [2007-12-04 23:43 +0100]: > So I tried to approach it from the other side: Building postgresql > with CFLAGS="-O0 -g" or "-O1 -g" works correctly, but with "-O2 -g" I > get above bug. Just FAOD, building with gcc 4.1 and -O2 works fine. I guess this sufficiently proves that this is a gcc 4.2 bug. Martin -- Martin Pitthttp://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org signature.asc Description: Digital signature
Re: [BUGS] BUG #3809: SSL "unsafe" private key permissions bug
Hi,
Simon Arlott [2007-12-08 12:24 +]:
> Bug reference: 3809
> Logged by: Simon Arlott
> Email address: [EMAIL PROTECTED]
> PostgreSQL version: 8.2.4
> Operating system: Linux 2.6.23
> Description:SSL "unsafe" private key permissions bug
> Details:
>
> FATAL: unsafe permissions on private key file "server.key"
> DETAIL: File must be owned by the database user and must have no
> permissions for "group" or "other".
>
> It should be possible to disable this check in the configuration, so those
> of us capable of deciding what's unsafe can do so.
For the same reason Debian/Ubuntu have modified this check ages ago,
to also allow for keys which are owned by root and readable by a
particular group. A lot of our users want to share a common SSL
cert/key between all servers, and the upstream check makes this
impossible. (Ubuntu sets up all server packages in a way that they all
share a common SSL key called "snakeoil" which is generated on system
installation. By merely replacing this with a real one, your box
becomes sanely configured without fiddling with any configuration
files.)
I already proposed this patch two times, but it has been rejected so
far unfortunately. But maybe it's useful for you.
Martin
--
Martin Pitthttp://www.piware.de
Ubuntu Developer http://www.ubuntu.com
Debian Developer http://www.debian.org
diff -Nur postgresql-8.2/build-tree/postgresql-8.2beta1/src/backend/libpq/be-secure.c postgresql-8.2.new/build-tree/postgresql-8.2beta1/src/backend/libpq/be-secure.c
--- postgresql-8.2beta1/src/backend/libpq/be-secure.c 2006-09-04 16:57:27.0 +0200
+++ postgresql-8.2beta1/src/backend/libpq/be-secure.c 2006-09-25 19:24:13.0 +0200
@@ -751,13 +751,15 @@
* directory permission check in postmaster.c)
*/
#if !defined(WIN32) && !defined(__CYGWIN__)
- if (!S_ISREG(buf.st_mode) || (buf.st_mode & (S_IRWXG | S_IRWXO)) ||
- buf.st_uid != geteuid())
+ if (!S_ISREG(buf.st_mode) || (buf.st_mode & (S_IWGRP | S_IRWXO)) ||
+ (buf.st_uid != geteuid()) && buf.st_uid != 0)
ereport(FATAL,
(errcode(ERRCODE_CONFIG_FILE_ERROR),
errmsg("unsafe permissions on private key file \"%s\"",
SERVER_PRIVATE_KEY_FILE),
- errdetail("File must be owned by the database user and must have no permissions for \"group\" or \"other\".")));
+ errdetail("File must be owned by the \
+database user or root, must have no write permission for \"group\", and must \
+have no permissions for \"other\".")));
#endif
if (!SSL_CTX_use_PrivateKey_file(SSL_context,
signature.asc
Description: Digital signature
[BUGS] pg_dump produces invalid SQL for "group by cast(null as numeric)"
Hi PostgreSQL developers, in [1], a user reported a failure of pg_dump: snip -- 1. Create an empty database. 2. Connect to the database and create these views: create view foo as select 3; create view bar as select count(*) from foo group by cast(null as numeric); 3. pg_dump the database to a text file. The file contains 'CREATE VIEW bar AS SELECT count(*) AS count FROM foo GROUP BY 2;' 4. Drop view bar from the database. 5. Run the CREATE VIEW bar..; statement from the text file. 6. The statement fails with 'ERROR: GROUP BY position 2 is not in select list' snip -- I verified that this is still an issue on 8.3 CVS head. However, I admit that I'm not sure why "group by cast(null as numeric)" is useful. However, it actually works in the DB and fails in pg_dump, so either it is valid and should be handled by pg_dump, or it is invalid and should not be accepted in the first place. Thank you! Martin [1] https://bugs.launchpad.net/bugs/177382 -- Martin Pitthttp://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org signature.asc Description: Digital signature
[BUGS] BUG #3907: Deadlock while "vacuum verbose analyze" runs...
The following bug has been logged online: Bug reference: 3907 Logged by: Martin Fandel Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.5 Operating system: Suse Linux Enterprise 10 Description:Deadlock while "vacuum verbose analyze" runs... Details: Hi, on this weekend my database runs into a deadlock every time I run "vacuum verbose analyze". The logfile show this: ERROR: deadlock detected DETAIL: Process 7951 waits for ShareLock on transaction 193734734; blocked by process 8576. Process 8576 waits for ShareLock on transaction 193734768; blocked by process 7951. I don't know why it's locked. Hopefully someone can help me to fix the problem? If they are any informations needed, please let me know. I've seen many ungranted deadlocks. I've used this query for view it: SELECT dbu.usename as locker, l.mode as locktype, pg_stat_get_backend_pid(S.backendid) as pid, db.datname||'.'||n.nspname||'.'||r.relname as relation, l.mode, substring(pg_stat_get_backend_activity(S.backendid ), 0, 30) as query FROM pg_user dbu, (SELECT pg_stat_get_backend_idset() AS backendid) AS S, pg_database db, pg_locks l, pg_class r, pg_namespace n WHERE db.oid = pg_stat_get_backend_dbid(S.backendid) AND dbu.usesysid = pg_stat_get_backend_userid(S.backendid) AND l.pid = pg_stat_get_backend_pid(S.backendid) AND l.relation = r.oid AND l.database = db.oid AND l.database = db.oid AND l.granted ORDER BY db.datname, n.nspname, r.relname, l.mode; Thanks! Best regards, Martin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #4040: psql should provide option to not prompt for password
Mika Fischer [2008-03-17 10:19 +]:
> Description:psql should provide option to not prompt for password
> Details:
>
> Hi,
>
> I'm currently working on the bash-completion package. The problem with
> postgresql is that psql cannot safely be called because there is no way to
> know whether it will prompt for a password and there is also no way to avoid
> the prompt.
>
> Needless to say a password prompt is very bad in the context of
> tab-completion.
>
> Ideally, psql should provide an option --no-password which would cause it to
> never promt for a password, and in case one is needed, fail as if a wrong
> one was given.
>
> However if you can think of an easier way to accomplish this, I'd be
> grateful for pointers.
Indeed I have a similar problem. I use psql to probe for actual
availability of cluster startup in the integration scripts (pg_ctl
does not provide that) and also stumbled over this.
Earlier versions did not prompt if PGPASSWD was supplied, 8.3 changed
this behaviour. That's why I applied the attached patch to the
Debian/Ubuntu packages to restore the older behaviour, which works
pretty well.
I already proposed that some months ago [1], but didn't get very far.
Martin
[1] http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg18440.html
--
Martin Pitt| http://www.piware.de
Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org)
# Description: Change psql to not prompt for a password if PGPASSWORD is given, just as in earlier versions. Without that, there is no way to suppress the password input prompt, which is bad for scripts.
--- postgresql-8.3RC1/src/bin/psql/startup.c 2008-01-04 14:48:17.0 +0100
+++ postgresql-8.3RC1/src/bin/psql/startup.c 2008-01-04 14:49:24.0 +0100
@@ -199,7 +199,7 @@
if (PQstatus(pset.db) == CONNECTION_BAD &&
PQconnectionNeedsPassword(pset.db) &&
- password == NULL &&
+ password == NULL && !getenv("PGPASSWORD") &&
!feof(stdin))
{
PQfinish(pset.db);
signature.asc
Description: Digital signature
Re: [BUGS] BUG #4040: psql should provide option to not prompt for password
Tom Lane [2008-03-17 10:48 -0400]:
> Martin Pitt <[EMAIL PROTECTED]> writes:
> > if (PQstatus(pset.db) == CONNECTION_BAD &&
> > PQconnectionNeedsPassword(pset.db) &&
> > - password == NULL &&
> > + password == NULL && !getenv("PGPASSWORD") &&
> > !feof(stdin))
> > {
> > PQfinish(pset.db);
>
> What exactly do you think that accomplishes? AFAICS
> PQconnectionNeedsPassword can't possibly return true if there was a
> password available from PGPASSWORD (regardless of whether it was
> correct or not).
I don't claim to understand the complete code behind
PQconnectionNeedsPassword(). I just found that in at least 8.3RC1,
this did return True if pg_hba.conf set password authentication and
none was provided. I tried every trick that came into my mind,
redirecting stdin, using PGPASSWORD, and I think even a fake empty
.pgpass file, nothing worked.
Martin
--
Martin Pitt| http://www.piware.de
Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4054: Text containing ';' upsets db restores
The following bug has been logged online: Bug reference: 4054 Logged by: Martin Gregorie Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.6 Operating system: Linux (Redhat Fedora 8 Description:Text containing ';' upsets db restores Details: I've been dumping my database using pg_dumpall in its default (fast) version. This weekend, after upgrading from Fedora 7 to Fedora 8 I had to restore my database. Finger trouble & tiredness caused the restore. The database survived the upgrade but not me. I found that replaying the output from pg_dumpall into psql caused problems: 1) A VIEW in the database was not dropped. The DROP for it was not in the pg_dumpall file). Minor point, mentioned only for completeness. A manual DROP fixed that. 2) Every table has an int as the first field. This number value is flagged as an error for the first row in every table. 3) My tables have BYTEA and TEXT fields in them. In some rows these field contain HTML text. This caused syntax errors during the reload. Specifically, "; " and "http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Recommended approach for upgrading DBs with nonmatching encodings
Hello all, as already known, 8.3 now enforces a match between DB encoding and server locale [1]. I agree that this is a Good Thing™, but it causes automatic upgrades from previous versions to 8.3 to fail with something like pg_restore: [archiver (db)] could not execute query: ERROR: encoding LATIN1 does not match server's locale it_IT.UTF-8 DETAIL: The server's LC_CTYPE setting requires encoding UTF8. Command was: CREATE DATABASE lixpergroupware WITH TEMPLATE = template0 ENCODING = 'LATIN1'; There were some proposals [3][4] to have the server run under locale C or POSIX, but IMHO this is rather an aggravation than a viable solution (locales are a good thing), so I rather don't do that. My gut feeling is that the right approach would be to create all target (8.3) databases with a correct encoding that matches the server locale and have the character data from the pg_dump converted on the fly (with iconv, or with pg_dump itself), so that people get from a broken 8.1/8.2 setup to a good 8.3 setup painlessly. I'm seeking some input on (1) whether above approach is the correct one, and (2) suggestions how to implement it properly. My current pg_upgradecluster uses pg_dumpall to copy the schema, and a per-db pg_dump to copy the DB table contents. Will calling pg_dump with --encoding= always DTRT and is it meant to solve this problem? The common case is --encoding=UTF-8, but of course in theory someone might also have it the other way around, so the upgrade should fail if it encounters an UTF-8 character which cannot be encoded into an. e. g. LATIN1 character. Thank you all for any suggestion, Martin [1] http://www.nabble.com/BUG--3924:-Create-Database-with-another-encoding-as-the-encoding-from-postgres-td15246051.html [2] http://bugs.debian.org/472930 [3] http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=472930#10 [4] https://bugs.launchpad.net/207779 -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) signature.asc Description: Digital signature
Re: [BUGS] Recommended approach for upgrading DBs with nonmatching encodings
Hi again, Martin Pitt [2008-03-30 20:40 +0200]: > My current pg_upgradecluster uses pg_dumpall to copy the schema, and a > per-db pg_dump to copy the DB table contents. Will calling pg_dump > with --encoding= always DTRT and is > it meant to solve this problem? One problem with this is that while pg_dump -E UTF8 works (with SQL output), -E does not seem to have any effect when using -Fc. However, -Fc is a must for a reasonably robust automatic upgrade script. Thanks in advance, Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) signature.asc Description: Digital signature
Re: [BUGS] Recommended approach for upgrading DBs with nonmatching encodings
Tom Lane [2008-03-30 16:43 -0400]: > Martin Pitt <[EMAIL PROTECTED]> writes: > > One problem with this is that while pg_dump -E UTF8 works (with SQL > > output), -E does not seem to have any effect when using -Fc. > > Huh? Please provide a test case. Ah, I got it. This fails: pg_dump -Fc -E UTF8 -p 5432 latin1test | pg_restore -p 5433 -d template1 -C (5432 is 8.1, 5433 is 8.3, both with locale ru_RU.UTF-8; createdb -E latin1 latin1test) But if I create the DB beforehand (with correct encoding) and then dump/restore without using -C, it works fine: createdb -p 5433 latin1test pg_dump -Fc -p 5432 latin1test | pg_restore -p 5433 -d latin1test In that case I do not even need to specify -E. Seems that pg_dump/pg_restore are clever enough to detect encodings and necessary conversions. So this seems to be the cleanest approach to me, and it's free of hacks. pg_restore restores the correct owner of the DB, so calling createdb as the DB superuser does not harm. Thanks, Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Recommended approach for upgrading DBs with nonmatching encodings
Hi Tom, Tom Lane [2008-03-30 17:15 -0400]: > > pg_dump -Fc -E UTF8 -p 5432 latin1test | pg_restore -p 5433 -d template1 > > -C > > Yeah. This will try to create the new latin1test with all the same > properties it had before, including encoding I see, so it's intended to behave like this. man pg_dump is a little unclear on this. >, and 8.3 intentionally rejects that. Rightly so. > Your 8.1 setup is pretty broken too (it will misbehave in various > ways because of the encoding mismatch), but 8.1 fails to realize > that. Right, I know. It is the reproducer for the upgrade problems many people have (see quoted bug reports in initial mail), and what I now use in the postgresql-common test suite. > Yeah, there's usually little value in -E unless you're planning to > do something else with the dump than just feed it to pg_restore. > (If you wanted to export to some other DBMS, for example, it could > be useful.) In particular -E has entirely zip bearing on what > database encoding will be assigned during restore. Thanks for the clarification. So it seems createdb+pg_restore is the way to go, and pg_restore -C does not DTRT for my purpose (correcting DB encodings on upgrades). BTW, many people seem to run the server under C and use different encodings in their DBs (latin, UTF8). Shouldn't that cause similar problems with collation, data type checking (ischar(), etc.)? What do you recommend should the upgrade script do if it encounters an 8.[12] server running under C? Thanks, Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] reproducible database crash with simple sql command
Hi Christian, redirecting to the -bugs mailing list. This is totally inappropriate for -announce. [EMAIL PROTECTED] [2008-05-08 11:22 +0200]: > first i create a table > create table regions (id integer, name varchar); > > then i want to set a default value for a column, e.g. > alter table regions alter column name set default 'bavaria'; > > at this point crashes the database with the message > > PANIK: ERROR_STACK_SIZE exceeded > [...] > p.s. os windows xp, sp2 > postgres 8.3.1, visual c++, build 1400 Hm, works fine with PostgreSQL 8.3.1 on Ubuntu 8.04, also under a German installation/locale. After the ALTER I get martin=> \d regions Tabelle »public.regions« Spalte |Typ| Attribute +---+-- id | integer | name | character varying | default 'bavaria'::character varying which looks alright to me. Might be a windows specific bug? Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4187: function to_tsvector not immutable
The following bug has been logged online: Bug reference: 4187 Logged by: Martin Sullivan Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3 Operating system: Fedora Core 9 Description:function to_tsvector not immutable Details: Postgres 8.3 now supports tsearch2 as standard and it's built into the distribution. So, to_tsvector, tsvector, the '@@' operator &al are readily available. The only wrinkle being that to_tsvector needs to be ALTERed to IMMUTABLE using the following (as user postgres): alter function to_tsvector(regconfig,text) immutable alter function to_tsvector(text) immutable This is needed to allow CREATE INDEX to work with these functions and the access methods GIN and GIST, else you get an error message about indexes requiring immutable functions. It should be added that the to_tsvector are declared immutable in the 8.2 contrib distribution. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
