[BUGS] psql command line client behaviour
I was told about behaviour of psql command line client that seems as bug to me. When I am connected to server (cmdline client, interactive mode) and server is restarted next query fails and client try to reconnect to server. Problem arise when I send more queries (not speaking that this may not be a good habit) at once - on first one client detects that connection is lost and try to reconnect and then rest of queries are executed (but not the first one). This can have bad consequences (e.g. if first query is BEGIN;): sample (postgresql 8.3.15): (test@[local:/var/opt/testdb/pg_sockets]:2) 10:55:34 [testdb] # select 10; select 20; ?column? -- 10 (1 row) Time: 0.142 ms ?column? -- 20 (1 row) Time: 0.039 ms (test@[local:/var/opt/testdb/pg_sockets]:2) 10:55:42 [testdb] # select 10; select 20; FATAL: terminating connection due to administrator command server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. ?column? -- 20 (1 row) Time: 0.193 ms I would suggest to not execute any query or all of them after reconnect. Jiri -- 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] Plperl trigger variables no longer global
Excerpts from Alex Hunsaker's message of mié may 04 23:53:34 -0300 2011: After playing with it a bit more I see 2 clear options: 1) make $_TD global like %_SHARED. This should not cause any problems as we make $_TD private via local() before each trigger call. Also pre 9.1 non trigger functions could still access and check the definedness of $_TD so if someone was relying on that (for whatever unknown reason) that will work again. This is strange. Are you saying that there's no decent way to make a variable global in C code? (In other words, the Perl code to create the global var is indecently implemented) -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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 #6006: Will not install
On Thu, May 5, 2011 at 1:51 PM, francis.br...@agfa.com wrote: Good Morning Dave.. Thanks for getting back to me... Please find attached the Dependency Walker files . Having not used this tool before, I hope this is the information you want. I cannot read those files on the Mac I have with me. Can you just tell me if any of the dependent libraries are missing or unusable? They normally show up with a warning icon in the treeview. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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 #6006: Will not install
On Thu, May 5, 2011 at 2:05 PM, francis.br...@agfa.com wrote: Ok... Looking at libubtl.dll there does not appear to be any problem. Looking at initdb.exe It appears to be complaining about IESHIMS.DLL Here is jpg view.. Thanks. ieshims.dll shouldn't be a problem. Still not sure what would cause libintl loading to fail, though I have to wonder if it's something to do with your non-default installation path. Does it work if you install to the default directory? -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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 #6006: Will not install
Thanks Dave! WOW! Really!... Yes that installed ok..but why?I can not have the database installed and running from the C: drive permanently. Kind Regards, mit besten Grüßen Francis Brown | Agfa Graphics Sr Analyst - IS | Branchburg IT GS/Reliability ECG T +1 908 231 | F +1 908 231 5556 BBIT Offices main number 908 231 5375 (local extension 5375) Manufacturing IT Support 24x7 via plant security - contact x5250 http://www.agfa.com/graphics/ From: Dave Page dp...@pgadmin.org To: Francis Brown/MOZOA/AGFA@AGFA Cc: pgsql-bugs@postgresql.org Date: 05/05/2011 09:09 AM Subject:Re: [BUGS] BUG #6006: Will not install On Thu, May 5, 2011 at 2:05 PM, francis.br...@agfa.com wrote: Ok... Looking at libubtl.dllthere does not appear to be any problem. Looking at initdb.exeIt appears to be complaining about IESHIMS.DLL Here is jpg view.. Thanks. ieshims.dll shouldn't be a problem. Still not sure what would cause libintl loading to fail, though I have to wonder if it's something to do with your non-default installation path. Does it work if you install to the default directory? -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [BUGS] BUG #6006: Will not install
On Thu, May 5, 2011 at 2:39 PM, francis.br...@agfa.com wrote: Thanks Dave! WOW! Really!... Yes that installed ok..but why? I can not have the database installed and running from the C: drive permanently. Just a hunch. What is the drive you were trying to use? How is it mounted, and what is the filesystem on it? -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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 #6007: pg_restore -C -d database does not behave as expected when dump is done with pg_dump
The following bug has been logged online: Bug reference: 6007 Logged by: gab Email address: kors...@free.fr PostgreSQL version: 8.4.8 Operating system: FreeBSD Description:pg_restore -C -d database does not behave as expected when dump is done with pg_dump Details: Create a database name 'test' and fill it : CREATE TABLE t ( t text ); ALTER TABLE public.t OWNER TO postgres; -- -- Data for Name: t; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY t (t) FROM stdin; plop \. Then dump it : pg_dump -F c -f /tmp/plop test Drop it : psql -c 'drop database test;' Try to restore it using : pg_restore -C -d test /tmp/plop pg_restore: [archiver (db)] connection to database test failed: FATAL: database test does not exist But the doc says that -C use the parameter of -d to CREATE the database ... -- 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 #6007: pg_restore -C -d database does not behave as expected when dump is done with pg_dump
gab kors...@free.fr writes: Try to restore it using : pg_restore -C -d test /tmp/plop pg_restore: [archiver (db)] connection to database test failed: FATAL: database test does not exist But the doc says that -C use the parameter of -d to CREATE the database ... Well, if the docs say that they shouldn't. When you use -C, the database named by -d is just the one where pg_restore initially connects to issue the CREATE DATABASE command. Please specify where you think the documentation is telling you something else. regards, tom lane -- 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] psql command line client behaviour
Jiri Sadek jiri.sa...@gmail.com writes: I was told about behaviour of psql command line client that seems as bug to me. When I am connected to server (cmdline client, interactive mode) and server is restarted next query fails and client try to reconnect to server. Problem arise when I send more queries (not speaking that this may not be a good habit) at once - on first one client detects that connection is lost and try to reconnect and then rest of queries are executed (but not the first one). The reason it does that is that ON_ERROR_STOP isn't set. If you don't want execution of a multi-command string to continue after an error, you should set that variable. It's not obvious to me that loss of connection should be treated differently here from other reasons for a command failure. Example: $ psql psql (9.1beta1) Type help for help. regression=# select 1/0; select 2; ERROR: division by zero ?column? -- 2 (1 row) regression=# \set ON_ERROR_STOP 1 regression=# select 1/0; select 2; ERROR: division by zero regression=# regards, tom lane -- 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 #5718: Cannot start postgres (FATAL: invalid cache id: 19)
Excerpts from Tom Lane's message of mié oct 20 13:57:16 -0300 2010: Aleksandr Dushein adush...@rutube.ru writes: Description:Cannot start postgres (FATAL: invalid cache id: 19) Hello. I was upgraded my database from 8.4.4 то 9.0.1 one week ago by pg_dump/pg_restore. Yesterday database stop responding with message ERROR: database is not accepting commands to avoid wraparound data loss I trying to restart in single mode and fail with the same error. Ugh. The invalid cache id failure is my fault. You can fix that by applying this patch to 9.0: http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=c98cd9bdb618794643b670c8240c0aad213340c7 after which you'll probably need to do a manual vacuum with a standalone backend before the DB will restart. However, the bigger question is how you got into this state in the first place. Even if you'd had autovacuum turned off, it should have protected you against a wraparound. I suspect that pg_upgrade is failing to preserve datfrozenxid and related values properly. This exact symptom was just reported twice in pgsql-es-ayuda. Upgrading to 9.0.4 fixed the problem. One of them used pg_upgrade from a previous version, the other one did not. I'm not sure that it's worthwhile to expore the underlying cause, but it seems clear that pg_upgrade is not the only possibly guilty party. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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 #5718: Cannot start postgres (FATAL: invalid cache id: 19)
Alvaro Herrera alvhe...@commandprompt.com writes: This exact symptom was just reported twice in pgsql-es-ayuda. Upgrading to 9.0.4 fixed the problem. One of them used pg_upgrade from a previous version, the other one did not. I'm not sure that it's worthwhile to expore the underlying cause, but it seems clear that pg_upgrade is not the only possibly guilty party. Yeah, it seems like we are getting into XID-wraparound-approaching states a lot more easily, or a lot quicker, than I would expect. The original theory about that, which turned out to be true, was that pg_upgrade was bollixing relfrozenxid values. But that makes at least two reports from people who (claimed they) didn't use pg_upgrade. So maybe there is something else going on. I see no way to investigate on the strength of the available evidence though ... regards, tom lane -- 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 #6008: Can't contact Tom Lane :)
gab kors...@free.fr writes: Although All data is restored into the database name that appears in the archive is unclear, as when dumping with pg_dump -F c the dump does not contain the database name ... Don't know where you got that idea from ... pg_restore -l prints the source database name in two different places for me. regards, tom lane -- 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] Plperl trigger variables no longer global
On Thu, May 5, 2011 at 06:51, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Alex Hunsaker's message of mié may 04 23:53:34 -0300 2011: After playing with it a bit more I see 2 clear options: 1) make $_TD global like %_SHARED. This should not cause any problems as we make $_TD private via local() before each trigger call. Also pre 9.1 non trigger functions could still access and check the definedness of $_TD so if someone was relying on that (for whatever unknown reason) that will work again. This is strange. Are you saying that there's no decent way to make a variable global in C code? Im sure we could... I don't see any reason to do it in C. (performance or otherwise) In other news I found another bug with this-- it was trying to local($_TD) by using SAVESPTR() when it seems it really should be using save_item(). Currently its not really localizing $_TD, which at the very least means recursive triggers might modify the callers $_TD. Ugh. Fixed in the attached plus added regression tests for both issues (use strict; Global symbol $_TD requires explicit package name, test recursive trigger calls). Although Ill admit, given the point we are in the release I could see a revert also being justified. Greg, big thanks for testing! keep it up! :) *** a/src/pl/plperl/expected/plperl_trigger.out --- b/src/pl/plperl/expected/plperl_trigger.out *** *** 255,260 SELECT * FROM trigger_test; --- 255,289 5 | third line(modified by trigger)(modified by trigger) | ((5)) (4 rows) + DROP TRIGGER test_valid_id_trig ON trigger_test; + CREATE OR REPLACE FUNCTION trigger_recurse() RETURNS trigger AS $$ + use strict; + + if ($_TD-{new}{i} == 1) + { + spi_exec_query(insert into trigger_test (i, v) values (2, 'child');); + + if ($_TD-{new}{i} != 1) + { + die recursive trigger modified: . $_TD-{new}{i}; + } + } + return; + $$ LANGUAGE plperl; + CREATE TRIGGER test_trigger_recurse BEFORE INSERT ON trigger_test + FOR EACH ROW EXECUTE PROCEDURE trigger_recurse(); + INSERT INTO trigger_test (i, v) values (1, 'top'); + SELECT * FROM trigger_test; +i | v | foo + ---+--+- + 1 | first line(modified by trigger) | ((2)) + 2 | second line(modified by trigger) | ((3)) + 4 | immortal | ((4)) + 5 | third line(modified by trigger)(modified by trigger) | ((5)) + 2 | child| + 1 | top | + (6 rows) + CREATE OR REPLACE FUNCTION immortal() RETURNS trigger AS $$ if ($_TD-{old}{v} eq $_TD-{args}[0]) { *** a/src/pl/plperl/plc_perlboot.pl --- b/src/pl/plperl/plc_perlboot.pl *** *** 1,7 # src/pl/plperl/plc_perlboot.pl use 5.008001; ! use vars qw(%_SHARED); PostgreSQL::InServer::Util::bootstrap(); --- 1,7 # src/pl/plperl/plc_perlboot.pl use 5.008001; ! use vars qw(%_SHARED $_TD); PostgreSQL::InServer::Util::bootstrap(); *** a/src/pl/plperl/plperl.c --- b/src/pl/plperl/plperl.c *** *** 1976,1983 plperl_call_perl_trigger_func(plperl_proc_desc *desc, FunctionCallInfo fcinfo, ENTER; SAVETMPS; ! TDsv = get_sv(_TD, GV_ADD); ! SAVESPTR(TDsv);/* local $_TD */ sv_setsv(TDsv, td); PUSHMARK(sp); --- 1976,1986 ENTER; SAVETMPS; ! TDsv = get_sv(_TD, 0); ! if(!TDsv) ! elog(ERROR, couldn't fetch $_TD); ! ! save_item(TDsv);/* local $_TD */ sv_setsv(TDsv, td); PUSHMARK(sp); *** a/src/pl/plperl/sql/plperl_trigger.sql --- b/src/pl/plperl/sql/plperl_trigger.sql *** *** 122,127 UPDATE trigger_test SET i = 100 where i=1; --- 122,151 SELECT * FROM trigger_test; + DROP TRIGGER test_valid_id_trig ON trigger_test; + + CREATE OR REPLACE FUNCTION trigger_recurse() RETURNS trigger AS $$ + use strict; + + if ($_TD-{new}{i} == 1) + { + spi_exec_query(insert into trigger_test (i, v) values (2, 'child');); + + if ($_TD-{new}{i} != 1) + { + die recursive trigger modified: . $_TD-{new}{i}; + } + } + return; + $$ LANGUAGE plperl; + + CREATE TRIGGER test_trigger_recurse BEFORE INSERT ON trigger_test + FOR EACH ROW EXECUTE PROCEDURE trigger_recurse(); + + INSERT INTO trigger_test (i, v) values (1, 'top'); + + SELECT * FROM trigger_test; + CREATE OR REPLACE FUNCTION immortal() RETURNS trigger AS $$ if ($_TD-{old}{v} eq $_TD-{args}[0]) { -- 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 #6009: Duvida
The following bug has been logged online: Bug reference: 6009 Logged by: Nestor Email address: nestord...@gmail.com PostgreSQL version: 9.0.4-1 Operating system: Windows Description:Duvida Details: Boa tarde, estou tentando instalar o POSTGRE, porem no setup aparece o campo PASSWORD e RETYPE PASSWORD, aonde informo uma senha aletória e redigito ela, porem aparece a seguinte mensagem: THE PASSWORD SPECIFIED WAS INCORRECT. PLEASE ENTER THE CORRECT PASSWORD FOR THE POSTGRES WINDOWNS USER ACCOUNT como faço para resolver este problema??? e dar continuidade na instalação. Desde já agradeço -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Adding a user without expiration date using pgAdmin III causes postgresql Beta1 to crash
Hoi, I have just installed Postgresql Beta 1 on a new Windows 7 64-bit system (without a previously installed PostgreSQL). When using pgAdmin III (included in the installer package) to create a user (Login Role) postgresql crashes. This does not occur when I also set an expiration date on that user. OS: Windows 7 64-bit PostgreSQL version: PostgreSQL 9.1beta1, compiled by Visual C++ build 1500, 64-bit Steps to reproduce: - Download PostgreSQL via Download 9.1 Beta 1 installers http://www.enterprisedb.com/products-services-training/pgdevdownload(offsite link) on the PostgreSQL website - install PostgreSQL (not upgrade) - start pgAdmin - connect to the server - right-click on Login Roles - select New login role... - fill in Role name Password Password (again) - OK can now be clicked - click OK You now see an error dialog (server closed the connection unexpectedly) This is because the server has crashed. Even though it's probably pgAdmin sending an illegal statement to the server I think the major problem here is that it causes the server to crash. Regards, Leon Widdershoven Logs (verbose): 2011-05-05 21:07:33 CEST LOG: 0: database system was shut down at 2011-05-05 21:06:15 CEST 2011-05-05 21:07:33 CEST LOCATION: StartupXLOG, .\src\backend\access\transam\xlog.c:6005 2011-05-05 21:07:33 CEST LOG: 0: database system is ready to accept connections 2011-05-05 21:07:33 CEST LOCATION: reaper, .\src\backend\postmaster\postmaster.c:2435 2011-05-05 21:07:33 CEST LOG: 0: autovacuum launcher started 2011-05-05 21:07:33 CEST LOCATION: AutoVacLauncherMain, .\src\backend\postmaster\autovacuum.c:404 2011-05-05 21:08:29 CEST LOG: 0: server process (PID 2700) was terminated by exception 0xC005 2011-05-05 21:08:29 CEST HINT: See C include file ntstatus.h for a description of the hexadecimal value. 2011-05-05 21:08:29 CEST LOCATION: LogChildExit, .\src\backend\postmaster\postmaster.c:2871 2011-05-05 21:08:29 CEST LOG: 0: terminating any other active server processes 2011-05-05 21:08:29 CEST LOCATION: HandleChildCrash, .\src\backend\postmaster\postmaster.c:2695 2011-05-05 21:08:29 CEST WARNING: 57P02: terminating connection because of crash of another server process 2011-05-05 21:08:29 CEST DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2011-05-05 21:08:29 CEST HINT: In a moment you should be able to reconnect to the database and repeat your command. 2011-05-05 21:08:29 CEST LOCATION: quickdie, .\src\backend\tcop\postgres.c:2592 2011-05-05 21:08:29 CEST LOG: 0: all server processes terminated; reinitializing 2011-05-05 21:08:29 CEST LOCATION: PostmasterStateMachine, .\src\backend\postmaster\postmaster.c:3116 2011-05-05 21:08:39 CEST FATAL: XX000: pre-existing shared memory block is still in use 2011-05-05 21:08:39 CEST HINT: Check if there are any old server processes still running, and terminate them. 2011-05-05 21:08:39 CEST LOCATION: PGSharedMemoryCreate, .\src\backend\port\win32_shmem.c:194
Re: [BUGS] Adding a user without expiration date using pgAdmin III causes postgresql Beta1 to crash
Leon Widdershoven q...@dds.nl writes: I have just installed Postgresql Beta 1 on a new Windows 7 64-bit system (without a previously installed PostgreSQL). When using pgAdmin III (included in the installer package) to create a user (Login Role) postgresql crashes. Hm, this sounds like it's probably the same thing as bug #6005 http://archives.postgresql.org/pgsql-bugs/2011-05/msg9.php which I couldn't reproduce. But both you and that person are using Windows, so a Windows-only bug is looking more probable. Could someone reproduce this and get a stack trace? http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Windows regards, tom lane -- 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] documentation bug - behave of NEW a OLD in plpgsql's triggers
[Moving to -docs] On Mon, May 2, 2011 at 12:00 PM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello one czech user reported a bug in documentation - http://www.postgresql.org/docs/8.4/static/plpgsql-trigger.html NEW Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is NULL in statement-level triggers and for DELETE operations. OLD Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is NULL in statement-level triggers and for INSERT operations. It isn't correct. NEW is not declared in DELETE trigger, OLD isn't declared in INSERT If I've understood you correctly, the problem is that the docs claim that the variables are defined with a value of NULL, when in fact they are undefined. For example, if you try to use variable NEW in a delete trigger, you'll get an error message like: | ERROR: record new is not assigned yet | DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. How about a doc tweak like the attached? Josh diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 1866e43..6b6cf37 100644 *** a/doc/src/sgml/plpgsql.sgml --- b/doc/src/sgml/plpgsql.sgml *** RAISE unique_violation USING MESSAGE = ' *** 3279,3286 para Data type typeRECORD/type; variable holding the new database row for commandINSERT//commandUPDATE/ operations in row-level !triggers. This variable is symbolNULL/symbol in statement-level triggers !and for commandDELETE/command operations. /para /listitem /varlistentry --- 3279,3286 para Data type typeRECORD/type; variable holding the new database row for commandINSERT//commandUPDATE/ operations in row-level !triggers. This variable is not defined in statement-level triggers !or commandDELETE/command operations. /para /listitem /varlistentry *** RAISE unique_violation USING MESSAGE = ' *** 3291,3298 para Data type typeRECORD/type; variable holding the old database row for commandUPDATE//commandDELETE/ operations in row-level !triggers. This variable is symbolNULL/symbol in statement-level triggers !and for commandINSERT/command operations. /para /listitem /varlistentry --- 3291,3298 para Data type typeRECORD/type; variable holding the old database row for commandUPDATE//commandDELETE/ operations in row-level !triggers. This variable is not defined in statement-level triggers !or commandINSERT/command operations. /para /listitem /varlistentry -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs