[ADMIN] set AUTOCOMMIT OFF in psqlrc not having affect
I have \set AUTOCOMMIT OFF in my .psqlrc and it seems psql seems to not have any effect. Am I missing something? I've been googling and googling and seem a bit lost at this point. [EMAIL PROTECTED] host]$ cat ~/.psqlrc \set AUTOCOMMIT OFF \echo 'AUTOCOMMIT is' :AUTOCOMMIT \set PROMPT1 'host.%/%R%# ' \timing *timing statments where trimmed below* [EMAIL PROTECTED] host]$ psql Welcome to psql 7.4.5, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit AUTOCOMMIT is OFF Timing is on. host.domain=# commit; WARNING: there is no transaction in progress COMMIT host.domain=# update foo set foo_type_id = 3 where foo_id=20234; UPDATE 1 host.domain=# commit; WARNING: there is no transaction in progress COMMIT host.domain=# update foo set foo_type_id = 3 where foo_id=20234; UPDATE 1 host.domain=# rollback; WARNING: there is no transaction in progress ROLLBACK host.domain=# begin; BEGIN host.domain=# update foo set foo_type_id = 3 where foo_id=20234; UPDATE 1 host.domain=# rollback; ROLLBACK host.domain=# begin; BEGIN host.domain=# update foo set foo_type_id = 3 where foo_id=20234; UPDATE 1 host.domain=# rollback; ROLLBACK host.domain=# \q [ \ / [ X Christian Fowler | [EMAIL PROTECTED] [ / \ http://www.viovio.com | http://www.tikipro.org ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] set AUTOCOMMIT OFF in psqlrc not having affect
Christian Fowler [EMAIL PROTECTED] writes: I have \set AUTOCOMMIT OFF in my .psqlrc and it seems psql seems to not have any effect. I think the value is case-sensitive. Try \set AUTOCOMMIT off regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] set AUTOCOMMIT OFF in psqlrc not having affect
FYI, it is not case-sensitive in current CVS. --- Tom Lane wrote: Christian Fowler [EMAIL PROTECTED] writes: I have \set AUTOCOMMIT OFF in my .psqlrc and it seems psql seems to not have any effect. I think the value is case-sensitive. Try \set AUTOCOMMIT off regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] Problem in starting postgres server on sun solaris 5.7
Hi! I installed postgresql-7.2.1 on Sun solaris 5.7 running on sparc processor. while installing i had no problems but as soon as start with following line /usr/local/pgsql/bin/postmaster -D /user2/pgsql/data or /usr/local/pgsql/bin/pg_ctl -D /user2/pgsql/data -l logfile start kailas# /usr/local/pgsql/bin/postmaster -D /user2/pgsql/data IpcMemoryCreate: shmget(key=5432001, size=1441792, 03600) failed: Invalid argument This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter. You can either reduce the request size or reconfigure the kernel with larger SHMMAX. To reduce the request size (currently 1441792 bytes), reduce PostgreSQL's shared_buffers parameter (currently 64) and/or its max_connections parameter (currently 32). If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for. The PostgreSQL Administrator's Guide contains more information about shared memory configuration. I am a new person with sun solaris so please sugest me in details i you can ! with regards Dattaram (Sys Admin) POD div. National Institute of Oceanography Donapaula -Goa India ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Suppressing duplicate key error messages in
At 11:59 PM 10/16/2004, Tom Lane wrote: Larry Lennhoff [EMAIL PROTECTED] writes: Is there any way to suppress this message so that it won't appear in the log? I looked at the documentation and log_min_error_statement looked promising. I set it to panic, but the messages continue. What am I doing wrong? Did you remember to SIGHUP the postmaster after editing postgresql.conf? After the crash (caused by the log filling all available disk space) and restart I tried the following: pexicast_datran_sg=# show log_min_error_statement; log_min_error_statement - panic (1 row) I did not change the value after the restart. I'm using pexicast 7.3.4. There are two databases in this instance, both have log_min_error_statement set to panic. Let me know if there is anything else I can tell you that might help. Larry ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Suppressing duplicate key error messages in
Larry Lennhoff [EMAIL PROTECTED] writes: Is there any way to suppress this message so that it won't appear in the log? I looked at the documentation and log_min_error_statement looked promising. I set it to panic, but the messages continue. What am I doing wrong? [ slightly more awake now ... ] Actually, PANIC is already the default value of log_min_error_statement. I think what you want is log_min_messages. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] set AUTOCOMMIT OFF in psqlrc not having affect
Thanks Tom, it appears that was the issue. Bruce, it would appear it was at some point? Glad to hear it's now insensitive. On Sun, 17 Oct 2004, Bruce Momjian wrote: FYI, it is not case-sensitive in current CVS. Tom Lane wrote: Christian Fowler [EMAIL PROTECTED] writes: I have \set AUTOCOMMIT OFF in my .psqlrc and it seems psql seems to not have any effect. I think the value is case-sensitive. Try \set AUTOCOMMIT off [ \ / [ X Christian Fowler | [EMAIL PROTECTED] [ / \ http://www.viovio.com | http://www.tikipro.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] Problem in starting postgres server on sun solaris 5.7
As root you will need to edit the /etc/system file to add some information for the OS about how much RAM to use for shared memory. These are settings for Informix (don't have a postgres SUN setup at hand and don't have doucumentation either): set shmsys:shminfo_shmmax=1205306368 set semsys:seminfo_semmap=256 set semsys:seminfo_semmni=4352 set semsys:seminfo_semmns=4352 set semsys:seminfo_semmnu=4096 set semsys:seminfo_semume=64 set semsys:seminfo_semmsl=100 set shmsys:shminfo_shmmin=100 set shmsys:shminfo_shmmni=356 set shmsys:shminfo_shmseg=4352 Postgres does not require all of these and uses shared memory in a somewhat different way so you should check the postgres documents for sysads and look for shared memory settings. By default none of this is enabled, i believe. You will probably need to reboot the server when you have edited the system file and then restart postgres. HTH, Greg Williamson DBA GlobeXplorer LLC -Original Message- From: Dattaram Shivji [mailto:[EMAIL PROTECTED] Sent: Sat 10/16/2004 1:27 AM To: [EMAIL PROTECTED] Cc: Subject:[ADMIN] Problem in starting postgres server on sun solaris 5.7 Hi! I installed postgresql-7.2.1 on Sun solaris 5.7 running on sparc processor. while installing i had no problems but as soon as start with following line /usr/local/pgsql/bin/postmaster -D /user2/pgsql/data or /usr/local/pgsql/bin/pg_ctl -D /user2/pgsql/data -l logfile start kailas# /usr/local/pgsql/bin/postmaster -D /user2/pgsql/data IpcMemoryCreate: shmget(key=5432001, size=1441792, 03600) failed: Invalid argument This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter. You can either reduce the request size or reconfigure the kernel with larger SHMMAX. To reduce the request size (currently 1441792 bytes), reduce PostgreSQL's shared_buffers parameter (currently 64) and/or its max_connections parameter (currently 32). If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for. The PostgreSQL Administrator's Guide contains more information about shared memory configuration. I am a new person with sun solaris so please sugest me in details i you can ! with regards Dattaram (Sys Admin) POD div. National Institute of Oceanography Donapaula -Goa India ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[ADMIN] Single table vacuum full different when vacuum full the whole database
Hi all, I'm experiencing different behaviour if a vacuum full a single table or the whole DB: SINGLE TABLE: # vacuum full verbose ua_user_data_exp; INFO: vacuuming public.ua_user_data_exp INFO: ua_user_data_exp: found 36 removable, 34519 nonremovable row versions in 22662 pages DETAIL: 82 dead row versions cannot be removed yet. Nonremovable row versions range from 444 to 924 bytes long. There were 289482 unused item pointers. Total free space (including removable row versions) is 164682076 bytes. 14385 pages are or will become empty, including 8 at the end of the table. 21687 pages containing 164397272 free bytes are potential move destinations. CPU 1.04s/0.23u sec elapsed 6.79 sec. INFO: index exp_id_provider now contains 34519 row versions in 2537 pages DETAIL: 36 index row versions were removed. 2260 index pages have been deleted, 2252 are currently reusable. CPU 0.16s/0.03u sec elapsed 2.71 sec. INFO: index exp_ci_login now contains 34519 row versions in 1401 pages DETAIL: 36 index row versions were removed. 103 index pages have been deleted, 103 are currently reusable. CPU 0.11s/0.05u sec elapsed 0.66 sec. INFO: index exp_country now contains 34519 row versions in 1855 pages DETAIL: 36 index row versions were removed. 1549 index pages have been deleted, 1541 are currently reusable. CPU 0.06s/0.05u sec elapsed 1.45 sec. INFO: index exp_os_type now contains 34519 row versions in 3015 pages DETAIL: 36 index row versions were removed. 2688 index pages have been deleted, 2672 are currently reusable. CPU 0.08s/0.07u sec elapsed 2.11 sec. INFO: index exp_card now contains 34519 row versions in 3119 pages DETAIL: 36 index row versions were removed. 2795 index pages have been deleted, 2783 are currently reusable. CPU 0.09s/0.07u sec elapsed 1.99 sec. INFO: index exp_status now contains 34519 row versions in 2647 pages DETAIL: 36 index row versions were removed. 2281 index pages have been deleted, 2259 are currently reusable. CPU 0.12s/0.06u sec elapsed 1.85 sec. INFO: index exp_email now contains 34519 row versions in 2320 pages DETAIL: 36 index row versions were removed. 1248 index pages have been deleted, 1242 are currently reusable. CPU 0.05s/0.03u sec elapsed 0.87 sec. INFO: index exp_ci_email now contains 34519 row versions in 2357 pages DETAIL: 36 index row versions were removed. 1279 index pages have been deleted, 1274 are currently reusable. CPU 0.12s/0.06u sec elapsed 0.86 sec. INFO: index exp_lastname now contains 34519 row versions in 1448 pages DETAIL: 36 index row versions were removed. 502 index pages have been deleted, 500 are currently reusable. CPU 0.07s/0.03u sec elapsed 0.57 sec. INFO: index exp_ci_lastname now contains 34519 row versions in 1444 pages DETAIL: 36 index row versions were removed. 501 index pages have been deleted, 499 are currently reusable. CPU 0.05s/0.07u sec elapsed 0.55 sec. INFO: index exp_orbital_ptns now contains 34519 row versions in 3001 pages DETAIL: 36 index row versions were removed. 2668 index pages have been deleted, 2651 are currently reusable. CPU 0.13s/0.02u sec elapsed 1.65 sec. INFO: index exp_stickers now contains 34519 row versions in 2980 pages DETAIL: 36 index row versions were removed. 2616 index pages have been deleted, 2606 are currently reusable. CPU 0.07s/0.04u sec elapsed 1.63 sec. INFO: index exp_pid now contains 34519 row versions in 2169 pages DETAIL: 36 index row versions were removed. 1930 index pages have been deleted, 1917 are currently reusable. CPU 0.11s/0.03u sec elapsed 1.29 sec. INFO: index exp_mac_address now contains 34519 row versions in 2012 pages DETAIL: 36 index row versions were removed. 413 index pages have been deleted, 413 are currently reusable. CPU 0.01s/0.08u sec elapsed 0.28 sec. INFO: index exp_mac_address_normal now contains 34519 row versions in 2014 pages DETAIL: 36 index row versions were removed. 421 index pages have been deleted, 421 are currently reusable. CPU 0.05s/0.04u sec elapsed 0.33 sec. INFO: index ua_user_data_exp_id_user_key now contains 34519 row versions in 886 pages DETAIL: 36 index row versions were removed. 3 index pages have been deleted, 3 are currently reusable. CPU 0.02s/0.02u sec elapsed 0.12 sec. INFO: index ua_user_data_exp_login_key now contains 34519 row versions in 1432 pages DETAIL: 36 index row versions were removed. 122 index pages have been deleted, 122 are currently reusable. CPU 0.05s/0.05u sec elapsed 0.17 sec. INFO: ua_user_data_exp: moved 1362 row versions, truncated 22662 to 17980 pages DETAIL: CPU 1.39s/2.30u sec elapsed 39.48 sec. INFO: index exp_id_provider now contains 34519 row versions in 2537 pages DETAIL: 1362 index row versions were removed. 2328 index pages have been deleted, 2320 are currently reusable. CPU 0.05s/0.01u sec elapsed 4.49 sec. INFO: index exp_ci_login now contains 34519 row versions in 1401 pages DETAIL: 1362 index row versions were removed. 103 index pages have been deleted, 103 are currently reusable. CPU
Re: [ADMIN] Single table vacuum full different when vacuum full the whole database
Gaetano Mendola [EMAIL PROTECTED] writes: it seems that a vacuum full on the whole DB is more aggressive. It is not. A much more plausible theory is that this is the result of concurrent changes to the table. It is clear from the dead row versions stats that there were concurrent transactions ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Single table vacuum full different when vacuum full the whole
Tom Lane wrote: Gaetano Mendola [EMAIL PROTECTED] writes: it seems that a vacuum full on the whole DB is more aggressive. It is not. A much more plausible theory is that this is the result of concurrent changes to the table. It is clear from the dead row versions stats that there were concurrent transactions ... That is the more updated/inserted table, and yes there were some concurrent transaction but, is it plausible that 82 dead rows were responsible of grab 26000 index row: INFO: index ua_user_data_exp_id_user_key now contains 34438 row versions in 886 pages DETAIL: 27488 index row versions were removed. instead of: INFO: index ua_user_data_exp_id_user_key now contains 34519 row versions in 886 pages DETAIL: 1362 index row versions were removed. Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] Suppressing duplicate key error messages in
At 05:24 PM 10/17/2004, Tom Lane wrote: Larry Lennhoff [EMAIL PROTECTED] writes: Is there any way to suppress this message so that it won't appear in the log? I looked at the documentation and log_min_error_statement looked promising. I set it to panic, but the messages continue. What am I doing wrong? [ slightly more awake now ... ] Actually, PANIC is already the default value of log_min_error_statement. I think what you want is log_min_messages. Hi In release 7.3 log_min_messages is called server_min_messages. and setting that to log and then doing a pg_ctl restart cleared the problem right up. Thanks for your help Larry ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] Longest name for table/column/key/index
Hi all, Are there any limits to the length of the names of tables, columns, primary keys and indexes in PostgreSQL? Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(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: [ADMIN] Longest name for table/column/key/index
Joost Kraaijeveld wrote: Hi all, Are there any limits to the length of the names of tables, columns, primary keys and indexes in PostgreSQL? 63 characters by default. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
