Re: [HACKERS] Index grows huge, possible leakage?
PostgreSQL hasn't a rewritten storage managent, this is a normal case. Regards XuYifeng - Original Message - From: "Alfred Perlstein" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: "Mikheev, Vadim" <[EMAIL PROTECTED]> Sent: Friday, February 02, 2001 7:34 AM Subject: [HACKERS] Index grows huge, possible leakage? > After several weeks our idicies grow very large (in one case to > 4-5 gigabytes) After droppping and recreating the indecies they > shrink back to something more reasonable (500megs same case). > > We are currently using Vadim's vacuum patches for VLAZY and MMNB, > against 7.0.3. We are using a LAZY vacuum on these tables > > However a normal (non-lazy) vacuum doesn't shrink the index, the > only thing that helps reduce the size is dropping and recreating. > > Is this a bug in 7.0.3? A possible bug in Vadim's patches? Or is > this somewhat expected behavior that we have to cope with? > > As a side note, the space requirement is actually 'ok' it's just > that performance gets terrible once the indecies reach such huge > sizes. > > -- > -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] > "I have the heart of a child; I keep it in a jar on my desk." >
[HACKERS] Index grows huge, possible leakage?
After several weeks our idicies grow very large (in one case to 4-5 gigabytes) After droppping and recreating the indecies they shrink back to something more reasonable (500megs same case). We are currently using Vadim's vacuum patches for VLAZY and MMNB, against 7.0.3. We are using a LAZY vacuum on these tables However a normal (non-lazy) vacuum doesn't shrink the index, the only thing that helps reduce the size is dropping and recreating. Is this a bug in 7.0.3? A possible bug in Vadim's patches? Or is this somewhat expected behavior that we have to cope with? As a side note, the space requirement is actually 'ok' it's just that performance gets terrible once the indecies reach such huge sizes. -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] "I have the heart of a child; I keep it in a jar on my desk."
[HACKERS] More features for the common man!
Title: More features for the common man! I'm writing this as a convert from MS-Access to PostgreSQL so whatever happens things are getting better :-) When using M$ Access I took for granted that any time I altered my database, I would be able to go back later and revert or alter that table, query, constraint etc so that nothing was set in stone! For Example: If I set a field to varchar(20) and then found out it needed to be larger I could go back and easily change that to varchar(30) but this, if you read the documentation is almost impossible, in PostgreSQL as there is no 'Alter table alter column .. ' command for attribute sizes BTW (I have just found out that this is possible by changing atttypmod in pg_attribute :-) Is this safe?). Also Foreign keys cannot be deleted easily, Not Null Constraints (or is that constraints in general) cannot be changed, columns cannot be dropped etc. I know there are ways round these problems: If you go into pg_attribute you can change attnotnull to add or remove Not Null constraints and if all else fails you can drop and recreate the table. This then opens another can of worms when it comes to rules and triggers. You recreate the table as it says in the docs, but what it does not say is none of your triggers or rules (or views I suppose) will work anymore. They need to be recreated because they refer to the OID of the table instead of the table name. I'm sure there are other examples of this mindset which makes me frustrated at PostgreSQL, and I also know that Postgres is a much more capable database than Access will ever be e.g. transactions, MVCC, triggers, rules, views, loads of other things, so I am changing the way I work so that I can get the best out of Postgres. I was just trying to point out that to make Postgres easier for the simpletons on this planet i.e. myself, could you make it one of your priorities to work on some of these probs. Hopefully for 7.2 - 7.3 Anyway Thanks for all the work so far. Ben
Re: [HACKERS] Re: Re: grant privileges to a database [URGENT]
On Wed, Jan 31, 2001 at 07:18:12PM -0300, Martin A. Marques wrote: > El Mié 31 Ene 2001 18:32, Dan Wilson escribió: > > GRANT ALL ON table1, table2, table3, view1, view2, sequence1, sequence2 TO > > user > The problem is that this is not what I'm looking for. I want the user to be > able to create new tables, views, sequences, etc on that database. This is nbow what people call security. Normaly only the dba (database administrator) is allowed to create tables. But everyone can create tables, views and sequences. The objects are than only accessible to that user. The user who created the table has also set the security on that object. I large company where i work, developers arent allowed to create objects on the database, this because developers make way to soon objects or change current tables, so the whole application doesn't work anymore. We have an user 'postgres'. Only 4 people (there are working 72 people here) have the ssh-key to login as postgres on the database. User postgres is the owner of all the objects in every database. User postgres give the proper security settings for each remote user (normaly websites, applications, etc). I now, i looks bad, but it's really a good thing Dave Mertens Unix System Administrator
Re: [HACKERS] Open 7.1 items
Thanks, Peter. This is a big help. > At 15:02 29/01/01 -0500, Bruce Momjian wrote: > >LAZY VACUUM (Vadim) > >Runtime btree recovery (Vadim) > >JDBC setMaxRows() is global variable affecting other objects > > Now fixed. When called from within a Statement it uses its maxrows value, > but internal queries don't have a restriction. > > >JDBC LargeObject short read return value missing > > Fixed. LargeObject.read(byte[],int,int) now returns the number of bytes > actualy read. > > >ODBC not disconnecting properly? > >Merge MySQL/PgSQL translation scripts > >Fix ipcclean on Linux > >unixODBC > > > >-- > > Bruce Momjian| http://candle.pha.pa.us > > [EMAIL PROTECTED] | (610) 853-3000 > > + If your life is a hard drive, | 830 Blythe Avenue > > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
RE: [HACKERS] Beta 4 problem(s)
Did you recompile PHP with postgre support after you upgraded? > -Original Message- > From: Mitch Vincent [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, February 01, 2001 9:13 AM > To: [EMAIL PROTECTED] > Subject: [HACKERS] Beta 4 problem(s) > > I've been using the 7.1 beta version for quite a while now and just > upgraded > to beta 4, I've noticed my application is reporting that the backend shuts > down prematurely... (I'm using PHP).. I'm having a time trying to debug > this.. I know it's not my code as this works fine on a 7.0.3 install.. > I've > upped my debug level to the max and don't see anything indicating that the > backend crashed, so I'm at a loss.. > > In the PHP code, I just go to execute a query (after checking to make sure > the value returned by pg_connect isn't 0) and I get a PHP warning > "Warning: > 1 is not a valid PostgreSQL link resource in ...". > > I'd like to figure this out, any pointers on what I might be able to do in > the backend? > > It seems to be somewhat PHP related because I can take the individual > queries and run them in psql just fine.. Are there any known issues in > beta4 > that might cause this kind of thing to happen? Are there any changes that > anyone can think of that might need to happen to the PHP PostgreSQL > support > for 7.1? I'd be happy to look into doing making the changes if so.. > > Thanks!! > > -Mitch >
Re: [HACKERS] Beta 4 problem(s)
Yes, I did.. -Mitch - Original Message - From: "Matthew" <[EMAIL PROTECTED]> To: "'Mitch Vincent'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, February 01, 2001 10:33 AM Subject: RE: [HACKERS] Beta 4 problem(s) > Did you recompile PHP with postgre support after you upgraded? > > > -Original Message- > > From: Mitch Vincent [SMTP:[EMAIL PROTECTED]] > > Sent: Thursday, February 01, 2001 9:13 AM > > To: [EMAIL PROTECTED] > > Subject: [HACKERS] Beta 4 problem(s) > > > > I've been using the 7.1 beta version for quite a while now and just > > upgraded > > to beta 4, I've noticed my application is reporting that the backend shuts > > down prematurely... (I'm using PHP).. I'm having a time trying to debug > > this.. I know it's not my code as this works fine on a 7.0.3 install.. > > I've > > upped my debug level to the max and don't see anything indicating that the > > backend crashed, so I'm at a loss.. > > > > In the PHP code, I just go to execute a query (after checking to make sure > > the value returned by pg_connect isn't 0) and I get a PHP warning > > "Warning: > > 1 is not a valid PostgreSQL link resource in ...". > > > > I'd like to figure this out, any pointers on what I might be able to do in > > the backend? > > > > It seems to be somewhat PHP related because I can take the individual > > queries and run them in psql just fine.. Are there any known issues in > > beta4 > > that might cause this kind of thing to happen? Are there any changes that > > anyone can think of that might need to happen to the PHP PostgreSQL > > support > > for 7.1? I'd be happy to look into doing making the changes if so.. > > > > Thanks!! > > > > -Mitch > > >
[HACKERS] Beta 4 problem(s)
I've been using the 7.1 beta version for quite a while now and just upgraded to beta 4, I've noticed my application is reporting that the backend shuts down prematurely... (I'm using PHP).. I'm having a time trying to debug this.. I know it's not my code as this works fine on a 7.0.3 install.. I've upped my debug level to the max and don't see anything indicating that the backend crashed, so I'm at a loss.. In the PHP code, I just go to execute a query (after checking to make sure the value returned by pg_connect isn't 0) and I get a PHP warning "Warning: 1 is not a valid PostgreSQL link resource in ...". I'd like to figure this out, any pointers on what I might be able to do in the backend? It seems to be somewhat PHP related because I can take the individual queries and run them in psql just fine.. Are there any known issues in beta4 that might cause this kind of thing to happen? Are there any changes that anyone can think of that might need to happen to the PHP PostgreSQL support for 7.1? I'd be happy to look into doing making the changes if so.. Thanks!! -Mitch
[HACKERS] WAL Crash during index vacuuming 7.1beta4
Hi, during the nightly vacuum pgsql closed and do not start any more. Attached the log. Seems the problem was rebuilding an Index, There is a way to force wal to ignore indexes ? Can I delete it ? thanks in advance --- Giuseppe Tanzilli [EMAIL PROTECTED] CSF Sistemi srl phone ++39 0775 7771 Via del Ciavattino Anagni FR Italy /usr/local/pgsql/bin/postmaster: reaping dead processes... /usr/local/pgsql/bin/postmaster: CleanupProc: pid 30842 exited with status 0 DEBUG: Index BD_Prodotto_SuddMerc_key: Pages 14533; Tuples 247153: Deleted 246556. CPU 2.61s/4.93u sec. DEBUG: XLogWrite: new log file created - try to increase WAL_FILES DEBUG: XLogWrite: new log file created - try to increase WAL_FILES DEBUG: XLogWrite: new log file created - try to increase WAL_FILES DEBUG: XLogWrite: new log file created - try to increase WAL_FILES DEBUG: XLogWrite: new log file created - try to increase WAL_FILES DEBUG: XLogWrite: new log file created - try to increase WAL_FILES DEBUG: XLogWrite: new log file created - try to increase WAL_FILES DEBUG: XLogWrite: new log file created - try to increase WAL_FILES DEBUG: XLogWrite: new log file created - try to increase WAL_FILES DEBUG: XLogWrite: new log file created - try to increase WAL_FILES DEBUG: XLogWrite: new log file created - try to increase WAL_FILES FATAL: s_lock(0x402a3535) at bufmgr.c:2072, stuck spinlock. Aborting. FATAL: s_lock(0x402a3535) at bufmgr.c:2072, stuck spinlock. Aborting. /usr/local/pgsql/bin/postmaster: reaping dead processes... /usr/local/pgsql/bin/postmaster: CleanupProc: pid 30355 exited with status 6 Server process (pid 30355) exited with status 6 at Thu Feb 1 04:36:21 2001 Terminating any active server processes... /usr/local/pgsql/bin/postmaster: CleanupProc: sending SIGUSR1 to process 30843 /usr/local/pgsql/bin/postmaster: ServerLoop:handling reading 5 /usr/local/pgsql/bin/postmaster: ServerLoop:handling reading 5 The Data Base System is in recovery mode /usr/local/pgsql/bin/postmaster: ServerLoop:handling writing 5 /usr/local/pgsql/bin/postmaster: ServerLoop:handling reading 5 /usr/local/pgsql/bin/postmaster: ServerLoop:handling reading 5 The Data Base System is in recovery mode /usr/local/pgsql/bin/postmaster: ServerLoop:handling writing 5 /usr/local/pgsql/bin/postmaster: ServerLoop:handling reading 5 /usr/local/pgsql/bin/postmaster: ServerLoop:handling reading 5 The Data Base System is in recovery mode /usr/local/pgsql/bin/postmaster: ServerLoop:handling writing 5 DEBUG: proc_exit(0) DEBUG: shmem_exit(0) DEBUG: exit(0) /usr/local/pgsql/bin/postmaster: reaping dead processes... /usr/local/pgsql/bin/postmaster: CleanupProc: pid 30843 exited with status 0 Server processes were terminated at Thu Feb 1 04:36:38 2001 Reinitializing shared memory and semaphores invoking IpcMemoryCreate(size=68608000) DEBUG: starting up DEBUG: database system was interrupted at 2001-02-01 04:36:38 DEBUG: CheckPoint record at (6, 4150292860) DEBUG: Redo record at (6, 4144414796); Undo record at (6, 3622728240); Shutdown FALSE DEBUG: NextTransactionId: 156659; NextOid: 7401689 DEBUG: database system was not properly shut down; automatic recovery in progress... DEBUG: redo starts at (6, 4144414796) NOTICE: PageAddItem: tried overwrite of used ItemId FATAL 2: heap_update_redo: failed to add tuple DEBUG: proc_exit(2) DEBUG: shmem_exit(2) DEBUG: exit(2) /usr/local/pgsql/bin/postmaster: reaping dead processes... Startup failed - abort invoking IpcMemoryCreate(size=68608000) FindExec: found "/usr/local/pgsql/bin/postmaster" using argv[0] DEBUG: starting up DEBUG: database system was interrupted being in recovery at 2001-02-01 04:36:38 This propably means that some data blocks are corrupted and you will have to use last backup for recovery. DEBUG: CheckPoint record at (6, 4150292860) DEBUG: Redo record at (6, 4144414796); Undo record at (6, 3622728240); Shutdown FALSE DEBUG: NextTransactionId: 156659; NextOid: 7401689 DEBUG: database system was not properly shut down; automatic recovery in progress... DEBUG: redo starts at (6, 4144414796) NOTICE: PageAddItem: tried overwrite of used ItemId FATAL 2: heap_update_redo: failed to add tuple DEBUG: proc_exit(2) DEBUG: shmem_exit(2) DEBUG: exit(2) /usr/local/pgsql/bin/postmaster: reaping dead processes... Startup failed - abort invoking IpcMemoryCreate(size=68608000) FindExec: found "/usr/local/pgsql/bin/postmaster" using argv[0] DEBUG: starting up DEBUG: database system was interrupted being in recovery at 2001-02-01 09:47:40 This propably means that some data blocks are corrupted and you will have to use last backup for recovery. DEBUG: CheckPoint record at (6, 4150292860) DEBUG: Redo record at (6, 4144414796); Undo record at (6, 3622728240); Shutdown FALSE DEBUG
[GENERAL] Re: Re: grant privileges to a database [URGENT]
El Mié 31 Ene 2001 21:32, Mike Miller escribió: > MySQL has this feature. I run a multi-user system and require shared MySQL > And PostgreSQL dbs. I have mysql fine. Users have their DBs and can only > access their DBs... but theres no real way to do this in Postgres. I can > restrict tables, but I can still create tables in other poeples DBs. Maybe > only allowed to acess DBs you create or are assigned permission to This is what I'm talking about (sort of) and I find it a handy thing that informix has since at least 1995. Lets say I have a database server and I create databases for diferent groups of people to populate. Thtas what grant dba would be usefull for. Saludos... :-) -- System Administration: It's a dirty job, but someone told I had to do it. - Martín Marqués email: [EMAIL PROTECTED] Santa Fe - Argentinahttp://math.unl.edu.ar/~martin/ Administrador de sistemas en math.unl.edu.ar -
