Re: [BUGS] BUG #8327: a bug of spgist index in a heavy write condition
My dear Tom Lane pgsql-hackers Thanks very very much! I have modified the postgres source code that Tom lane fixed, and the origin phenomenon of that bug no longer appears in my old test case. Thanks again! Best Regards! at 2013-07-24 19:08, Andres Freund wrote: Hi, On 2013-07-24 10:54:07 +, hailong...@qunar.commailto:hailong...@qunar.com wrote: The following bug has been logged on the website: Bug reference: 8327 Logged by: hailong.li Email address: hailong...@qunar.commailto:hailong...@qunar.com PostgreSQL version: 9.2.4 Operating system: CentOS release 5.5 (Final) Description: http://www.postgresql.org/message-id/519a5917.40...@qunar.com There's a thread that discussed the issue on the -hackers list: http://archives.postgresql.org/message-id/5829.1370555205%40sss.pgh.pa.us This resulted in a fix: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=e472b921406407794bab911c64655b8b82375196 Which has also been backported to the 9.2 branch. There hasn't been a release since the commit though. Greetings, Andres Freund
[BUGS] Reltuples/n_live_tup values wrong
Hi, I'm facing a problem which seems like a bug to me. I'm running: centos 5.9 postgresql92.x86_64 (9.2.4-1PGDG.rhel5) I have a table whith many inserts/deletes select * from pg_stat_user_tables where schemaname = 'lobby' and relname = 'lobby_player' order by n_live_tup desc; relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count ---++--+--+--+--+---+---+---+---+---+++---+-- -+---+---+--+--+---+--- 16594 | lobby | lobby_player | 231335 | 7156825 | 3726165 | 3334044 | 277457 | 124 | 277380 | 118 | 608965 | 187 | 2013-08-07 11:38:08.877226+02 | 2013- 08-07 10:48:16.282828+02 | 2013-08-07 11:17:50.551531+02 | 2013-08-06 19:48:41.649425+02 | 8 | 25 | 4 | 24 (1 row) select reltuples from pg_class where relname = 'lobby_player'; reltuples --- 608985 (1 row) But my Problem is the following: select count(*) from lobby_player; count --- 10 (1 row) If I do an analyze, the stats change: select relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum from pg_stat_user_tables where schemaname = 'lobby' and relname = 'lobby_player' order by n_live_tup desc; relname | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum --+++---+--- lobby_player | 15 | 609186 | 2013-08-07 11:38:08.877226+02 | 2013-08-07 10:48:16.282828+02 (1 row) Reltuples stays at the 600k value, after another vacuum the tuples are live again. I already did a reindex table, dropped the index+pkey, vacuum full but it doesn't change anything. Another thing I see is: \dt+ lobby_player List of relations Schema | Name | Type | Owner | Size | Description +--+---+---+---+- lobby | lobby_player | table | adm | 46 MB | (1 row) select oid from pg_class where relname = 'lobby_player'; oid --- 16594 (1 row) postgres@database:~ # ls -alh 9.2/data/base/*/16594* -rw--- 1 postgres postgres 0 Aug 7 11:34 9.2/data/base/16384/16594 So the 46MB are not there vacuum ANALYZE VERBOSE lobby_player; INFO: vacuuming lobby.lobby_player INFO: index lobby_player_pkey now contains 609319 row versions in 2348 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 8.03 sec. INFO: index idx_lobby_player_gtype_player_type now contains 609319 row versions in 1674 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 1.00 sec. INFO: lobby_player: found 0 removable, 609319 nonremovable row versions in 5937 out of 5937 pages DETAIL: 609299 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 16.18 sec. INFO: vacuuming pg_toast.pg_toast_16594 INFO: index pg_toast_16594_index now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: pg_toast_16594: found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing lobby.lobby_player INFO: lobby_player: scanned 5937 of 5937 pages, containing 21 live rows and 609302 dead rows; 21 rows in sample, 21 estimated total rows VACUUM Thanks for any help! -Sebastian -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Reltuples/n_live_tup values wrong
Sebastian Kornehl webmas...@sourcebase.org writes: I'm facing a problem which seems like a bug to me. I'm running: It seems like most of your problem is explained by this: DETAIL: 609299 dead row versions cannot be removed yet. You need to get rid of whatever old open transaction is preventing those rows from getting vacuumed away. Perhaps you have a prepared transaction lying around? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Reltuples/n_live_tup values wrong
Hi Tom, thanks for your Reply! You might be right, I found some pg_prepared_xacts from 2013-05-23. This was a test with a XA using application, but the application is already offline. There is also no pid available for the given transaction id's. Do you have any idea how to close these xa transactions without restarting the whole db? Thank you. -Sebastian On 08/07/2013 02:12 PM, Tom Lane wrote: Sebastian Kornehl webmas...@sourcebase.org writes: I'm facing a problem which seems like a bug to me. I'm running: It seems like most of your problem is explained by this: DETAIL: 609299 dead row versions cannot be removed yet. You need to get rid of whatever old open transaction is preventing those rows from getting vacuumed away. Perhaps you have a prepared transaction lying around? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Reltuples/n_live_tup values wrong
On Wed, Aug 7, 2013 at 1:50 PM, Sebastian Kornehl webmas...@sourcebase.orgwrote: Do you have any idea how to close these xa transactions without restarting the whole db? Restarting the database wouldn't accomplish anything. Prepared transactions are persistent across reboots. http://www.postgresql.org/docs/9.3/static/sql-rollback-prepared.html -- greg
Re: [BUGS] Reltuples/n_live_tup values wrong
Sebastian Kornehl webmas...@sourcebase.org writes: You might be right, I found some pg_prepared_xacts from 2013-05-23. This was a test with a XA using application, but the application is already offline. There is also no pid available for the given transaction id's. Do you have any idea how to close these xa transactions without restarting the whole db? Just use ROLLBACK PREPARED with the ID you see in pg_prepared_xacts. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Reltuples/n_live_tup values wrong
Thank you, that did it! On 08/07/2013 03:30 PM, Tom Lane wrote: Sebastian Kornehl webmas...@sourcebase.org writes: You might be right, I found some pg_prepared_xacts from 2013-05-23. This was a test with a XA using application, but the application is already offline. There is also no pid available for the given transaction id's. Do you have any idea how to close these xa transactions without restarting the whole db? Just use ROLLBACK PREPARED with the ID you see in pg_prepared_xacts. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #8373: can create database with long name , but can't connect
The following bug has been logged on the website: Bug reference: 8373 Logged by: Gerrit Geurs Email address: gerrit.ge...@hp.com PostgreSQL version: 9.2.4 Operating system: RHEL 6.4 Description: template1=# CREATE DATABASE vergunnigen_sit; CREATE DATABASE template1=# GRANT ALL PRIVILEGES ON DATABASE vergunnigen_sit to postgres; GRANT template1=# \q -bash-4.1$ psql vergunningen_sit psql: FATAL: database vergunningen_sit does not exist When I do a \LIST, THE LONG NAME IS LISTED. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8373: can create database with long name , but can't connect
On Wed, Aug 7, 2013 at 8:14 PM, gerrit.ge...@hp.com wrote: The following bug has been logged on the website: Bug reference: 8373 Logged by: Gerrit Geurs Email address: gerrit.ge...@hp.com PostgreSQL version: 9.2.4 Operating system: RHEL 6.4 Description: template1=# CREATE DATABASE vergunnigen_sit; CREATE DATABASE template1=# GRANT ALL PRIVILEGES ON DATABASE vergunnigen_sit to postgres; GRANT template1=# \q -bash-4.1$ psql vergunningen_sit psql: FATAL: database vergunningen_sit does not exist You misspelled the database name. Please check. vergunningen_sit - What you are connecting vergunnigen_sit - What you created Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee
Re: [BUGS] BUG #8373: can create database with long name , but can't connect
gerrit.ge...@hp.com wrote: template1=# CREATE DATABASE vergunnigen_sit; CREATE DATABASE template1=# GRANT ALL PRIVILEGES ON DATABASE vergunnigen_sit to postgres; GRANT template1=# \q -bash-4.1$ psql vergunningen_sit psql: FATAL: database vergunningen_sit does not exist When I do a \LIST, THE LONG NAME IS LISTED. I think you have misdiagnosed the problem. Maybe your psql is trying to connect to another server, not the one on which you just created the database. It certainly works for me: alvherre=# create database you think that's a long database name, huh?; CREATE DATABASE alvherre=# \q $ psql -d you think that's a long database name, huh? psql (9.2.4) Digite «help» para obtener ayuda. you think that's a long database name, huh?=# select current_database(); -[ RECORD 1 ]+ current_database | you think that's a long database name, huh? You can use up to 63 bytes in a database name (or any other identifier, for that matter). -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8335: trim() un-document behaviour
On Fri, Jul 26, 2013 at 02:23:10AM +, am...@amutu.com wrote:
in the postgresql doc 9.4,I find the trim() function like this:
trim([leading | trailing | both] [characters] from string)
so the trim should be pass only one argument with some optional prefix ---
but I
find the following calls with two argument is successfull but the results is
unexpected and wired:
##first call
postgres=# select trim(trailing '/, 'fasd/');
rtrim
--
(1 row)
-!!!note: it return titile is rtrim
## second call
postgres=# select trim('/', 'fasd/')
;
btrim
-
(1 row)
-!!!note: it return titile is btrim
it seems trim is transform to rtrim internal but the above call should
return error or it may produce un-expect results
(I have cleaned up this posting because single-quotes were converted to
Unicode forward-backward quotes):
What is happening is that TRIM() is converted by the parser to calls to
base functions, e.g.
\df *trim*
List of functions
Schema | Name | Result data type | Argument data types | Type
+---+--+-+
pg_catalog | btrim | bytea| bytea, bytea| normal
pg_catalog | btrim | text | text| normal
pg_catalog | btrim | text | text, text | normal
pg_catalog | ltrim | text | text| normal
pg_catalog | ltrim | text | text, text | normal
pg_catalog | rtrim | text | text| normal
pg_catalog | rtrim | text | text, text | normal
That is why the headings don't say 'trim', but 'btrim', or similar ---
not sure we can easily improve that, and you can change the label with
AS.
The larger problem is the use of ',' instead of FROM, and the backwards
interpretation of the arguments. The query:
SELECT trim('/' FROM 'fasd/')
is internally converted to:
SELECT btrim('fasd/', '/')
Note the arguments are reversed. The comma syntax does not reverse the
arguments:
SELECT trim('/', 'fasd/')
is internally converted to:
SELECT btrim('/', 'fasd/')
You can even use modifiers like TRAILING with comma syntax:
SELECT trim(TRAILING '/', 'fasd/');
and that uses 'rtrim', but of course the behavior is still reverse of
expected.
Basically the odd comma behavior is because without a FROM, the
arguments are passed directly to btrim/rtrim/ltrim, and these functions
take the origin string first, then the string of characters to remove.
You are right this is undocumented.
The attached patch swaps the arguments in the parser, and allows your
expected behavior:
SELECT trim('x', 'xfasdx');
btrim
---
fasd
Another option would be to change the C API for the b/r/ltrim functions,
or disallow the use of the comma TRIM syntax in the parser.
I am a little worried people might be relying on the trim/comma syntax
somewhere.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
new file mode 100644
index 22e82ba..8419559
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*** substr_for: FOR a_expr{ $$ = $2;
*** 11993,11999
trim_list: a_expr FROM expr_list { $$ = lappend($3, $1); }
| FROM expr_list { $$ = $2; }
! | expr_list{ $$ = $1; }
;
in_expr: select_with_parens
--- 11993,12000
trim_list: a_expr FROM expr_list { $$ = lappend($3, $1); }
| FROM expr_list { $$ = $2; }
! | a_expr ',' a_expr { $$ = list_make2($3, $1); }
! | a_expr{ $$ = list_make1($1); }
;
in_expr: select_with_parens
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
