[BUGS] Bug #868: temp schema can't be clean automatically
trainee ([EMAIL PROTECTED]) reports a bug with a severity of 3 The lower the number the more severe it is. Short Description temp schema can't be clean automatically Long Description postgresql 7.31 + mandrake linux 8.1 when I create a temporary table, server will create temporary schema pg_temp_X automatically. after I disconnect the server, the temporary table will be drop automatically, but temporary schema can't be drop automatically. So my database remain several temporary schema, I don't know why? Sample Code No file was uploaded with this report ---(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] Version 7.2.3 unrecoverable crash on missing pg_clog
All, One of our databases crashed yesterday with a bug that looks a lot like the non superuser vacuum issue that 7.2.3 was intended to fix, although we do our vacuum with a user that has usesuper=t in pg_user so I guess it's not that simple. From the logs: DEBUG: pq_recvbuf: unexpected EOF on client connection FATAL 2: open of /u0/pgdata/pg_clog/0726 failed: No such file or directory DEBUG: server process (pid 4232) exited with exit code 2 DEBUG: terminating any other active server processes NOTICE: Message from PostgreSQL backend: The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory. I have rolled back the current transaction and am going to terminate your database system connection and exit. Please reconnect to the database system and repeat your query. [backend message repeated] FATAL 1: The database system is in recovery mode [repeated] DEBUG: all server processes terminated; reinitializing shared memory and semaphores DEBUG: database system was interrupted at 2003-01-08 20:14:06 GMT DEBUG: checkpoint record is at 69/74D200E4 DEBUG: redo record is at 69/74D0DA14; undo record is at 0/0; shutdown FALSE DEBUG: next transaction id: 221940405; next oid: 281786728 DEBUG: database system was not properly shut down; automatic recovery in progress DEBUG: redo starts at 69/74D0DA14 DEBUG: ReadRecord: record with zero length at 69/74D2634C DEBUG: redo done at 69/74D26328 FATAL 1: The database system is starting up [repeated] DEBUG: database system is ready Then almost immediately it went out again FATAL 2: open of /u0/pgdata/pg_clog/0656 failed: No such file or directory DEBUG: server process (pid 13054) exited with exit code 2 DEBUG: terminating any other active server processes NOTICE: Message from PostgreSQL backend: The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory. I have rolled back the current transaction and am going to terminate your database system connection and exit. Please reconnect to the database system and repeat your query. [repeated] FATAL 1: The database system is in recovery mode FATAL 1: The database system is in recovery mode FATAL 1: The database system is in recovery mode DEBUG: all server processes terminated; reinitializing shared memory and semaphores DEBUG: database system was interrupted at 2003-01-08 20:16:12 GMT DEBUG: checkpoint record is at 69/74D2634C DEBUG: redo record is at 69/74D2634C; undo record is at 0/0; shutdown TRUE DEBUG: next transaction id: 221940709; next oid: 281786728 DEBUG: database system was not properly shut down; automatic recovery in progress FATAL 1: The database system is starting up [repeated] DEBUG: redo starts at 69/74D2638C DEBUG: ReadRecord: record with zero length at 69/754828E8 DEBUG: redo done at 69/754828C4 FATAL 1: The database system is starting up [repeated] DEBUG: database system is ready and again FATAL 2: open of /u0/pgdata/pg_clog/0452 failed: No such file or directory DEBUG: server process (pid 13451) exited with exit code 2 DEBUG: terminating any other active server processes NOTICE: Message from PostgreSQL backend: The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory. I have rolled back the current transaction and am going to terminate your database system connection and exit. Please reconnect to the database system and repeat your query. and so on until we shut it down. Platform is a Dell 6650 Quad Xeon 1.4GHz with hyperthreading switched on. 2GB RAM. Running RedHat 7.3 with their kernel "2.4.18-10smp #1 SMP Wed Aug 7 11:17:48 EDT 2002 i686 unknown". We built our postgresql from source with: ./configure --with-perl --with-openssl --enable-syslog and with NAMEDATALEN = 64 in postgres_ext.h. select version() reports PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC 2.96 and we are building against perl "perl5 (revision 5.0 version 6 subversion 1)" which is from the RedHat rpm but rebuilt to have a shared libperl. With postgres up in single user mode, anything that touched one particular table (called news - very active and about 450MB in size and about 83k rows) caused postgres to fail as above. In the end we dropped this table, vacuumed (full) the database and put the table back from a backup that was about 3 hrs old. The database has been ok since. We vacuum every night and vacuum --full once a week. The database cluster has six databases (8 incl template[01]) of which five are very active. Typically 150 or so connections active. postgresql.conf options that we've altered from default are ... max_connections = 512 shared_buffers = 8192 wal_buffers = 12 sort_mem = 32768 vacuum_mem = 32768 wal_files = 8 This is the only time we've seen this happen and I can't reproduce it on our test machine
Re: [BUGS] Bug #868: temp schema can't be clean automatically
[EMAIL PROTECTED] writes: > So my database remain several temporary schema, I don't know why? Because it's designed to work that way. What's the point of dropping the schema entry, only to have to recreate it later? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Version 7.2.3 unrecoverable crash on missing pg_clog
Andy Osborne <[EMAIL PROTECTED]> writes: > One of our databases crashed yesterday with a bug that looks > a lot like the non superuser vacuum issue that 7.2.3 was > intended to fix, although we do our vacuum with a user that > has usesuper=t in pg_user so I guess it's not that simple. > FATAL 2: open of /u0/pgdata/pg_clog/0726 failed: No such file or directory What range of file names do you actually see in pg_clog? The fixes in 7.2.3 were for cases that would try to access already-removed clog segments (file numbers less than what's present). In this case the accessed file name is large enough that I'm thinking the problem is due to a garbage transaction number being passed to the transaction-status-check code. So my bet is on physical data corruption in the table that was causing the problem. It turns out that the first detectable symptom of a trashed tuple header is often a failure like this :-(. You didn't happen to make a physical copy of the news table before dropping it, did you? It'd be interesting to examine the remains. So far, the cases I have seen like this all seem to be due to hardware faults, but we've seen it just often enough to make me wonder if there is a software issue too. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] Version 7.2.3 unrecoverable crash on missing pg_clog
Tom Lane wrote: Andy Osborne <[EMAIL PROTECTED]> writes: One of our databases crashed yesterday with a bug that looks a lot like the non superuser vacuum issue that 7.2.3 was intended to fix, although we do our vacuum with a user that has usesuper=t in pg_user so I guess it's not that simple. FATAL 2: open of /u0/pgdata/pg_clog/0726 failed: No such file or directory What range of file names do you actually see in pg_clog? Currently to 00D6. I don't know what it was last night. The fixes in 7.2.3 were for cases that would try to access already-removed clog segments (file numbers less than what's present). In this case the accessed file name is large enough that I'm thinking the problem is due to a garbage transaction number being passed to the transaction-status-check code. So my bet is on physical data corruption in the table that was causing the problem. It turns out that the first detectable symptom of a trashed tuple header is often a failure like this :-(. /u0 is a Linux software RAID using RAID 1 on three disks with two raid-disks and one spare-disk. The previous backup (a pg_dump in plain text SQL) ran ok and produced a clean backup that we were able to reload. We do this every three hours and the next backup was running when the database crashed. Any attempt to access the table crashed it again. I don't know if it helps, but a select * from news where You didn't happen to make a physical copy of the news table before dropping it, did you? It'd be interesting to examine the remains. So far, the cases I have seen like this all seem to be due to hardware faults, but we've seen it just often enough to make me wonder if there is a software issue too. Sadly, no I didn't. This is one of our live database servers and I was under a lot of pressure to get it back quickly. If it does it again, what can I do to provide the most useful feedback ?. Thanks, Andy -- Andy Osborne "Vertical B2B Communities" Senior Internet Engineer Sift Group100 Victoria Street, Bristol BS1 6HZ tel:+44 117 915 9600 fax:+44 117 915 9630 http://www.sift.co.uk ---(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] Version 7.2.3 unrecoverable crash on missing pg_clog
Andy Osborne <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >>> FATAL 2: open of /u0/pgdata/pg_clog/0726 failed: No such file or directory >> What range of file names do you actually see in pg_clog? > Currently to 00D6. I don't know what it was last night. Not any greater, for sure. (FYI, each segment covers one million transactions.) > the next backup was running when the database crashed. Any > attempt to access the table crashed it again. I don't know if > it helps, but a select * from news where with an index) was ok but if the where was not indexed and resulted > in a table scan, it crashed it. This is consistent with one page of the table being corrupted. > While I wouldn't rule out data corruption, the kernel message > ring has no errors for the md dirver, scsi host adapter or the > disks, which I would expect if we had bad blocks appearing on a > disk or somesuch. Some of the cases that I've seen look like completely unrelated data (not even Postgres stuff, just bits of text files) was written into a page of a Postgres table. This could possibly be a kernel bug, along the lines of getting confused about which buffer belongs to which file. But with no way to reproduce it it's hard to pin blame. >> You didn't happen to make a physical copy of the news table before >> dropping it, did you? It'd be interesting to examine the remains. > Sadly, no I didn't. This is one of our live database servers > and I was under a lot of pressure to get it back quickly. If > it does it again, what can I do to provide the most useful > feedback ?. If the database isn't unreasonably large, perhaps you could take a tarball dump of the whole $PGDATA directory tree while the postmaster is stopped? That would document the situation for examination at leisure. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Version 7.2.3 unrecoverable crash on missing pg_clog
Tom Lane wrote: Andy Osborne <[EMAIL PROTECTED]> writes: Tom Lane wrote: > But with no way to reproduce it it's hard to pin blame. Sad but true :-( You didn't happen to make a physical copy of the news table before dropping it, did you? It'd be interesting to examine the remains. Sadly, no I didn't. This is one of our live database servers and I was under a lot of pressure to get it back quickly. If it does it again, what can I do to provide the most useful feedback ?. If the database isn't unreasonably large, perhaps you could take a tarball dump of the whole $PGDATA directory tree while the postmaster is stopped? That would document the situation for examination at leisure. It's about 8GB in total, so that seems like a plan. Thanks for your help, Andy -- Andy Osborne "Vertical B2B Communities" Senior Internet Engineer Sift Group100 Victoria Street, Bristol BS1 6HZ tel:+44 117 915 9600 fax:+44 117 915 9630 http://www.sift.co.uk ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[BUGS] dropping user doesn't erase his rights.
Versions tested: 7.4devel from cvs and 7.2.x (i'm not sure about x since the test was done by somebody else). Description: When dropping user his rights stay in database creating possible security breach. Sample Code: create table xxx (...); create user test; grant select on xxx to test; select relacl from pg_class where relname='czasy'; drop user test; select relacl from pg_class where relname='czasy'; right now it's not even possible to revoke this rights: # revoke all on xxx from test; ERROR: user "test" does not exist [[local]:5432] [depesz@depesz] # revoke all on xxx from 102; ERROR: parser: syntax error at or near "102" at character 24 I belive drop user should automatically drop all user privileges, and even if not there should be simple syntax to drop all user privileges from all objects in database (dropping all privileges "by hand" might be major pain when dealing with > 100 tables with several hundreds of views, procedures and so on. depesz -- hubert depesz lubaczewski http://www.depesz.pl/ > wynajmę mieszkanie - 60 metrów, 3 pokoje, piastów - od lutego (po < -=> remoncie) interesuje cię - napisz: [EMAIL PROTECTED]<=- msg05632/pgp0.pgp Description: PGP signature
Re: [BUGS] dropping user doesn't erase his rights.
Hubert depesz Lubaczewski <[EMAIL PROTECTED]> writes: > I belive drop user should automatically drop all user privileges Difficult to do, when those privileges might be recorded in databases you're not even connected to at the time of the drop. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] dropping user doesn't erase his rights.
On Thu, Jan 09, 2003 at 11:21:56AM -0500, Tom Lane wrote: > Difficult to do, when those privileges might be recorded in databases > you're not even connected to at the time of the drop. I belive it would be pretty difficult, but leaving it "just like that" creates ssecurity breach (imagine someone droping user, beliving that everytinh is o.k.), than someone else creates different user but with keeping unused sysid (this might be the case with system users and keeping system user-id with database user-id the same) - which happens to be "not unused". i'm not sure if i'm clear about it. depesz -- hubert depesz lubaczewski http://www.depesz.pl/ > wynajmę mieszkanie - 60 metrów, 3 pokoje, piastów - od lutego (po < -=> remoncie) interesuje cię - napisz: [EMAIL PROTECTED]<=- msg05634/pgp0.pgp Description: PGP signature
Re: [BUGS] Libpq is not a shared library on Mac OS X
Peter Eisentraut <[EMAIL PROTECTED]> writes: > One thing I like about the patch is that it introduces a differentiation > between run-time loadable modules and build-time linkable libraries, which > is something I've wanted to do for a while. Even on platforms where this > isn't technically necessary we could choose better file names, such as > plpgsql.so instead of libplpgsql.so.0.0. I'd prefer if a more general > term than "bundle" is used. That bothered me too. Got a suggestion for a better name? 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])
[BUGS] Bug #869: varchar 's comparision
trainee ([EMAIL PROTECTED]) reports a bug with a severity of 2 The lower the number the more severe it is. Short Description varchar 's comparision Long Description SERVER: PGSQL 7.31 + MANDRAKE LINUX 8.1 CLIENT: VISIVAL FOXPRO 7.0 + PGSQLODBC 7.02 TABLE :MYTABLE M_ID VARCHAR(10) M_DESCVARCHAR(10) --- ABC HELLO I create a romate view in vfp, when I modify "m_desc" from "HELLO" TO "GOOD" ,and update table, vfp trigger a error "UPDATE CONFLICT". In odbc-log: (THIS SQL SYNTAX IS GENERATED BY VFP ) --- UPDATE MYTABLE SET M_DESC='GOOD' WHERE M_ID='ABC ' AND M_DESC='HELLO ' UPDATE 0 RECORDS no record be updated, so VFP trigger a error "UPDATE CONFLICT" But if you execute SQL manually - UPDATE MYTABLE SET M_DESC='GOOD' WHERE M_ID='ABC' AND M_DESC='HELLO' UPDATE 1 RECORDS - HOW CAN I DO? THANKS Sample Code No file was uploaded with this report ---(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 #869: varchar 's comparision
On Thu, 9 Jan 2003 [EMAIL PROTECTED] wrote: > trainee ([EMAIL PROTECTED]) reports a bug with a severity of 2 > The lower the number the more severe it is. > > Short Description > varchar 's comparision > > Long Description > SERVER: PGSQL 7.31 + MANDRAKE LINUX 8.1 > CLIENT: VISIVAL FOXPRO 7.0 + PGSQLODBC 7.02 > > TABLE :MYTABLE >M_ID VARCHAR(10) M_DESCVARCHAR(10) > --- >ABC HELLO > > I create a romate view in vfp, when I modify "m_desc" from "HELLO" > TO "GOOD" ,and update table, vfp trigger a error "UPDATE CONFLICT". > > In odbc-log: (THIS SQL SYNTAX IS GENERATED BY VFP ) > --- > UPDATE MYTABLE SET M_DESC='GOOD' WHERE M_ID='ABC ' AND M_DESC='HELLO ' varchar() considers trailing spaces as significant, so 'ABC'!='ABC ' If it's going to generate values with trailing spaces, you might want to use char() instead. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Bug #869: varchar 's comparision
[EMAIL PROTECTED] writes: >M_ID VARCHAR(10) M_DESCVARCHAR(10) > --- >ABC HELLO > UPDATE MYTABLE SET M_DESC='GOOD' WHERE M_ID='ABC ' AND M_DESC='HELLO ' > UPDATE 0 RECORDS My guess is that the stored values do not have trailing spaces. Trailing spaces are significant data in varchar fields. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Libpq is not a shared library on Mac OS X
Tom Lane writes: > I will test and apply the parts that are related to OS X shared library > behavior. If Benjamin wants to advocate the other stuff (like pam.h > search path) that should be handled as separate patches. One thing I like about the patch is that it introduces a differentiation between run-time loadable modules and build-time linkable libraries, which is something I've wanted to do for a while. Even on platforms where this isn't technically necessary we could choose better file names, such as plpgsql.so instead of libplpgsql.so.0.0. I'd prefer if a more general term than "bundle" is used. However, the trick is that some libraries may be used both ways. For example, libpgtcl is build-time linked by pgtclsh but run-time loaded by PgAccess. (PgAccess uses the shared-library extension provided by the Tcl configuration interface, which might be equally confused about this issue. It's further complicated because you can actually run-time load the build-time linkable file type if you try hard enough. So be sure to check that whatever you do works with PgAccess.) -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
