[BUGS] BUG #4672: undefined symbol: check_encoding_conversion_args
The following bug has been logged online: Bug reference: 4672 Logged by: Boris Email address: piol...@lpthe.jussieu.fr PostgreSQL version: 8.3.6 Operating system: Linux Description:undefined symbol: check_encoding_conversion_args Details: I recently upgraded to 8.3.6, as a result of which encoding conversion does not seem to work anymore: $ export PGCLIENTENCODING=ISO-8859-1 $ psql psql: FATAL: could not load library "/usr/lib/pgsql/utf8_and_iso8859_1.so": /usr/lib/pgsql/utf8_and_iso8859_1.so: undefined symbol: check_encoding_conversion_args My database is in UTF8, if I run the same with export PGCLIENTENCODING=UTF8 everything works fine. The same problem occurs in psql if I use \encoding ISO-8859-1 ISO-8859-1: invalid encoding name or conversion procedure not found Thank you for your help BP -- 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 #2472: Incorrect ILIKE, ~* for Cyrilic symbols.
The following bug has been logged online:
Bug reference: 2472
Logged by: Boris
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.4
Operating system: win2000
Description:Incorrect ILIKE, ~* for Cyrilic symbols.
Details:
Microsoft Windows 2000 [Version 5.00.2195]
PosgreSql 8.1.4
PgAdmin 1.4.2 III
for Cyrilis symbols ~* and ILIKE return wrong result
data base is created by:
initdb.exe -E UTF8 --lc-collate=UTF8 --lc-ctype=UTF8 "c:\Program
Files\PostgreSQL\8.1\basewin1251\"
then...
postmaster.exe -D "c:\Program Files\PostgreSQL\8.1\basewin1251\"
then
connect to server on local w PgAdmin 1.4.2 III
I see db:
CREATE DATABASE postgres
WITH OWNER = postgres
ENCODING = 'UTF8'
TABLESPACE = pg_default;
Go to SQL Query
SELECT upper('ÐÑоÐа ÐÑÐÑÐÑ'), lower('ÐÑоÐа ÐÑÐÑÐÑ'),
'Ð'ILIKE'Ñ'
return incorrect FALSE for last ILIKE
It must be TRUE
ICQ 176869864 for more info
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #2552: wrong sql dump?
The following bug has been logged online: Bug reference: 2552 Logged by: Boris Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.4 Operating system: win 2000 Description:wrong sql dump? Details: Hi! data base lives from pg7.2 before upgrade to 8.1.2 I used pgdump from 7.2 then I go to pg 8.1.4 and pgAdmin III 1.4.3 (Jul 19 2006) The sequence for primry key (PK) of some (not all) was missed and PK become wrong - allways 0. I export all base as sql script (1). When I import this script I see no sequences for sope PK. I see no commands to create sequences in this (1)sql script. ForEx: table "auth"."access_policy_list" has a PK but no sequence is created. See sql dump here http://www.bdr.kiev.ua/tmpboris/bdr_v0_plain.zip ICQ 176869864 for any more info, please. :) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #2557: field`s default value not restored in big sql script
The following bug has been logged online:
Bug reference: 2557
Logged by: Boris
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.4
Operating system: win 2000, freebsd
Description:field`s default value not restored in big sql script
Details:
Hi!
This sql script was created by pd_dump.
(http://www.lanukr.com/upload/errordump.zip)
No error when i run this script to restore DB.
But table "auth"."access_flag" has wrong def value for "access_flag_id".
I think ii must be
nextval('auth.access_flag_access_flag_id_seq'::regclass).
>From script it becomes nextval('access_flag_access_flag_id_seq'::regclass)
When I run a shot script to create this table - no problem. deff value is
correct for field "access_flag_id"
CREATE TABLE "auth"."access_flag_2" (
"access_flag_id" SERIAL,
"access_flag_name" TEXT NOT NULL,
"comments" TEXT,
"long_flag_name" TEXT,
CONSTRAINT "access_flag_2_access_flag_name_key"
UNIQUE("access_flag_name"),
CONSTRAINT "access_flag_2_pkey" PRIMARY KEY("access_flag_id")
)
HOW TO:
1. Create db test
2. restore db from script by
psql -d test -U your_user_name -f dump.sql >123.log 2<&1
3. check if table "auth"."access_flag" has a field "access_flag_id" as PK
and has deff value nextval('access_flag_access_flag_id_seq'::regclass) (
error)
I can reproduse this error on "PostgreSQL 8.1.4 on i386-portbld-freebsd4.9,
compiled by GCC 2.95.4" too.
any info you need upon req.
ICQ 176869864
Boris
---(end of broadcast)---
TIP 6: explain analyze is your friend
[BUGS] BUG #3610: Vaccum stop and can not continue
The following bug has been logged online: Bug reference: 3610 Logged by: Boris Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1 Operating system: Windows Server 2003 Description:Vaccum stop and can not continue Details: Hi, I have a problem with full vaccum maintenance - vaccum stop and can not continue - i wait four-five hours and finally I choose cancel to exit... Please help! Tank you very much. Boris ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[BUGS] BUG #5697: Infinite loop inside PQexecStart function
The following bug has been logged online:
Bug reference: 5697
Logged by: Boris
Email address: ad...@nyc.yamaha.com
PostgreSQL version: 8.3.5
Operating system: Linux RH ES5
Description:Infinite loop inside PQexecStart function
Details:
The infinite loop in this case occurs inside the PQexecStart() function in
pgsql driver. The following insert corresponds to the actual infinite loop.
The are several conditions that are checked "(result = PQgetResult(conn)) !=
NULL" and "result->resultStatus == PGRES_COPY_IN || result->resultStatus ==
PGRES_COPY_OUT || conn->status == CONNECTION_BAD" as an exit point.
-
ASM code
0x0042bca5 in PQexecStart () from /usr/local/pgsql/lib/libpq.so.5
17 0x0042bca5 : cmp $0x3,%esi
18 0x0042bca8 : je 0x42bd00
19 0x0042bcaa : cmpl $0x1,0x44(%edi)
20 0x0042bcae : je 0x42bcf0
21 0x0042bcb0 : mov %edi,(%esp)
22 0x0042bcb3 : call 0x424fa0
23 0x0042bcb8 : test %eax,%eax
24 0x0042bcba : je 0x42bd13
25 0x0042bcbc : mov 0x1c(%eax),%esi
26 0x0042bcbf : mov %eax,(%esp)
27 0x0042bcc2 : call 0x4255d0
28 0x0042bcc7 : cmp $0x4,%esi
29 0x0042bcca : jne 0x42bca5
-
The C-code corresponding to this part (short version):
while ((result = PQgetResult(conn)) != NULL){
ExecStatusType resultStatus = result->resultStatus;
PQclear(result); /* only need its status */
/* check for loss of connection, too */
if (result->resultStatus == PGRES_COPY_IN ||
result->resultStatus == PGRES_COPY_OUT ||
conn->status == CONNECTION_BAD)
break;
}
return true;
-
These are the values mapped to the corresponding constants:
PGRES_EMPTY_QUERY = 0
PGRES_COMMAND_OK = 1
PGRES_TUPLES_OK = 2
PGRES_COPY_OUT = 3
PGRES_COPY_IN = 4
PGRES_BAD_RESPONSE = 5
PGRES_NONFATAL_ERROR = 6
PGRES_FATAL_ERROR = 7
Condition exit point is evaluated against 3 constants PGRES_COPY_IN,
PGRES_COPY_OUT, CONNECTION_BAD. Since the connection to the database is in a
"GOOD" state the only constants that are evaluated are PGRES_COPY_IN and
PGRES_COPY_OUT, but according to the debugger trace the value those are
compared against is 7, e.g. PGRES_FATAL_ERROR which has no condition and
thus the process runs forever. Please see the following insert with detailed
output of the registers.
-
0x0042bcc7 in PQexecStart () from /usr/local/pgsql/lib/libpq.so.5
1: x/i $pc 0x42bcc7 : cmp $0x4,%esi
(gdb) i r
eax 0x99153
ecx 0x1 1
edx 0x98152
ebx 0x43a3304432688
esp 0xbfe506d0 0xbfe506d0
ebp 0xbfe506e8 0xbfe506e8
esi 0x7 7
edi 0x98c5bb4 160193460
eip 0x42bcc70x42bcc7
eflags 0x286[ PF SF IF ]
cs 0x73 115
ss 0x7b 123
ds 0x7b 123
es 0x7b 123
fs 0x0 0
gs 0x33 51
--
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 #6774: FOR IN SELECT LOOP ignores ORDER BY
The following bug has been logged on the website: Bug reference: 6774 Logged by: Boris Folgmann Email address: bo...@folgmann.de PostgreSQL version: 8.4.12 Operating system: CentOS 6.3 Description: This is an really interesting one! I've trimmed down the problem so you can simply reproduce it by copy & paste: CREATE OR REPLACE FUNCTION ignores_order_by() RETURNS TABLE(datname VARCHAR) AS $$ DECLARE r RECORD; BEGIN FOR r IN SELECT * FROM pg_database WHERE datallowconn ORDER BY datname LOOP datname := r.datname; RETURN NEXT; END LOOP; END; $$ LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION respects_order_by() RETURNS TABLE(dn VARCHAR) AS $$ DECLARE r RECORD; BEGIN FOR r IN SELECT * FROM pg_database WHERE datallowconn ORDER BY datname LOOP dn := r.datname; RETURN NEXT; END LOOP; END; $$ LANGUAGE 'plpgsql'; SELECT * from ignores_order_by(); SELECT * from respects_order_by(); Now compare the different output! The only difference of the two functions is that the first one uses a variable with the same name of a column. This might be a feature and not a bug, but browsing through the documentation I could not find any documented restrictions on variable names in this context. -- 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 #4672: undefined symbol: check_encoding_conversion_args
Thanks a lot for the quick reply: Boris wrote: The following bug has been logged online: Bug reference: 4672 Logged by: Boris Email address: piol...@lpthe.jussieu.fr PostgreSQL version: 8.3.6 Operating system: Linux Description:undefined symbol: check_encoding_conversion_args Details: I recently upgraded to 8.3.6, as a result of which encoding conversion does not seem to work anymore: $ export PGCLIENTENCODING=ISO-8859-1 $ psql psql: FATAL: could not load library "/usr/lib/pgsql/utf8_and_iso8859_1.so": /usr/lib/pgsql/utf8_and_iso8859_1.so: undefined symbol: check_encoding_conversion_args My database is in UTF8, if I run the same with export PGCLIENTENCODING=UTF8 everything works fine. check_encoding_conversion_args is a new internal function in 8.3.6. I suspect that there's a mismatch in the version of PostgreSQL and the associated encoding conversion libraries. Since you upgraded to 8.3.6, have you restarted PostgreSQL? What does "SELECT version()" say? You're right, that fixed the pb. However I still get a conversion error from UTF8 into ISO-8859-1:: "Couldn't execute statement: ERROR: character 0xe28099 of encoding "UTF8" has no equivalent in "LATIN1" SELECT version returns PostgreSQL 8.3.6 on i386-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.3.0 20080428 (Red Hat 4.3.0-8) Thanks for any help. Regards, Boris -- 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 #5151: autovacuum process segfaults when max_fsm_pages are too low
The following bug has been logged online: Bug reference: 5151 Logged by: Boris Folgmann Email address: bo...@folgmann.de PostgreSQL version: 8.1.15 Operating system: CentOS release 4.8 (Final) Description:autovacuum process segfaults when max_fsm_pages are too low Details: Hi (SELECT * FROM pgsql_developers), first of all I want to tell you, that I'm successfully using pgsql since 2002, v7.x on RedHat Linux for a high-speed mobile web application. I hardly can remember problems caused by bugs in pgsql. I love it, it rocks. Thanks for all! Currently I'm using postgresql-8.1.15-1.el4s1.1 from the centosplus repository for CentOS 4.8. All updates applied, kernel is 2.6.9-89.0.11.ELsmp. I know that 8.1.18 is out, but haven't found something in the release-notes about problems with max_fsm_pages. Anyway, there isn't any more recent RPM available at the moment. 8.1.15 is in use since a month, server uptime was 7 days when yesterday pgsql suffered from severe problems. Relevant parts of postgresql.conf -- max_fsm_pages = 6 # 3x default max_fsm_relations = 2000 # 2x default vacuum_cost_delay = 125 autovacuum = on -- Then suddenly during normal operation (sorry, German log): -- 2009-10-29 16:30:51 UTC @ [22311] LOG: Autovacuum: bearbeite Datenbank »postgres« 2009-10-29 16:31:19 UTC @ [22311] HINWEIS: Anzahl der benötigten Page-Slots (107264) überschreitet max_fsm_pages (6) 2009-10-29 16:31:19 UTC @ [22311] TIPP: Erhöhen Sie eventuell den Konfigurationsparameter »max_fsm_pages« auf über 107264. 2009-10-29 16:31:19 UTC @ [22311] LOG: Anzahl der benötigten Page-Slots (107264) überschreitet max_fsm_pages (6) 2009-10-29 16:31:19 UTC @ [22311] TIPP: Erhöhen Sie eventuell den Konfigurationsparameter »max_fsm_pages« auf über 107264. 2009-10-29 16:31:19 UTC @ [22311] LOG: Grenze für Transaktionsnummernüberlauf ist 2146954493, begrenzt durch Datenbank »boris« 2009-10-29 16:31:19 UTC @ [3538] LOG: Autovacuum-Prozess (PID 22311) wurde von Signal 11 beendet 2009-10-29 16:31:19 UTC @ [3538] LOG: aktive Serverprozesse werden abgebrochen 2009-10-29 16:31:19 UTC modw...@modwars-vf-de [32243] WARNUNG: breche Verbindung ab wegen Absturz eines anderen Serverprozesses 2009-10-29 16:31:19 UTC modw...@modwars-vf-de [32243] DETAIL: Der Postmaster hat diesen Serverprozess angewiesen, die aktuelle Transaktion zurückzurollen und die Sitzung zu beenden, weil ein anderer Serverprozess abnormal beendet wurde und möglicherweise das Shared Memory verfälscht hat. 2009-10-29 16:31:19 UTC modw...@modwars-vf-de [32243] TIPP: In einem Moment sollten Sie wieder mit der Datenbank verbinden und Ihren Befehl wiederholen können. 2009-10-29 16:31:19 UTC modw...@modwars-vf-de-2 [29075] WARNUNG: breche Verbindung ab wegen Absturz eines anderen Serverprozesses 2009-10-29 16:31:19 UTC modw...@modwars-vf-de-2 [29075] DETAIL: Der Postmaster hat diesen Serverprozess angewiesen, die aktuelle Transaktion zurückzurollen und die Sitzung zu beenden, weil ein anderer Serverprozess abnormal beendet wurde und möglicherweise das Shared Memory verfälscht hat. 2009-10-29 16:31:19 UTC modw...@modwars-vf-de-2 [29075] TIPP: In einem Moment sollten Sie wieder mit der Datenbank verbinden und Ihren Befehl wiederholen können. [...] 2009-10-29 16:31:19 UTC @ [3538] LOG: alle Serverprozesse beendet; initialisiere neu 2009-10-29 16:31:19 UTC @ [22313] LOG: Datenbanksystem wurde am 2009-10-29 16:30:48 UTC unterbrochen 2009-10-29 16:31:19 UTC @ [22313] LOG: Checkpoint-Eintrag ist bei 671/7140CC68 2009-10-29 16:31:19 UTC @ [22313] LOG: Redo-Eintrag ist bei 671/7140CB88; Undo-Eintrag ist bei 0/0; Shutdown FALSE 2009-10-29 16:31:19 UTC @ [22313] LOG: nächste Transaktions-ID: 1609954434; nächste OID: 3582936 2009-10-29 16:31:19 UTC @ [22313] LOG: nächste MultiXactId: 1583322; nächster MultiXactOffset: 3674272 2009-10-29 16:31:19 UTC @ [22313] LOG: Datenbanksystem wurde nicht richtig heruntergefahren; automatische Wiederherstellung läuft 2009-10-29 16:31:19 UTC @ [22313] LOG: Redo beginnt bei 671/7140CB88 2009-10-29 16:31:20 UTC @ [22313] LOG: Datensatz mit Länge null bei 671/71A1B48C 2009-10-29 16:31:20 UTC @ [22313] LOG: Redo fertig bei 671/71A1B464 2009-10-29 16:31:20 UTC @ [22313] LOG: Datenbanksystem ist bereit 2009-10-29 16:31:20 UTC @ [22313] LOG: Grenze für Transaktionsnummernüberlauf ist 2146921242, begrenzt durch Datenbank »postgres« 2009-10-29 16:31:25 UTC [unbekan...@[unbekannt] [22337] LOG: Verbindung empfangen: Host=127.0.0.1 port=33743 2009-10-29 16:31:25 UTC modw...@modwars-vf-de [22337] LOG: Verbindung authorisiert: Benutzer=modwars Datenbank=modwars-vf-de 2009-10-29 16:31:25 UTC [unbekan...@[unbekannt] [22340] LOG: Verbindung empfangen: Host=127.0.0.1 port=33752 2009-10-29 16:31:25 UTC modw...@modwars-vf-de [22340] LOG: Verbindung authorisie
Re: [BUGS] BUG #5151: autovacuum process segfaults when max_fsm_pages are too low
Hi Tom, Tom Lane schrieb/wrote: > > Red Hat stopped supporting the RHEL-4 App Stack product awhile ago; > > since CentOS is evidently copying that, you should not hold your > > breath waiting for an update :-(. However, as far as I can see It seems that you are right, and I don't understand how they can stop releasing important updates as RHEL4 is still supported. > > Or use Devrim's RPMs from postgresql.org (I think he has a build > > of 8.1.x for RHEL-4, though I could be wrong). I've done that on 10 Nov 2009 and they're working perfectly. No autovac crashes since then. RedHat is very conservative concerning pgsql as my small table shows: RHEL/CentOS 4.x 5.x pgsql default 7.4 8.1 pgsql RHWAS 8.1 8.2 So I'm feeling very happy at the moment using your yum repositories and are thinking about using an even more recent postgresql version in the future. Is 8.4.1 ready for production or is it advisable to wait for one or two updates? tnx, boris -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Silent deadlock
Hi!
We run a web application based on a complex database using
postgresql-7.2.3-5.80 on Red Hat 8.0.
Generally using pgsql with JDBC is very nice, but from time to time we run
into problems that are caused by something like a silent deadlock, which
means that it isn't reported in the logfile and doesn't rollback one of the
transactions.
Hanging database connections cause the webapp to not return web pages to
the client, so that the connection pool gets filled up soon, as users try
and try to reload the page. This can freeze the entire application very fast.
Most actions are implemented in PL/PGSQL functions to use the transaction
features of pgsql, so we simply call things like
SELECT create_user('test');
in the application. As it seems to us that it's not very portable to parse
pgsql exceptions, e.g. if you try to insert a duplicate value in a unique
coloumn, we check things like this inside the function before doing the
insert. A table lock is necessary to be sure, that the result of the select
is still valid when the new value is inserted. Have a look at this functions:
CREATE OR REPLACE FUNCTION create_user(
users.username%TYPE,
users.password%TYPE,
users.email%TYPE
)
RETURNS BOOL AS '
DECLARE
old_userINTEGER;
new_userINTEGER;
BEGIN
LOCK users IN SHARE MODE;
SELECT INTO old_user uid FROM users WHERE username=$1;
IF NOT FOUND THEN
INSERT INTO users (username, password, email, status,
userlevel)
VALUES ($1, $2, $3, 1, 0);
GET DIAGNOSTICS new_user = RESULT_OID;
PERFORM some_more_stuff((SELECT uid FROM users WHERE
oid=new_user));
RETURN true;
ELSE
RETURN false; -- username already exists
END IF;
END;
' LANGUAGE 'plpgsql';
-- This is called after a successful login
CREATE OR REPLACE FUNCTION save_login_stats(
users.uid%TYPE,
)
RETURNS BOOL AS '
DECLARE
u users.uid%TYPE;
BEGIN
SELECT INTO u uid FROM users
WHERE uid=$1
FOR UPDATE;
IF FOUND THEN
UPDATE users SET
last_login= CURRENT_TIMESTAMP,
login_counter = login_counter + 1,
WHERE uid=u;
ELSE
RETURN FALSE; -- user not found
END IF;
RETURN TRUE;
END;
' LANGUAGE 'plpgsql';
The database reached a condition were nobody could login because
save_login_stats() did not return. A short time before that problem began
create_user() was called. I can not say if it that create_user() call
returned, because the logfile is not verbose enough at the moment. But to
me it seems as create_user() in any way did not free the shared lock of the
users table, that's why the SELECT ... uid ... FOR UPDATE in
save_login_stats() seems to hang.
As all calls are totally concurrent, can you imagine what could be the problem?
greetings,
boris
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[BUGS] Silent deadlock
Hi!
We run a web application based on a complex database using
postgresql-7.2.3-5.80 on Red Hat 8.0.
Generally using pgsql with JDBC is very nice, but from time to time we run
into problems that are caused by something like a silent deadlock, which
means that it isn't reported in the logfile and doesn't rollback one of the
transactions.
Hanging database connections cause the webapp to not return web pages to
the client, so that the connection pool gets filled up soon, as users try
and try to reload the page. This can freeze the entire application very fast.
Most actions are implemented in PL/PGSQL functions to use the transaction
features of pgsql, so we simply call things like
SELECT create_user('test');
in the application. As it seems to us that it's not very portable to parse
pgsql exceptions, e.g. if you try to insert a duplicate value in a unique
coloumn, we check things like this inside the function before doing the
insert. A table lock is necessary to be sure, that the result of the select
is still valid when the new value is inserted. Have a look at this functions:
CREATE OR REPLACE FUNCTION create_user(
users.username%TYPE,
users.password%TYPE,
users.email%TYPE
)
RETURNS BOOL AS '
DECLARE
old_userINTEGER;
new_userINTEGER;
BEGIN
LOCK users IN SHARE MODE;
SELECT INTO old_user uid FROM users WHERE username=$1;
IF NOT FOUND THEN
INSERT INTO users (username, password, email, status,
userlevel)
VALUES ($1, $2, $3, 1, 0);
GET DIAGNOSTICS new_user = RESULT_OID;
PERFORM some_more_stuff((SELECT uid FROM users WHERE
oid=new_user));
RETURN true;
ELSE
RETURN false; -- username already exists
END IF;
END;
' LANGUAGE 'plpgsql';
-- This is called after a successful login
CREATE OR REPLACE FUNCTION save_login_stats(
users.uid%TYPE,
)
RETURNS BOOL AS '
DECLARE
u users.uid%TYPE;
BEGIN
SELECT INTO u uid FROM users
WHERE uid=$1
FOR UPDATE;
IF FOUND THEN
UPDATE users SET
last_login= CURRENT_TIMESTAMP,
login_counter = login_counter + 1,
WHERE uid=u;
ELSE
RETURN FALSE; -- user not found
END IF;
RETURN TRUE;
END;
' LANGUAGE 'plpgsql';
The database reached a condition were nobody could login because
save_login_stats() did not return. A short time before that problem began
create_user() was called. I can not say if it that create_user() call
returned, because the logfile is not verbose enough at the moment. But to
me it seems as create_user() in any way did not free the shared lock of the
users table, that's why the SELECT ... uid ... FOR UPDATE in
save_login_stats() seems to hang.
As all calls are totally concurrent, can you imagine what could be the problem?
greetings,
boris
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[BUGS] Bugzilla?
Hi!
I posted a bug report to this list. Did anybody receive it (subject: Silent
Deadlock)? I wanted to use bugzilla instead but couldn't find a link to it
on www.postgresql.org. Please move it to a prominent place, if you still
use it.
I'm also wondering what happened to news.postgresql.org, because it does
not respond for days know.
cu,
boris
---(end of broadcast)---
TIP 8: explain analyze is your friend
[BUGS] 1.0 in function call not regarded as REAL in 7.3.2
Hi!
I've triggered a type related problem in postgresql-7.3.2-3
It worked in postgresql-7.2.3-5.80.
CREATE OR REPLACE FUNCTION _rmin(REAL, REAL)
RETURNS REAL AS '
BEGIN
IF $1 <= $2 THEN
RETURN $1;
ELSE
RETURN $2;
END IF;
END;
' LANGUAGE 'plpgsql' WITH (ISCACHABLE);
This works:
SELECT _rmin(1.0, CAST(123 AS REAL));
_rmin
---
1
(1 Zeile)
This not:
SELECT _rmin(1.0, CAST(123 AS REAL)/25);
ERROR: Funktion _rmin(numeric, double precision) existiert nicht
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
Look closely: postmaster now thinks that the first argument 1.0 is NUMERIC,
but I added only the /25 for the _second_ argument!
cu,
boris
--
Dipl.-Inf. Boris Folgmann mailto:[EMAIL PROTECTED]
TeamForge GmbH http://www.teamforge.de
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[BUGS] BUG #1455: pg_dumpall fails
The following bug has been logged online: Bug reference: 1455 Logged by: Boris Makovecki Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.1 Operating system: Windows 2K3 web server Description:pg_dumpall fails Details: I'm trying to migrate postgre DB from 7.4 to 8.0. When I start pg_dumpall (8.0) and connect it to 7.4 it fails with error: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: cannot cast type "unknown" to text pg_dump: The command was: SELECT proretset, prosrc, probin, null::text as proargnames, provolatile, proisstrict, prosecdef, (SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname FROM pg_catalog.pg_proc WHERE oid = '17926077'::pg_catalog.oid oid 17926077 stand for recursive function: CREATE OR REPLACE FUNCTION meta.entity_inherit_from(int4) RETURNS varchar AS 'DECLARE id_e ALIAS FOR $1; rez varchar; a int4; BEGIN SELECT INTO a lng_inherit_from_fkeyi_ref FROM meta.entity WHERE lng_entity_pkeyi=id_e; IF a IS NULL THEN rez= \'\' || id_e; ELSE rez=meta.entity_inherit_from(a) || \',\' || id_e; END IF; RETURN rez; END; ' LANGUAGE 'plpgsql' VOLATILE; please advice, regards, Boris Makovecki ABAK.NET ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1455: pg_dumpall fails
Currently I'm using 7.4.0 version. I'll first upgarade to 7.4.7 and then try to upgarade to 8.0.1, thanks, Boris Makovecki -Original Message- From: Tom Lane <[EMAIL PROTECTED]> To: "Boris Makovecki" <[EMAIL PROTECTED]> Cc: pgsql-bugs@postgresql.org Date: Thu, 03 Feb 2005 01:02:01 -0500 Subject: Re: [BUGS] BUG #1455: pg_dumpall fails > "Boris Makovecki" <[EMAIL PROTECTED]> writes: > > I'm trying to migrate postgre DB from 7.4 to 8.0. When I start > pg_dumpall > > (8.0) and connect it to 7.4 it fails with error: > > > pg_dump: SQL command failed > > pg_dump: Error message from server: ERROR: cannot cast type > "unknown" to > > text > > pg_dump: The command was: SELECT proretset, prosrc, probin, > null::text as > > proargnames, provolatile, proisstrict, prosecdef, (SELECT lanname > FROM > > pg_catalog.pg_language WHERE oid = prolang) as lanname FROM > > pg_catalog.pg_proc WHERE oid = '17926077'::pg_catalog.oid > > Works fine for me, using a 7.4.7 server. Possibly you are hitting some > bug in an earlier 7.4.* release? (I don't see anything promising > looking in the CVS logs, though.) What *exactly* is the server > version? > > regards, tom lane > > ---(end of > broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 8: explain analyze is your friend
[BUGS] BUG #1759: domain check constraint ignored in prepared statement
The following bug has been logged online:
Bug reference: 1759
Logged by: Boris Sukholitko
Email address: [EMAIL PROTECTED]
PostgreSQL version: 7.4.8
Operating system: Linux
Description:domain check constraint ignored in prepared statement
Details:
Hi,
It seems that the domain check constraint does not trigger
an error when using prepared statement in perl's DBD::Pg 1.42.
It seemed to work in earlier versions of DBD::Pg module.
The following code snippet manifests the problem:
#!/usr/bin/perl -w
use strict;
use DBI;
use DBD::Pg;
my $dbh = DBI->connect("dbi:Pg:dbname=testdb");
$dbh->do(<prepare("insert into domain_test (d) values (?)");
# This execute will succeed leaving invalid value
# in the database
$s->execute(' ');
# This statement fails due to the check constraint
# as expected
$dbh->do("insert into domain_test (d) values (?)", undef, ' ');
Thanks,
Boris.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] BUG #5697: Infinite loop inside PQexecStart function
That was a short form i used to point out the issue, the actual code
from src/interfaces/libpq/fe-exec.c
1368 static bool
1369 PQexecStart(PGconn *conn)
1370 {
1371 PGresult *result;
1372
1373 if (!conn)
1374 return false;
1375
1376 /*
1377 * Silently discard any prior query result that
application didn't eat.
1378 * This is probably poor design, but it's here for
backward compatibility.
1379 */
1380 while ((result = PQgetResult(conn)) != NULL)
1381 {
1382 ExecStatusType resultStatus = result->resultStatus;
1383
1384 PQclear(result);/* only need
its status */
1385 if (resultStatus == PGRES_COPY_IN)
1386 {
1387 if (PG_PROTOCOL_MAJOR(conn->pversion) >= 3)
1388 {
1389 /* In protocol 3, we can get
out of a COPY IN state */
1390 if (PQputCopyEnd(conn,
1391
libpq_gettext("COPY terminated by new PQexec")) < 0)
1392 return false;
1393 /* keep waiting to swallow the
copy's failure message */
1394 }
1395 else
1396 {
1397 /* In older protocols we have
to punt */
1398
printfPQExpBuffer(&conn->errorMessage,
1399 libpq_gettext("COPY IN state
must be terminated first\n"));
1400 return false;
1401 }
1402 }
1403 else if (resultStatus == PGRES_COPY_OUT)
1404 {
1405 if (PG_PROTOCOL_MAJOR(conn->pversion) >= 3)
1406 {
1407 /*
1408 * In protocol 3, we can get
out of a COPY OUT state: we just
1409 * switch back to BUSY and
allow the remaining COPY data to be
1410 * dropped on the floor.
1411 */
1412 conn->asyncStatus = PGASYNC_BUSY;
1413 /* keep waiting to swallow the
copy's completion message */
1414 }
1415 else
1416 {
1417 /* In older protocols we have
to punt */
1418
printfPQExpBuffer(&conn->errorMessage,
1419 libpq_gettext("COPY OUT state
must be terminated first\n"));
1420 return false;
1421 }
1422 }
1423 /* check for loss of connection, too */
1424 if (conn->status == CONNECTION_BAD)
1425 return false;
1426 }
1427
1428 /* OK to send a command */
1429 return true;
1430 }
Sorry for the confusion from the shortened form.
Tom Lane wrote:
"Boris" writes:
while ((result = PQgetResult(conn)) != NULL){
ExecStatusType resultStatus = result->resultStatus;
PQclear(result); /* only need its status */
/* check for loss of connection, too */
if (result->resultStatus == PGRES_COPY_IN ||
result->resultStatus == PGRES_COPY_OUT ||
conn->status == CONNECTION_BAD)
break;
}
This code is broken: once you've done PQclear() it's unsafe to access
the PGresult. I think you meant just "resultStatus" not
"result->resultStatus" in the if().
regards, tom lane
--
Boris Bondarenko -:- bbondare...@nyc.yamaha.com
Yamaha Music Interactive Inc.
YMIA
--
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 #6236: Query execution bug
The following bug has been logged online: Bug reference: 6236 Logged by: Boris Matkov Email address: bor...@devart.com PostgreSQL version: 9.0.0.1 Operating system: Windows Description:Query execution bug Details: INSERT INTO btest0 (id, name) VALUES ((SELECT DISTINCT id FROM btest0 WHERE name = $1 LIMIT 1), $1) - was executed successfully INSERT INTO btest0 (name,id) VALUES ($1, (SELECT DISTINCT id FROM btest0 WHERE name = $1 LIMIT 1)) - I got error: --- inconsistent types deduced for parameter $1 --- Script for the btest0 table creating: CREATE TABLE btest0 ( id integer NOT NULL, name character varying(50) NOT NULL, value double precision, CONSTRAINT pk_btest0 PRIMARY KEY (id) ) -- 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 #6774: FOR IN SELECT LOOP ignores ORDER BY
Hi, hubert depesz lubaczewski schrieb/wrote: generally - order by datname is understood as "order by *variable datname*". - which is null. It's clear that it's a shadowing problem. But it's not a "FOR IN EXECUTE" where a variable makes sense. I mean why is a "ORDER BY variable" valid in "FOR IN"? In SQL you can't use ORDER BY NULL. If the order by clause is not correct the whole statement should raise an error. Tnx, Boris -- 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 #1643: dbf2pg broken + quick fix
The following bug has been logged online: Bug reference: 1643 Logged by: Boris van Schooten Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.5, 8.0.2 Operating system: FreeBSD Description:dbf2pg broken + quick fix Details: As several people on the mailing lists have already noted, dbf2pg is broken. It usually fails to enter the numeric values from the dbf file in the sql tables. If you enable verbose output (-vv) you see "Illegal numeric value found" errors. As it turns out, the integer checking code (contrib/dbase/dbf2pg.c, function isinteger) is broken. It appears the function reports 0 in case it finds a space in the dbf rather than a digit. I disabled it (always made it return 1). Now, my dbf files import fine. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #1643: dbf2pg broken + quick fix
It only does the integer check for numbers of type integer (when # of decimals = 0), noninteger numbers are not checked in any way. If isinteger returns 0, the program does give a warning, suggesting it thinks it detected an illegal field rather than a null field. Contrary to the isinteger check on dates, which does not give a warning, though it does enter a null. On dates the check makes sense because dates are always 8 digits long and have no blank padding like numbers. See the spec. http://www.dbase.com/KnowledgeBase/int/db7_file_fmt.htm Don't know anything about nulls in dbf though. I am not a dbase expert, I just run into dbfs often when trying to enter gis data into postgis. Kind regards, Boris van Schooten On Tue, 3 May 2005, Tom Lane wrote: > "Boris van Schooten" <[EMAIL PROTECTED]> writes: > > As it turns out, the integer checking code (contrib/dbase/dbf2pg.c, function > > isinteger) is broken. It appears the function reports 0 in case it finds a > > space in the dbf rather than a digit. I disabled it (always made it return > > 1). Now, my dbf files import fine. > > Hmm. I know nothing about dbase ... but if the test has any use at all, > I guess it must be to handle NULL values. How does dbase represent a > NULL? Why is this code only checking this for integer columns? > > regards, tom lane > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] BUG #1643: dbf2pg broken + quick fix
I can't find anything about dbase nulls either. Never seen one. I'll report it when I find nulls (or other bugs in the code). I'm using some 5-10 different db formats, and noticed most of the converters need some work (as usual). The isinteger seems to be made to do double duty, but it doesn't exactly do it well. The code is dated +/-1995, and the version number is unclear, but I noticed there have been recent changes. Maybe cvs will give more info? Tell me when you got your patch ready, then I'll test the newest version. There was this funny thing with the bug report form on the postgres site. I got mails which suggest that the message bounced. Later I sent a mail to Bruce Momjian. I never knew if you replied to the bug report or to the mail to Bruce. Do you know what happened here? Do bug reports get bounced? Kind regards, Boris van Schooten On Tue, 3 May 2005, Tom Lane wrote: > Boris van Schooten <[EMAIL PROTECTED]> writes: > > See the spec. > > > http://www.dbase.com/KnowledgeBase/int/db7_file_fmt.htm > > Thanks for the link. As far as can be told from this, dbase hasn't got > nulls at all -- is that correct, or are they just omitting a ton of > relevant information? > > It looks to me like we should just remove the special case for integer > fields altogether. The special case for date fields is wrong in detail > as well: as coded it will accept "date" fields with a leading sign, > which surely is not intentional. > > regards, tom lane > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] BUG #1643: dbf2pg broken + quick fix
Looks good to me. I'd prefer to have a warning message (if (verbose) fprintf stderr) for each of the exceptional conditions though. I'm even so paranoid I prefer to have the verbose switch on by default. Kinds regards, Boris van Schooten On Wed, 4 May 2005, Tom Lane wrote: > Boris van Schooten <[EMAIL PROTECTED]> writes: > > Don't know anything about nulls in dbf though. I am not a dbase expert, I > > just run into dbfs often when trying to enter gis data into postgis. > > I'm considering the following patch, which turns around the test: check > for an empty string and if so believe it's a null, otherwise just insert > the value as-is. I dunno if the check for null is actually meaningful, > but I doubt this will break any cases that worked before. Comments anyone? > > regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] BUG #1643: dbf2pg broken + quick fix
I don't see this as half baked error checking, but rather, as error reporting during a "half baked conversion". I prefer to know when the converter encounters something it doesn't understand, rather than having it silently enter nulls into the db. It was the original error reporting that helped me find this bug, I suppose. Kind regards, Boris van Schooten On Thu, 5 May 2005, Tom Lane wrote: > Boris van Schooten <[EMAIL PROTECTED]> writes: > > Looks good to me. I'd prefer to have a warning message (if (verbose) > > fprintf stderr) for each of the exceptional conditions though. I'm even > > so paranoid I prefer to have the verbose switch on by default. > > Don't really see the need for it. What we are doing here is trusting to > the backend to error-check the input, rather than making a half-baked > attempt to do error checking in dbf2pg. > > regards, tom lane > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
