Re: [BUGS] BUG #2386: pg_restore doesn't restore large objects
Andreas, I had another opportunity to do a restore for a client today. Both restore attempts that I'll be describing were to a remote Mac OS X machine with PostgreSQL 8.0.7 installed. Knowing that it wasn't going to work using PGAdmin III, I went directly to a command window on the machine with PGAdmin III v1.4.2 installed. The version of pg_restore on that machine is 8.1.3.6044. That machine is running Windows XP SP2. The following command, which didn't work was taken directly from the command window. I've removed the IP address and password for security reasons. C:\Program Files\pgAdmin III\1.4>pg_restore -i -h xx.xx.xx.xx -p 5432 -U password -d mtviewDEV -v C:\mtviewDEV.backup The restore errored out with the following lines: pg_restore: restoring large object data pg_restore: [archiver] could not create large object 28305 pg_restore: *** aborted because of error I then switched over to a Windows Server 2003 machine with PostgreSQL 8.0.4 installed and used pg_restore from the PostgreSQL bin folder. The version for pg_restore on that machine is v8.0.4.5277. On the Windows 2003 machine the restore worked. Only thing is that I lost a lot of the output from pg_restore that appeared in the command window because the lines scrolled out of the display buffer. However, at the point where the error occurred on the Windows XP machine the Windows 2003 machine prompted me for the password a second time. It then reported that it was restoring the large objects and then proceeded to restore other items in the database. Hope that provides some insights into the cause of the problem. Patrick Headley Linx Consulting, Inc. (303) 916-5522 [EMAIL PROTECTED] www.linxco-inc.com -Original Message- From: Andreas Pflug [mailto:[EMAIL PROTECTED] Sent: Friday, April 14, 2006 3:52 AM To: Dave Page Cc: Bruce Momjian; Patrick Headley; Tom Lane; pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #2386: pg_restore doesn't restore large objects Dave Page wrote: > pgAdmin just uses pg_dump/pg_restore to handle the heavy lifting. > >>> When I try to restore the backup using PGAdmin III, the log >>> window begins to fill up. Near the end, when it should say it's >>> restoring the BLOBS an error message appears stating the BLOBS >>> couldn't be restored. This is the original output from pg_restore. pgAdmin will also log the precise parameters used to restore in the first log line, it might help to see those. Regards, Andreas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: Permission denied on fsync / Win32 (was [BUGS] right
The error messages refer to the old relfilenode (in 3 out of 3 occurrences today). Pete >>> Tom Lane <[EMAIL PROTECTED]> 04/14/06 2:41 am >>> OK ... but what's still unclear is whether the failures are occurring against the old relfilenode (the one just removed by the CLUSTER) or the new one just added by CLUSTER. If you note the relfilenodes assigned to these tables just before and just after the next cycle of CLUSTERs, it should be easy to tell what the complaints refer to. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] [Win32] Problem with rename()
Hi all, In the last couple of days, we've been bitten (a couple of times, on different servers) by an apparent glitch or bad interaction in the Windows implementation of rename(). The relevant log message is: [2006-04-17 16:49:22.583 ] 2252 LOG: could not rename file "pg_xlog/0001010A00BD" to "pg_xlog/0001010A00D7", continuing to try It apparently just keeps on looping indefinitely. The "completed rename" message from port/dirmod.c never shows up. Shortly thereafter, Postgres becomes unresponsive. Attempts to make a new connection just block. Autovacuums block. A "pg_ctl ... stop -m fast" doesn't work. Only "pg_ctl ... stop -m immediate" does. With the last occurrence, I saved off the output of "handle -a" and "pslist -x" in case that's helpful. Any thoughts on what might be going wrong? If it happens again, what other clues should I be looking for? Pete ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #2399: Postgis DLL corrupted
The following bug has been logged online: Bug reference: 2399 Logged by: Alvaro Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.3-1 Operating system: Windows Description:Postgis DLL corrupted Details: Hello, I'm from Argentina. I've downloaded twice from different computers the win binaries zip file: postgresql-8.1.3-1.zip It don't throw any error message during install. When I've tried to restore a 8.0 PostGIS DB into a new DB it fails when restoring PostGIS functions, relations, etc. I've found that no libpostgis.dll is in the lib directory. I think it is corrupted in the distribution zip. Well, if it isn't a bug please let me know what I'm doing wrong. Thank you. Postgres rules! ---(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] syntax error
Boa Tarde !! Numa aplicação, de acordo com o btnbotton clicado, a variável opçao receberá um valor. No decorrer do processo, de acordo com a opcao deverá executar um comando da function onde os valores a serem adicionados (input) serão supridos na aplicação.Só que dá o seguinte erro:ERROR: syntax error at or near "$1" at character 36QUERY: insert into tb_conta_corrente (id, $1 , $2 , $3 , $4 , $5 , $6 , $7 , $8 , $9 , $10 , $11 , $12 , $13 , $14 , $15 , $16 , $17 , $18 , $19 , $20 , $21 ) values ( $22 , $23 , $24 , $25 , $26 , $27 , $28 , $29 , $30 , $31 , $32 , $33 , $34 , $35 , $36 , $37 , $38 , $39 , $40 , $41 , $42 )CONTEXT: SQL statement in PL/PgSQL function "iud_tb_conta_corrente" near line 27 === xxx === Syntax: CREATE OR REPLACE FUNCTION iud_tb_conta_corrente( codemp integer, titulo integer, tipolanc char, placa varchar, referencia varchar, codgene varchar, bordero integer, codfor integer, chave varchar, dt_leilao date, dt_cadastro date, dt_pgto date, dt_baixa date, dt_envio date, dt_recbto date, tipomov char(1), valor numeric(18,2), tipo char, observacao varchar, usuario varchar, data_hora timestamp, opcao char) RETURNS varcharAS $$DECLARE vcodemp ALIAS FOR $1; vtitulo ALIAS FOR $2; vtipolanc ALIAS FOR $3; vplaca ALIAS FOR $4; vreferencia ALIAS FOR $5; vcodgene ALIAS FOR $6; vbordero ALIAS FOR $7; vcodfor ALIAS FOR $8; vchave ALIAS FOR $9; vdt_leilao ALIAS FOR $10; vdt_cadastro ALIAS FOR $11; vdt_pgto ALIAS FOR $12; vdt_baixa ALIAS FOR $13; vdt_envio ALIAS FOR $14; vdt_recbto ALIAS FOR $15; vtipomov ALIAS FOR $16; vvalor ALIAS FOR $17; vtipo ALIAS FOR $18; vobservacao ALIAS FOR $19; vusuario ALIAS FOR $20; vdata_hora ALIAS FOR $21; vopcao ALIAS FOR $22;begin if vopcao = 'I' then begin insert into tb_conta_corrente (id,codemp,titulo,tipolanc,placa,referencia,codgene,bordero,codfor,chave,dt_leilao,dt_cadastro,dt_pgto,dt_baixa,dt_envio,dt_recbto,tipomov,valor,tipo,observacao,usuario,data_hora) values (vcodemp,vtitulo,vtipolanc,vplaca,vreferencia,vcodgene,vbordero,vcodfor,vchave,vdt_leilao,vdt_cadastro,vdt_pgto,vdt_baixa,vdt_envio,vdt_recbto,vtipomov,vvalor,vtipo,vobservacao,vusuario,vdata_hora); RETURN 1; end if; if (opcao = 'U') then begin update tb_conta_corrente set codemp = vcodemp, titulo = vtitulo, tipolanc = vtipolanc, placa = vplaca, referencia = vreferencia, codgene = vcodgene, bordero = vbordero, codfor = vcodfor, chave = vchave, dt_leilao = vdt_leilao, dt_pgto = vdt_pgto, dt_baixa = vdt_baixa, dt_envio = vdt_envio, dt_recbto = vdt_recbto, tipomov = vtipomov, valor = vvalor, tipo = vtipo, observacao = vobservacao, usuario = vusuario, data_hora = vdata_hora where ( id = id and codemp = vcodemp and placa = vplaca and referencia = vreferencia and codgene = vcodgene); RETURN 2; end if; if (opcao = 'A') then begin update tb_conta_corrente set codemp = vcodemp, placa = vplaca, referencia = vreferencia, codgene = vcodgene, bordero = vbordero, codfor = vcodfor, chave = vchave, dt_pgto = vdt_pgto, dt_baixa = vdt_baixa, usuario = vusuario, data_hora = vdata_hora where ( id =id and codemp = codemp and placa = placa and referencia = referencia and codgene = codgene and dt_baixa is null); RETURN 3; end if; if (opcao = 'D') then begin delete from tb_conta_corrente where ( id = id and codemp = codemp and placa = placa and tipolanc = tipolanc and referencia = referencia and codgene = codgene); RETURN 0; end if;END $$LANGUAGE 'plpgsql' VOLATILE ; Se tiver uma idéia me
Re: [BUGS] [EMAIL PROTECTED]: BUG in logs]
I have looked at this behavior, which indeed is new for 8.1. The change
was caused by code I think I did to improve the behavior of
log_statement, specifically streamlining how we check for the type of
command.
In looking at reverting to the 8.0 behavior of logging error commands
with 'all', I see it is going to be hard to do, specifically since we
added behavior of logging the PREPARE query when EXECUTE is sent. I
don't think we want to lose that feature, and to have it we have to
first parse the statement, with possible exit on error.
What I have done is to document that errors are not output by
log_statement, and added as suggestion to use log_min_error_statement
for this purpose. I also fixed the code so the first EXECUTE has it's
prepare, rather than the last which is what was in the current code.
I also removed the "protocol" prefix from the PREPARE output, because in
fact both protocol and SQL-level prepares can be executed by SQL
EXECUTE.
Patch attached. I have backpatched this to 8.1.X.
---
Martin Marques wrote:
>
> OK, you're right about the log_min_error_statement value, but this behaviour
> has changed from 8.0. In earlier versions ERROR statements did get logged if
> log_statment was set to all or in 7.4, set to "on" DMaybe I missed something
> in the changelog of 8.1?
>
> On Tue, 11 Apr 2006 23:51:51 +0200, "Guillaume Smet" <[EMAIL PROTECTED]>
> wrote:
> >> From: Martin Marques
> >> I encountered a rare BUG in the way PG is logging. Let me first enlight
> > with some configuration I have and PG version:
> >
> > Perhaps I'm missing something but I think it's not a bug but a
> > configuration problem.
> >
> >> log_min_error_statement| panic
> >
> > If you set this one to error instead of panic, you will have your
> > failed statements logged.
> >
> >> log_statement | all
> >
> > This one only logs successful queries so it's normal you don't have
> > the statement in the log file if it fails.
> >
> > Regards,
> >
> >
> --
> -
> Lic. Mart?n Marqu?s | SELECT 'mmarques' ||
> Centro de Telem?tica| '@' || 'unl.edu.ar';
> Universidad Nacional| DBA, Programador,
> del Litoral | Administrador
> -
>
>
>
> ---(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
>
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/config.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.52
diff -c -c -r1.52 config.sgml
*** doc/src/sgml/config.sgml10 Mar 2006 19:10:47 - 1.52
--- doc/src/sgml/config.sgml18 Apr 2006 00:35:12 -
***
*** 2758,2766
The EXECUTE statement is not considered a
! ddl or mod statement. When it is logged,
! only the name of the prepared statement is reported, not the
! actual prepared statement.
--- 2758,2767
The EXECUTE statement is not considered a
! ddl or mod statement. Statements that
! generate errors are not logged. Set
! log_min_error_statement to error to
! log such statements.
Index: src/backend/tcop/postgres.c
===
RCS file: /cvsroot/pgsql/src/backend/tcop/postgres.c,v
retrieving revision 1.483
diff -c -c -r1.483 postgres.c
*** src/backend/tcop/postgres.c 4 Apr 2006 19:35:35 - 1.483
--- src/backend/tcop/postgres.c 18 Apr 2006 00:35:21 -
***
*** 586,604
/*
* For the first EXECUTE we find, record the client statement
used by
!* the PREPARE.
*/
if (IsA(parsetree, ExecuteStmt))
{
ExecuteStmt *stmt = (ExecuteStmt *) parsetree;
PreparedStatement *entry;
! if ((entry = FetchPreparedStatement(stmt->name, false))
!= NULL &&
entry->query_string)
{
*prepare_string =
palloc(strlen(entry->query_string) +
!
strlen(" [protocol PREPARE: %s]") - 1);
! sprintf(*prepare_string, " [protocol PREPARE:
%s]",
ent
Re: [BUGS] [Win32] Problem with rename()
Peter Brant wrote: > Hi all, > > In the last couple of days, we've been bitten (a couple of times, on > different servers) by an apparent glitch or bad interaction in the > Windows implementation of rename(). > > The relevant log message is: > > [2006-04-17 16:49:22.583 ] 2252 LOG: could not rename file > "pg_xlog/0001010A00BD" to > "pg_xlog/0001010A00D7", continuing to try > > It apparently just keeps on looping indefinitely. The "completed > rename" message from port/dirmod.c never shows up. > > Shortly thereafter, Postgres becomes unresponsive. Attempts to make a > new connection just block. Autovacuums block. A "pg_ctl ... stop -m > fast" doesn't work. Only "pg_ctl ... stop -m immediate" does. > > With the last occurrence, I saved off the output of "handle -a" and > "pslist -x" in case that's helpful. > > Any thoughts on what might be going wrong? If it happens again, what > other clues should I be looking for? Yes, comment I added to dirmod.c give a hint: /* * We need these loops because even though PostgreSQL uses flags that * allow rename while the file is open, other applications might have * these files open without those flags. */ so someone else has the file opened, but didn't use the required flags. As to what could have it open, I don't know. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] [Win32] Problem with rename()
""Peter Brant"" <[EMAIL PROTECTED]> > > In the last couple of days, we've been bitten (a couple of times, on > different servers) by an apparent glitch or bad interaction in the > Windows implementation of rename(). > > The relevant log message is: > > [2006-04-17 16:49:22.583 ] 2252 LOG: could not rename file > "pg_xlog/0001010A00BD" to > "pg_xlog/0001010A00D7", continuing to try > > It apparently just keeps on looping indefinitely. The "completed > rename" message from port/dirmod.c never shows up. > Similar problems have been reported before -- which PG version and do you have any anti-virus software installed? Regards, Qingqing ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] syntax error
Roberto Castro wrote: Numa aplicação, de acordo com o btnbotton clicado, a variável ‘opçao’ receberá um valor. No decorrer do processo, de acordo com a ‘opcao’ deverá executar um comando da function onde os valores a serem adicionados (input) serão supridos na aplicação. Envie somente *bugs* para esta lista. Por favor envie o seu problema para brasil-usuarios@pgfoundry.org (lista em português) ou [EMAIL PROTECTED] (lista em inglês). Report only *bugs* to this list. Please report your problem to brasil-usuarios@pgfoundry.org (brazilian list) or [EMAIL PROTECTED] (english list). -- Euler Taveira de Oliveira ---(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
