Re: [GENERAL] VACUUM and transactions in different databases
Hi Ragnar could not duplicate this. I also cannot reproduce the hanging VACUUM FULL. The problem remains thet the dead tuples cannot be vemoved. dummy1=# vacuum full; VACUUM dummy1=# SELECT relpages, reltuples FROM pg_class WHERE relname ='dummy1'; -[ RECORD 1 ]- relpages | 997 reltuples | 10 dummy1=# analyze verbose; ... INFO: analyzing "public.dummy1" INFO: "dummy1": scanned 997 of 997 pages, containing 5000 live rows and 95000 dead rows; 3000 rows in sample, 5000 estimated total rows ... dummy1=# select * from pg_stat_activity; -[ RECORD 1 ]-+-- datid | 21529 datname | dummy1 procpid | 2065 usesysid | 10 usename | postgres current_query | query_start | backend_start | 2006-12-07 21:03:54.89+01 client_addr | client_port | -1 -[ RECORD 2 ]-+-- datid | 21530 datname | dummy2 procpid | 2152 usesysid | 10 usename | postgres current_query | query_start | backend_start | 2006-12-07 21:07:59.973477+01 client_addr | client_port | -1 the transaction in db dummy2 performed an update and select count(*) and is still running. dummy1=# select * from pg_locks; -[ RECORD 1 ]-+- locktype | relation database | 21530 relation | 21540 page | tuple | transactionid | classid | objid | objsubid | transaction | 85385 pid | 2152 mode | AccessShareLock granted | t -[ RECORD 2 ]-+- locktype | relation database | 21530 relation | 21540 page | tuple | transactionid | classid | objid | objsubid | transaction | 85385 pid | 2152 mode | RowExclusiveLock granted | t -[ RECORD 3 ]-+- locktype | relation database | 21529 relation | 10342 page | tuple | transactionid | classid | objid | objsubid | transaction | 85925 pid | 2065 mode | AccessShareLock granted | t -[ RECORD 4 ]-+- locktype | transactionid database | relation | page | tuple | transactionid | 85925 classid | objid | objsubid | transaction | 85925 pid | 2065 mode | ExclusiveLock granted | t -[ RECORD 5 ]-+- locktype | transactionid database | relation | page | tuple | transactionid | 85385 classid | objid | objsubid | transaction | 85385 pid | 2152 mode | ExclusiveLock granted | t Thanks Conni ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] VACUUM and transactions in different databases
Hi Sorry, i was out The first try was: create database dummy1; create table dummy ... and filled with 500 records create database dummy2; create table dummy ... and filled with 500 records connecting to dummy1, opening a transaction and issued an update begin; update dummy set f1='achterbahn'; then opened a second console and connected to dummy2: dummy2=# select count(*) from dummy; count --- 5000 (1 row) dummy2=# SELECT relpages, reltuples FROM pg_class WHERE relname ='dummy'; relpages | reltuples --+--- 160 | 5000 (1 row) updated the table several times - to generate dead tuples: dummy2=# SELECT relpages, reltuples FROM pg_class WHERE relname ='dummy'; relpages | reltuples --+--- 326 | 3 (1 row) dummy2=# vacuum; VACUUM dummy2=# SELECT relpages, reltuples FROM pg_class WHERE relname ='dummy'; relpages | reltuples --+--- 326 | 3 (1 row) dummy2=# select count(*) from dummy; count --- 5000 (1 row) dummy2=# vacuum full; vacuum was in waiting state as long the transaction in dummy1 was opened. After committing the transaction the vacuum full was carried out. --- VACUUM dummy2=# SELECT relpages, reltuples FROM pg_class WHERE relname ='dummy'; relpages | reltuples --+--- 326 | 3 (1 row) running on pg 8.1.4 on Fedora 5 Thanks Conni ---(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
Re: [GENERAL] VACUUM and transactions in different databases
Hi as soon as I committed the open transaction the hangig vacuum full completed and the table was vacuumed: regards Conni ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] VACUUM and transactions in different databases
Hi Bill > I don't believe that's the reason. AFAIK, activity in one database > will never block activity in another. This way I read the documentation. > I would suspect that you haven't vacuumed this database in a long time, I created both databases one hour ago for just testing this behaviour. I started with two identical tables, each with 5000 rows in both databases. In db1 I opened a transaction, updated the table and left the transaction open. In db2 I updated, inserted and deleted a lot and then tried to vacuum. > Can you run a "vacuum > full", and does it reclaim the space? I tried but it hangs. [EMAIL PROTECTED] ~]# ps axw|grep postgres 1746 ?S 0:00 postgres: writer process 1747 ?S 0:00 postgres: stats buffer process 1748 ?S 0:00 postgres: stats collector process 2106 pts/1S 0:00 su postgres 2120 pts/1S+ 0:00 psql postgres 2188 ?S 0:04 postgres: postgres dummy1 [local] VACUUM waiting 2200 pts/3S 0:00 su postgres 2215 ?S 0:00 postgres: postgres dummy2 [local] idle in transaction 2717 pts/2R+ 0:00 grep postgres Regards Conni ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] VACUUM and transactions in different databases
Hi all If I have a running transaction in database1 and try to vacuum database2 but the dead tuples in database2 cannot be removed. INFO: vacuuming "public.dummy1" INFO: "dummy1": found 0 removable, 14 nonremovable row versions in 1341 pages DETAIL: 135000 dead row versions cannot be removed yet. How can I achieve that database2 is vacuumed while a transaction in database1 is not yet commited? Regards conni ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] zero values in statistic views
Hi Tom > But you didn't say what the ps output showed. ps aux | grep stats postgres 24979 0.0 0.0 24416 3780 ?SNov07 0:07 postgres: stats buffer process postgres 24980 0.0 0.0 24684 4024 ?SNov07 0:10 postgres: stats collector process Regards Conni ---(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
Re: [GENERAL] zero values in statistic views
Tom, thanks for the quick response. stats_row_level and stats_block_level are disabled in postgresql.conf. I used the SET command to activate them. > If it's not running, look in the postmaster log right after > restarting the postmaster; there may be a message there about why > not. I cannot restart the postmaster, it is a production server. Regards Conni ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] zero values in statistic views
Hi all I would like to see what is going on in a database using the statistic views. I am superuser, stats_start_collector = on stats_row_level = on stats_block_level = on select * from pg_stat_user_tables; select * from pg_stat_user_indexes; select * from pg_statio_user_tables select * from pg_statio_user_indexes; are showing zeroes in all columns where UPDATEs, INSERTs, block-reads, hits ... should be shown. select * from pg_stat_database where datname = 'mydb'; shows between 20 and 60 numbackends and from 11 to 50 commits. What could be the reason for this behaviour? What am I missing? Thanks in advance Conni ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Problem creating a function - solved
Hi Tom, Joe After compiling the C-source files # gcc -fpic -c bitvg.c # gcc -shared -o bitvg.so bitvg.o the pg_restore created the function. It is Suse 9.x and PG 8.1.4 Thanks Conni ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Problem creating a function
Hi Tom > You should try "ldd" or local equivalent on bitvg.so to check > whether all its dependencies can be found. output: ldd /home/database/pgdata/cobis/bitvg/bitvg.so statically linked Regards Conni ---(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
Re: [GENERAL] Problem creating a function
Hi Joe > Try: > su postgres > cd /home/database/pgdata/cobis/bitvg Succeeds, ls -la shows all files in that directory and can open them with less. Regards Conni ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Problem creating a function
Hi all Trying to create a function I get the following: codex=# CREATE FUNCTION "bitvg" (integer,integer) RETURNS integer AS '/home/database/pgdata/cobis/bitvg/bitvg.so', 'bitvg' LANGUAGE 'C'; ERROR: could not load library "/home/database/pgdata/cobis/bitvg/bitvg.so": /home/database/pgdata/cobis/bitvg/bitvg.so: Kann die Shared-Object-Datei nicht öffnen: Datei oder Verzeichnis nicht gefunden in english: /home/database/pgdata/cobis/bitvg/bitvg.so: cannot open shared object: file not found The file does exist in the path /home/database/pgdata/cobis/bitvg/: -rwxr-xr-x 1 root root 133 Jul 19 13:13 bitvg.c -rwxr-xr-x 1 root root 782 Jul 19 13:13 bitvg.o -rwxr-xr-x 1 root root 3198 Jul 19 13:13 bitvg.so What do I miss? Thank's in advance Conni -- http://pgsql.info | http://postgresql.de | http://pgfakt.de Telefon: 07127 80 961 ---(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
[GENERAL] Question on output of VACUUM VERBOSE
Hi all I don't understand what these two lines exactly mean. INFO: free space map: 490 relations, 13541 pages stored; 34480 total pages needed DETAIL: Allocated FSM size: 1000 relations + 2 pages = 178 kB shared memory Thanks in advance Conni ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Merry Christmas
Hi all, Merry Christmas and best wishes for the New Year. Conni ---(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: [GENERAL] Problem to connect to the Windows Port
Hi Magnus Thanks. > If you meant to match the entire subnet (192.168.*.*), instead put > host minitest postgres 192.168.0.0 255.255.0.0 password Ok. I changed the line in pg_hba.conf to host all all 192.168.0.0 255.255.0.0 password then rebooted the computer and tried again: C:\>psql -h 192.168.1.8 -U postgres -d minitest psql: could not connect to server: Connection refused Is the server running on host "192.168.1.8" and accepting TCP/IP connections on port 5432? The server on 192.168.1.8 is running, (I can connect on this machine to localhost/minitest.). I can ping 192.168.1.8 Regards Conni ---(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: [GENERAL] Problem with date calculations
Hi Stephan Thank you > d1 + dur * interval '1 month' works ;-) Regards Conni ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Problem with date calculations
Hi alltogether I have a table with two fields, d1 timestamp and dur smallint. d1 is the starting date and dur is the duration. From this two fields I want to generate future dates for the whole table. There is no problem with queries where a number for the duration is given. test=# select d1,dur,d1 + '6 month' from t1; d1 | dur | ?column? ---+-+- 2003-12-27 00:00:00 | 4 | 2004-06-27 00:00:00 2003-11-14 00:00:00 | 7 | 2004-05-14 00:00:00 2004-01-03 00:00:00 | 5 | 2004-07-03 00:00:00 I want to have the date plus the duration stored in the table, but didn't succeed. test=# select '\''||dur::varchar||' month\'' from t1; ?column? --- '4 month' '7 month' '5 month' but test=# select d1 + '\''||wielange::varchar||' month\'' from t1; ERROR: invalid input syntax for type interval: "'" Any hints are welcome Regards Conni ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Pgsql on Windows
Hi > When you have postgresql support compiled into PHP, then PHP connects > directly using native PostgreSQL calls (libpq), no ODBC layer to deal > with. Working with Windows you normally don't compile PHP but use the Win binaries. To connect to a PostgreSQL database you must uncomment the php_pgsql.dll in php.ini. Regards Conni ---(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
[GENERAL] What values does PQstatus return
Hi all What values does PQstatus return in case of CONNECTION_OK and CONNECTION_BAD? Thanks Conni ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Warning: PostgreSQL query failed
Hi Sreedhar
Try it with
...
catalogid) values ('KICKIN''BACK.SDS', 13803564 , ' OpenOffice.org 5.0
Chart
...
Use a second ' to escape ' instead of a backslash.
Greetings
Conni
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] converting PostgreSQ- timestamp to UNIX-timestamp ?
Hi everybody To convert PostgreSQL timestmp to UNIX timestamp I use select ((today::abstime)::int4) from table ... In the documentation there is a notice that abstime shouldn't be used. Is there another way to convert? Also I tried select timetz_hash(today) from table .. and got negative values for some timestamps. Does anybody know why? RedHat 7.3, PostgreSQL 7.2.1 Regards Conni ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Fatal error: Call to undefined function: pg_connect()
Hi did you configure php for PostgreSQL support ./configure --with-pgsql Regards Conni ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
