[BUGS] Failed to connect to database - further investigations
Still running Postgresql 8.0 beta1 from the binary MSI Installer, Windows XP Professional now Sevice Pack 2 Stopped the running service, started Postgresql from the command line to get some debugging output: C:\Programme\PostgreSQL\8.0-beta1\bin>postmaster.exe -i WARNING: could not find a match for Windows timezone "Westeuropõische Normalzei t" LOG: database system was shut down at 2004-09-05 12:06:23 Westeuropõische Norma lzeit LOG: checkpoint record is at 0/AB4B78 LOG: redo record is at 0/AB4B78; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 498; next OID: 17228 LOG: database system is ready LOG: select() failed in statistics buffer: Ein Vorgang bezog sich auf ein Objek t, das kein Socket ist. LOG: statistics collector process (PID 2700) was terminated by signal 1 LOG: select() failed in statistics collector: Ein Vorgang bezog sich auf ein Ob jekt, das kein Socket ist. LOG: select() failed in statistics buffer: Ein Vorgang bezog sich auf ein Objek t, das kein Socket ist. ### this message gets repeated without something else happening ... no connection attempt or anything. # # LOG: incomplete startup packet LOG: could not receive data from client: Ein Vorgang bezog sich auf ein Objekt, das kein Socket ist. This gets printed out anytime I try to connect - no matter if from psql or from pgadmin on the client side: C:\Programme\PostgreSQL\8.0-beta1\bin>psql -h localhost psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. Any ideas what could be wrong? Googling led to around 3 people with the same problem, but no solution yet. h ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] plpgsql error handling bug
Hello.
I found a bug in the behaviour of plpgsql error handling system
while trying to handle foreign key violation exception.
When this error occured, control doesn't jump to exception handling block.
It moves to the next statement instead. When control leaves the
function exception is occured. So it's impossible to handle this kind of
exception.
Furthermore, the FOUND local variable is set to true after insert
statement which raises foreign key violation and
GET DIAGNOSTICS var = ROW_COUNT set var to 1.
Attached file contains sample that shows this bug.
Execute fk_violation_bug, fk_violation_bug2 and fk_violation_bug3
plpgsql functions to see this error.
-
I'm using
"PostgreSQL 8.0.0beta1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.2.3 (mingw
special 20030504-1)"
on
OS Name Microsoft Windows XP Professional
Version 5.1.2600 Service Pack 2 Build 2600
Thank you.
--
Best regards,
Ivan mailto:[EMAIL PROTECTED]--
-- PostgreSQL database dump
--
SET client_encoding = 'WIN';
SET check_function_bodies = false;
SET SESSION AUTHORIZATION 'root';
--
-- Name: public; Type: SCHEMA; Schema: -; Owner: root
--
--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: root
--
COMMENT ON SCHEMA public IS 'Standard public schema';
SET search_path = public, pg_catalog;
--
-- Name: plpgsql_call_handler(); Type: FUNCTION; Schema: public; Owner: root
--
CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
AS '$libdir/plpgsql', 'plpgsql_call_handler'
LANGUAGE c;
--
-- Name: plpgsql_validator(oid); Type: FUNCTION; Schema: public; Owner: root
--
CREATE FUNCTION plpgsql_validator(oid) RETURNS void
AS '$libdir/plpgsql', 'plpgsql_validator'
LANGUAGE c;
SET SESSION AUTHORIZATION DEFAULT;
--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: public; Owner:
--
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler VALIDATOR
plpgsql_validator;
SET SESSION AUTHORIZATION 'root';
--
-- Name: fk_violation_bug(); Type: FUNCTION; Schema: public; Owner: root
--
CREATE FUNCTION fk_violation_bug() RETURNS integer
AS '
begin
insert into detailed (main_id, task)
values (-1, ''bug'');
return 0;
end;
'
LANGUAGE plpgsql;
--
-- Name: fk_violation_bug2(); Type: FUNCTION; Schema: public; Owner: root
--
CREATE FUNCTION fk_violation_bug2() RETURNS integer
AS '
begin
insert into detailed (main_id, task)
values (-1, ''bug'');
return 0;
exception
when foreign_key_violation then
raise warning ''foreign key violation'';
return -1;
when others then
raise warning ''other error occured'';
return -2;
end;
'
LANGUAGE plpgsql;
--
-- Name: fk_violation_bug3(); Type: FUNCTION; Schema: public; Owner: root
--
CREATE FUNCTION fk_violation_bug3() RETURNS integer
AS '
declare
tmp int4;
begin
insert into detailed (main_id, task)
values (-1, ''bug'');
raise notice ''should not be there if the foreign key violation occured'';
raise notice ''local variable FOUND value: %'', FOUND;
GET DIAGNOSTICS tmp = ROW_COUNT;
raise notice ''DIAGNOSTICS ROW_COUNT value: %'', tmp;
tmp := 1/0;
raise notice ''should not be there if division by zero occured'';
return 0;
exception
when foreign_key_violation then
raise warning ''foreign key violation'';
return -1;
when division_by_zero then
raise warning ''division by zero'';
return -2;
when others then
raise warning ''other error occured'';
return -3;
end;
'
LANGUAGE plpgsql;
SET default_with_oids = true;
--
-- Name: detailed; Type: TABLE; Schema: public; Owner: root
--
CREATE TABLE detailed (
main_id integer,
task text NOT NULL
);
--
-- Name: main; Type: TABLE; Schema: public; Owner: root
--
CREATE TABLE main (
id serial NOT NULL,
name character varying(64)
);
--
-- Name: main_id_seq; Type: SEQUENCE SET; Schema: public; Owner: root
--
SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('main', 'id'), 1, false);
--
-- Data for Name: detailed; Type: TABLE DATA; Schema: public; Owner: root
--
INSERT INTO detailed (main_id, task) VALUES (1, 'task11');
INSERT INTO detailed (main_id, task) VALUES (1, 'task12');
--
-- Data for Name: main; Type: TABLE DATA; Schema: public; Owner: root
--
INSERT INTO main (id, name) VALUES (1, 'name1');
INSERT INTO main (id, name) VALUES (2, 'name2');
INSERT INTO main (id, name) VALUES (3, 'name3');
--
-- Name: main_pkey; Type: CONSTRAINT; Schema: public; Owner: root
--
ALTER TABLE ONLY main
ADD CONSTRAINT main_pkey PRIMARY KEY (id);
--
-- Name: FK_main_detailed; Type: FK CONSTRAINT; Schema: public; Owner: root
--
ALTER TABLE ONLY detailed
ADD CONSTRAINT "FK_main_detailed" FOREIGN KEY (main_id) REFERENCES main(id) ON
UPDATE RESTRICT ON DELETE CASCADE;
SET SESSION AUTHORIZATION DEFAULT;
SET SESSION AUTHORIZATION 'root';
--
-- PostgreSQL database dump c
Re: [BUGS] readline selection (was Re: [GENERAL] psql leaking? - SOLVED)
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> (2) fix the readline header selection so that it will only take the >> headers that correspond to the selected library. > That sounds like a pretty hard problem to solve. It didn't seem that bad to me. One problem is to not select, eg, in preference to if we've selected libedit for linking to. (I think that is the behavior that bit Russ.) But that seems a small matter of paying attention to a flag variable set by PGAC_CHECK_READLINE. The other problem is that if we select we can't really be sure it matches the selected library. I'm not sure that we *need* to solve that --- if you have readline.so and not then had better be the right thing. But I would think it could be done with some appropriate AC_EGREP_HEADER check if you wanted to be paranoid. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] Permissions problem with sequences
Michael Fuhr <[EMAIL PROTECTED]> writes: > There is, however, an ownership problem with restoring sequences > in 8.0.0beta. In 7.4.5, for example, pg_dump issues a SET SESSION > AUTHORIZATION command and then creates a table, so implicitly-created > sequences are restored with the correct ownership. In 8.0.0beta2, > however, pg_dump doesn't issue SET SESSION AUTHORIZATION but rather > creates a table and then issues ALTER TABLE ... OWNER TO. Yeah, we still need to find a solution for that. I'd prefer not to back out the ALTER OWNER TO changes, but I will if nothing else presents itself. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] plpgsql error handling bug
On Sun, 5 Sep 2004 [EMAIL PROTECTED] wrote: > I found a bug in the behaviour of plpgsql error handling system > while trying to handle foreign key violation exception. > > When this error occured, control doesn't jump to exception handling block. > It moves to the next statement instead. When control leaves the > function exception is occured. So it's impossible to handle this kind of > exception. IIRC that's because the check is not happening until after the function is finished (at the end of the user sent statement). If so, then yes, currently there'd be no way to handle the exception. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] plpgsql error handling bug
[EMAIL PROTECTED] writes: > I found a bug in the behaviour of plpgsql error handling system > while trying to handle foreign key violation exception. This is not a bug in the exception system. The problem is that FK constraints are enforced by triggers that do not fire until the end of the outer statement (that is, the SELECT that calls the plpgsql function). So by the time the constraint error is raised, we have long since exited the exception structure. There has been some talk of changing trigger firing rules to make this sort of thing behave more intuitively inside functions, but it hasn't happened yet. Maybe we should think about doing something about this for 8.0? It's a larger behavioral change than I like to think about for post-beta, but (a) the exception mechanism's usefulness is certainly going to be severely limited if it can't catch FK errors; (b) 8.0 seems like a more appropriate time to introduce backwards-incompatibilities than future 8.x releases. I would imagine that the right thing would be to fire pending non-deferred triggers at the end of every SPI_exec/execp, not only at the outer loop. Are there other places that it would be needed in addition, or instead? Comments? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] plpgsql error handling bug
Tom Lane wrote: > [EMAIL PROTECTED] writes: > >>I found a bug in the behaviour of plpgsql error handling system >>while trying to handle foreign key violation exception. > > > This is not a bug in the exception system. > > The problem is that FK constraints are enforced by triggers that do not > fire until the end of the outer statement (that is, the SELECT that > calls the plpgsql function). So by the time the constraint error is > raised, we have long since exited the exception structure. > > There has been some talk of changing trigger firing rules to make this > sort of thing behave more intuitively inside functions, but it hasn't > happened yet. > > Maybe we should think about doing something about this for 8.0? It's a > larger behavioral change than I like to think about for post-beta, but > (a) the exception mechanism's usefulness is certainly going to be > severely limited if it can't catch FK errors; (b) 8.0 seems like a > more appropriate time to introduce backwards-incompatibilities than > future 8.x releases. Considering also that if you fix the BUG #1231 then 8.0 have already some backwards-incompatibilities so one more is mitigated by the other one. Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] Permissions problem with sequences
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > However, of course the best thing is to just fix it, which I guess I'll > have a crack at... Given that pg_dump does put out GRANT/REVOKE operations on the sequence, it's certainly aware that the sequence exists. I suspect this is just a fixable bug (ie, suppression of output of the sequence CREATE command is being done at the wrong place). regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] transaction block: server closed the connection unexpectedly
Hi (B (BWhile testing 8.0's transaction mechanism, I encountered this error: (B(I can reproduce this error with the latest cvs codes) (B (B (BWelcome to psql 8.0.0beta2, the PostgreSQL interactive terminal. (B (BType: \copyright for distribution terms (B \h for help with SQL commands (B \? for help with psql commands (B \g or terminate with semicolon to execute query (B \q to quit (B (Btemplate1=# BEGIN; (BBEGIN (Btemplate1=# CREATE TABLE FOO ( a int unique); (BNOTICE: CREATE TABLE / UNIQUE will create implicit index "foo_a_key" for (Btable (B"foo" (BCREATE TABLE (Btemplate1=# INSERT INTO FOO VALUES ( 0 ); (BINSERT 17232 1 (Btemplate1=# INSERT INTO FOO VALUES ( 0 ); (BERROR: duplicate key violates unique constraint "foo_a_key" (BFATAL: block 1 of 1663/1/17230 is still referenced (private 1, global 1) (Bserver closed the connection unexpectedly (BThis probably means the server terminated abnormally (Bbefore or while processing the request. (BThe connection to the server was lost. Attempting reset: Succeeded. (Btemplate1=# (B (B (BThis error cann't be reproduced If the CREATE TABLE is called outside the (Btransaction block. (B (B (BRegards (B (Bkoju (B (B (B--- (BKoju Iijima (B (BSoftware Engineer (BFujitsu Australia Software Technology (BAddress: 14 Rodborough Road, Frenchs Forest NSW 2086 (BTel: +61 2 9452 9076 (BFax: +61 2 9975 2899 (BEmail: [EMAIL PROTECTED] (BWeb site: www.fastware.com (B (B--- (B (BThis is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 693 (B481. It is confidential to the ordinary user of the email address to which it was (Baddressed and may contain copyright and/or legally privileged information. No one else (Bmay read, print, store, copy or forward all or any of it or its attachments. If you (Breceive this email in error, please return to sender. Thank you. (B (BIf you do not wish to receive commercial email messages from Fujitsu Australia (BSoftware Technology Pty Ltd, please email [EMAIL PROTECTED] (B (B (B (B---(end of broadcast)--- (BTIP 7: don't forget to increase your free space map settings
