[BUGS] Bug: ERROR: duplicate key violates unique constraint pg_type_typname_nsp_index

2006-10-30 Thread Rusty Conover 
Under a period of high load it seems that creating a temporary tables  
with the same name in separate postgres back-ends can trigger this  
error:


ERROR:  duplicate key violates unique constraint  
pg_type_typname_nsp_index


The command that causes it is:

create temp table register_reqs(full_key text, register_index  
integer) WITHOUT OIDS ON COMMIT DROP


Is this being caused by a race condition?  Does the create table  
command acquire any locks?


# select version();
  version
 
---
PostgreSQL 8.1.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)  
4.1.0 20060304 (Red Hat 4.1.0-3)

(1 row)

This is on Fedora Core 4.

Thanks,

Rusty
--
Rusty Conover
InfoGears Inc.
Web: http://www.infogears.com



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [BUGS] BUG #2724: Could not check connection status with ssl=on

2006-10-30 Thread Алексей Заяц 
Hi.

 I would argue that this is an OpenSSL bug: it should not be trying to
 write on a connection that it knows perfectly well is already dead.
 (It should know that, anyway, because the only way that pqReadData would
 be trying to close the connection is that it got an error indication
 from OpenSSL.)
May be, may be...

 Possibly we could work around the problem by disabling SIGPIPE during
 connection close, but I don't really see why we should have to do that.
While take a look at source of libpq, i have discover following:
while reading from pipe, you are getting
case SSL_ERROR_ZERO_RETURN:
SOCK_ERRNO_SET(ECONNRESET); 
but why you'r do not check 
SOCK_ERRNO != ECONNRESET
while closing ssl connection ?

i was trying this and all is work fine.

In function close_SSL you are call SSL_shutdown to shutdown ssl pipe.
But if you are already get ECONNRESET (by pear?), why you call whi funtcion?

From openssl docs.
SSL_shutdown  - shuts down an active TLS/SSL connection. It sends the ``close 
notify'' shutdown alert to the peer.

That's why i've got SIGPIPE.

 That's pretty much a waste of time, because all it tells you is whether
 the connection was good the last time a query was done.  It is *not*
 intended as an active ping.
Ok, i'll take it in my mind.

Alexey Zayats.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [BUGS] Bug: ERROR: duplicate key violates unique constraint pg_type_typname_nsp_index

2006-10-30 Thread Tom Lane 
Rusty Conover [EMAIL PROTECTED] writes:
 Under a period of high load it seems that creating a temporary tables  
 with the same name in separate postgres back-ends can trigger this  
 error:
 ERROR:  duplicate key violates unique constraint  
 pg_type_typname_nsp_index

Can you create a reproducible test case?

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[BUGS] beta2: process crash: server process (PID 4872) exited with exit code -1073741819

2006-10-30 Thread Thomas H. 



unfortunately, my problems with 8.2 on win32 become 
more and more severe. when i wanted to test magnus compiled patch for the xlog 
transaction rename lockup, i run into more problems (with the original beta2 
files):

it seems that processes regurarly crash after a 
number of transactions, where that number is strongly related to the amount of 
data updated. for example i have one table that consists of 8 integers, 3 bools, 
2 timestamps, 1 varchar(150)for columns, has 1 foreign keyand 
contains roughly 6600 rows. the table is vacuumed, analyzed, reindexed. when 
updating any of the integer fields, the backend process crashes after 320 to 450 
updates, no matter it the updates are in 1 transaction for all or 1 update per 
transaction).

there are no log entries (beside the ones from the 
startup sequence) prior to the process error... 

i first logged the problem as tsearch2 problem (http://archives.postgresql.org/pgsql-bugs/2006-10/msg00123.php). 
but it seems it is a more general problem.

if anyone wants (db-)access for debuging or 
checking stuff, please let me know. 

best regards,
thomas



2006-10-31 01:04:29 [3304] LOG: 0: 
database system was interrupted at 2006-10-31 01:04:27 W. Europe Standard 
Time2006-10-31 01:04:29 [3304] LOCATION: StartupXLOG, 
xlog.c:46602006-10-31 01:04:29 [3304] LOG: 0: Windows 
fopen("recovery.conf","r") failed: code 2, errno 22006-10-31 01:04:29 [3304] 
LOCATION: AllocateFile, fd.c:12892006-10-31 01:04:29 [3304] LOG: 
0: Windows fopen("pg_xlog/0001.history","r") failed: code 2, errno 
22006-10-31 01:04:29 [3304] LOCATION: AllocateFile, 
fd.c:12892006-10-31 01:04:29 [3304] LOG: 0: Windows 
fopen("backup_label","r") failed: code 2, errno 22006-10-31 01:04:29 [3304] 
LOCATION: AllocateFile, fd.c:12892006-10-31 01:04:29 [3304] LOG: 
0: checkpoint record is at C/84D208102006-10-31 01:04:29 [3304] 
LOCATION: StartupXLOG, xlog.c:47302006-10-31 01:04:29 [3304] 
LOG: 0: redo record is at C/84D20810; undo record is at 0/0; shutdown 
TRUE2006-10-31 01:04:29 [3304] LOCATION: StartupXLOG, 
xlog.c:47572006-10-31 01:04:29 [3304] LOG: 0: next transaction ID: 
0/16121161; next OID: 66609082006-10-31 01:04:29 [3304] LOCATION: 
StartupXLOG, xlog.c:47612006-10-31 01:04:29 [3304] LOG: 0: next 
MultiXactId: 1; next MultiXactOffset: 02006-10-31 01:04:29 [3304] 
LOCATION: StartupXLOG, xlog.c:47642006-10-31 01:04:29 [3304] 
LOG: 0: database system was not properly shut down; automatic recovery 
in progress2006-10-31 01:04:29 [3304] LOCATION: StartupXLOG, 
xlog.c:48312006-10-31 01:04:29 [3304] LOG: 0: redo starts at 
C/84D208602006-10-31 01:04:29 [3304] LOCATION: StartupXLOG, 
xlog.c:48932006-10-31 01:04:29 [3304] LOG: 0: record with zero 
length at C/85164D682006-10-31 01:04:29 [3304] LOCATION: ReadRecord, 
xlog.c:29972006-10-31 01:04:29 [3304] LOG: 0: redo done at 
C/85164D382006-10-31 01:04:29 [3304] LOCATION: StartupXLOG, 
xlog.c:49632006-10-31 01:04:29 [3304] LOG: 0: database system is 
ready2006-10-31 01:04:29 [3304] LOCATION: StartupXLOG, 
xlog.c:51562006-10-31 01:04:29 [3304] LOG: 0: Windows 
fopen("global/pg_fsm.cache","rb") failed: code 2, errno 22006-10-31 01:04:29 
[3304] LOCATION: AllocateFile, fd.c:1289

2006-10-31 01:04:54 [3272] LOG: 0: server 
process (PID 4872) exited with exit code -10737418192006-10-31 01:04:54 
[3272] LOCATION: LogChildExit, postmaster.c:2385
2006-10-31 01:04:54 [3272] LOG: 0: 
terminating any other active server processes2006-10-31 01:04:54 [3272] 
LOCATION: HandleChildCrash, postmaster.c:22772006-10-31 01:04:54 
[3272] LOG: 0: all server processes terminated; 
reinitializing2006-10-31 01:04:54 [3272] LOCATION: reaper, 
postmaster.c:21792006-10-31 01:04:54 [580] LOG: 0: database system 
was interrupted at 2006-10-31 01:04:29 W. Europe Standard Time2006-10-31 
01:04:54 [580] LOCATION: StartupXLOG, xlog.c:46602006-10-31 01:04:54 
[580] LOG: 0: Windows fopen("recovery.conf","r") failed: code 2, errno 
22006-10-31 01:04:54 [580] LOCATION: AllocateFile, 
fd.c:12892006-10-31 01:04:54 [580] LOG: 0: Windows 
fopen("pg_xlog/0001.history","r") failed: code 2, errno 22006-10-31 
01:04:54 [580] LOCATION: AllocateFile, fd.c:12892006-10-31 01:04:54 
[580] LOG: 0: Windows fopen("backup_label","r") failed: code 2, errno 
22006-10-31 01:04:54 [580] LOCATION: AllocateFile, 
fd.c:12892006-10-31 01:04:54 [580] LOG: 0: checkpoint record is at 
C/85164D682006-10-31 01:04:54 [580] LOCATION: StartupXLOG, 
xlog.c:47302006-10-31 01:04:54 [580] LOG: 0: redo record is at 
C/85164D68; undo record is at 0/0; shutdown TRUE2006-10-31 01:04:54 [580] 
LOCATION: StartupXLOG, xlog.c:47572006-10-31 01:04:54 [580] LOG: 
0: next transaction ID: 0/16122460; next OID: 66691002006-10-31 01:04:54 
[580] LOCATION: StartupXLOG, xlog.c:47612006-10-31 01:04:54 [580] 
LOG: 0: next MultiXactId: 1; next MultiXactOffset: 02006-10-31 
01:04:54 [580] LOCATION: StartupXLOG, xlog.c:47642006-10-31

Re: [BUGS] xlog lockup patch (was: BUG #2712: could not fsync segment: Permission)

2006-10-30 Thread Thomas H. 
i've loaded 1gb of data without any xlog-problems, whereas with the 8.2b2 
executable it locked up after ~100mb. the xlog-files are cycling...


if i need to test for some specific behaviour let me know.


maybe a similar patch could be found for the 2nd permission problem, where 
the writer process tries to use a previously deleted file whose filehandle 
is still in use by another postgresql process:


2006-10-31 07:12:37 [5392] ERROR:  42501: could not open relation 
1663/3964774/6696548: Permission denied

2006-10-31 07:12:37 [5392] LOCATION:  mdopen, md.c:366
2006-10-31 07:12:38 [5392] ERROR:  42501: could not open relation 
1663/3964774/6696548: Permission denied

2006-10-31 07:12:38 [5392] LOCATION:  mdopen, md.c:366


thanks for your efforts, very much appreciated!

- thomas

- Original Message - 
Sent: Sunday, October 29, 2006 6:10 PM

Subject: Re: [BUGS] BUG #2712: could not fsync segment: Permission



 I haven't reproduced this on my box. But if you can give me
a patch to
 try I can build binaries for Thomas to test, if he can do
testing but
 not building.

Utterly untested ... BTW, why does pgrename have an #if to
check either GetLastError() or errno, but pgunlink doesn't?


Ok, I've built a .EXE with this patch. It's compiled without pretty much
all other options, hope that still works :-) (Meaning no NLS, no
kerberos, no SSL etc)

Grab the exe from
http://www.hagander.net/download/postgres_renamepatch.zip.

Sorry about the delay.

//Magnus

---(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



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org