[BUGS] BUG #5328: GIN index with fastupdates=on provide wrong result on bitmap scan
The following bug has been logged online:
Bug reference: 5328
Logged by: Maxim Boguk
Email address: maxim.bo...@gmail.com
PostgreSQL version: 8.4.1
Operating system: linux kernel v 2.6.18
Description:GIN index with fastupdates=on provide wrong result on
bitmap scan
Details:
I cannot create self-containing test case because fresh loaded table from
dump don't have such effect.
Bug itself is returning some extra unrelated tuples from bitmap scan over
complex GIN index.
Bug not random server issue (same problem exists on the master database and
on both londiste slaves).
Here is query which show bug:
SELECT file_type_did FROM
file
WHERE
file.file_type_did='6903057'::bigint
AND file.obj_status_did = 1
AND (file.obj_tsvector @@ (make_tsquery('(море)',
'utf8_russian')))
offset 0
) AS t
where t.file_type_did'6903057'::bigint;
---
6906314
6902260
6906314
6906314
6902260
6902260
...
6902260
6906314
(83 rows)
E.g. inner query returned 83 rows with complete unrelated file_type_did
Now let me show explain of an inner query:
db=# EXPLAIN SELECT file_type_did FROM
db-# file
db-# WHERE
db-# file.file_type_did='6903057'::bigint
db-# AND file.obj_status_did = 1
db-# AND (file.obj_tsvector @@ (make_tsquery('(море)',
'utf8_russian')))
db-# offset 0;
QUERY PLAN
--
Limit (cost=58.47..722.08 rows=5253 width=8)
- Bitmap Heap Scan on file (cost=58.47..722.08 rows=5253 width=8)
Recheck Cond: ((file_type_did = 6903057::bigint) AND
(obj_tsvector @@ '''море'''::tsquery) AND (obj_status_did = 1))
- Bitmap Index Scan on i_file__photo_search (cost=0.00..57.16
rows=5253 width=0)
Index Cond: ((file_type_did = 6903057::bigint) AND
(obj_tsvector @@ '''море'''::tsquery))
Description of used GIN index:
i_file__photo_search gin (file_type_did, photo_vislvl, obj_tsvector) WHERE
obj_status_did = 1
where is related table fields is:
file_type_did | bigint | not null
photo_vislvl | smallint | not null default 9
obj_tsvector | tsvector | not null default
''::tsvector
Additional information:
1)doing reindex index i_file__photo_search; remove an problem for a short
time (1-60 minutes depend user=update activity on the table).
2)VACUUM files; have the same effect for the same time.
3)8.3 server don't have such issues
My idea is bug related to GIN fastupdate engine which was implemented in 8.4
version.
I will do ALTER INDEX i_file__photo_search set (fastupdate=off); and VACUUM
file; on one of slaves and check results in next 24 hours to confirm or
contradict my idea.
--
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 #5328: GIN index with fastupdates=on provide wrong result on bitmap scan
Maxim Boguk maxim.bo...@gmail.com writes: Bug reference: 5328 Logged by: Maxim Boguk Email address: maxim.bo...@gmail.com PostgreSQL version: 8.4.1 Operating system: linux kernel v 2.6.18 Description:GIN index with fastupdates=on provide wrong result on bitmap scan The 8.4.2 release notes mention * Fix wrong search results for a multi-column GIN index with fastupdate enabled (Teodor) which seems to match your complaint. Please update and see if the problem is resolved. You might need to reindex the GIN index after updating to make things good. 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] BUG #5327: postgresql gives checksum error when upgrading 8.2.6 binaries to 8.2.14 in windows.
The short answer is that you should not be changing any build configurations if changing from one minor release to another. Are you sure you are using the same build setup? --- janandith wrote: The following bug has been logged online: Bug reference: 5327 Logged by: janandith Email address: janand...@gmail.com PostgreSQL version: 8.2.14 Operating system: windows 2003 Description:postgresql gives checksum error when upgrading 8.2.6 binaries to 8.2.14 in windows. Details: After upgrading postgresql binaries from 8.2.6 to 8.2.14 in windows 2003 I cannot start the database using postgresql.exe because there is a checksum error. What I'm doing is building postgresql 8.2.14 from source code and replacing the binaries. In windows I'm building using MSYS and replacing existing .exe files. After the replacement Linux binaries are working without errors. But the windows binaries reads the Date/time type storage: and Locale wrong. It should be be floating-point and C locale but read as 64-bit integers and Locale as @. Following are the results of pg_controldata. Linux pg_control version number:822 Catalog version number: 200611241 Database system identifier: 5219167662867643742 Database cluster state: in production pg_control last modified: Fri 12 Feb 2010 03:47:00 PM IST Current log file ID: 0 Next log file segment:63 Latest checkpoint location: 0/3EA20688 Prior checkpoint location:0/3E9F1084 Latest checkpoint's REDO location:0/3EA20688 Latest checkpoint's UNDO location:0/0 Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 0/547350 Latest checkpoint's NextOID: 26232 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Time of latest checkpoint:Fri 12 Feb 2010 03:47:00 PM IST Minimum recovery ending location: 0/0 Maximum data alignment: 4 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment:16777216 Maximum length of identifiers:64 Maximum columns in an index: 32 Date/time type storage: floating-point numbers Maximum length of locale name:128 LC_COLLATE: C LC_CTYPE: C Windows WARNING: Calculated CRC checksum does not match value stored in file. Either the file is corrupt, or it has a different layout than this program is expecting. The results below are untrustworthy. pg_control version number:822 Catalog version number: 200611241 Database system identifier: 5247545044375645930 Database cluster state: shut down pg_control last modified: 01.01.1970 01:00:00 Current log file ID: 1265884073 Next log file segment:0 Latest checkpoint location: 0/30 Prior checkpoint location:0/2F7CA1A8 Latest checkpoint's REDO location:0/2F6F4E60 Latest checkpoint's UNDO location:0/2F7CA1A8 Latest checkpoint's TimeLineID: 0 Latest checkpoint's NextXID: 0/1 Latest checkpoint's NextOID: 0 Latest checkpoint's NextMultiXactId: 173924 Latest checkpoint's NextMultiOffset: 17725 Time of latest checkpoint:01.01.1970 01:00:01 Minimum recovery ending location: 0/4B73DBA9 Maximum data alignment: 0 Database block size: 8 Blocks per segment of large relation: 0 WAL block size: 0 Bytes per WAL segment:1093850759 Maximum length of identifiers:8192 Maximum columns in an index: 131072 Date/time type storage: 64-bit integers Maximum length of locale name:16777216 LC_COLLATE: @ LC_CTYPE: -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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 #5329: postmaster fails to start because of an erroneous pg_hba.conf, but doesn't output any error
The following bug has been logged online: Bug reference: 5329 Logged by: Olivier Jeannet Email address: olivier.jean...@gmail.com PostgreSQL version: 8.4.2 Operating system: Fedora 12 on x86_64 Description:postmaster fails to start because of an erroneous pg_hba.conf, but doesn't output any error Details: After having modified pg_hba.conf in bad way, with the following line (the IP address is not used and not wanted) : local all all 127.0.0.1md5 postmaster was unable to start as usual. I had a hard time finding the problem, as nothing is ouput on the command line or in the logs. I suggest to print something when halting on a configuration file parsing problem. Best regards, Olivier Jeannet. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Postgresql Relations disappear
Hello, good morning! I started to work with postgresql but i find that all the relations I create disappear when I quit (\q) from the interactive terminal or after load a sql file. I tried to find something about this bug but I haven't found anything about that, I would really appreciate if you could tell me why this is happening. I'm triying to load my database scheme from a sql file (psql -f myscheme.sql mydb or, from interactive terminal: \i my_scheme.sql) but the only thing I get is No relations found.. I also tried installing several versions of postgresql 8.1, 8.2, etc under Snow Leopard, ubuntu and debian and every time I get the same answer: No relations found. -- Sergio Jhovanne Domínguez González Mat. a0213100H Maestría en Ciencias en Ingeniería Eléctrica, División de Estudios de Posgrado, FIE, U.M.S.N.H.
Re: [BUGS] Postgresql Relations disappear
sergio dominguez escreveu: I started to work with postgresql but i find that all the relations I create disappear when I quit (\q) This is not a bug. I bet your tables are loaded into a schema that is not in the search_path. -- Euler Taveira de Oliveira http://www.timbira.com/ -- 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 #5329: postmaster fails to start because of an erroneous pg_hba.conf, but doesn't output any error
Olivier Jeannet olivier.jean...@gmail.com writes: After having modified pg_hba.conf in bad way, with the following line (the IP address is not used and not wanted) : local all all 127.0.0.1md5 postmaster was unable to start as usual. I had a hard time finding the problem, as nothing is ouput on the command line or in the logs. I suggest to print something when halting on a configuration file parsing problem. We most certainly do print something --- in your example I get this: LOG: invalid authentication method 127.0.0.1 CONTEXT: line 81 of configuration file /home/postgres/data/pg_hba.conf FATAL: could not load pg_hba.conf I suspect you are using a configuration that redirects the postmaster's log to /dev/null, or some equally unhelpful place :-(. FWIW, this message comes out after the logging settings in postgresql.conf have been adopted, so you should look to those to see where the message actually went. Errors in postgresql.conf itself typically get reported on postmaster's stderr. 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] Postgresql Relations disappear
sergio dominguez dgsergi...@gmail.com writes: I started to work with postgresql but i find that all the relations I create disappear when I quit (\q) from the interactive terminal or after load a sql file. It's difficult to say for sure when you haven't showed us exactly what you did, but one possibility is that you created the relations inside a transaction and then forgot to commit the transaction before quitting. Or perhaps you're creating them in a different database or different schema than where you're subsequently looking. Note that \d without any parameter will only show relations that are in your current search_path. 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] BUG #5329: postmaster fails to start because of an erroneous pg_hba.conf, but doesn't output any error
Olivier Jeannet escreveu: I had a hard time finding the problem, as nothing is ouput on the command line or in the logs. Ugh? I got this in 8.4 or earlier version. Are you checking the right log file? LOG: invalid authentication method 127.0.0.1/32 CONTEXT: line 74 of configuration file /a/data/pg_hba.conf FATAL: could not load pg_hba.conf -- Euler Taveira de Oliveira http://www.timbira.com/ -- 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 #5326: The 2nd update of a table which has foreign keys is blocked.
Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp writes: But to be exact, the comment says we *can* still skip the checks if we don't have any deferred FKs, right? If so, can we add a has_deferred_FKs() check to the condition? if ((!has_deferred_FKs(rel) || !TransactionIdIsCurrentTransactionId(...)) RI_FKey_keyequal_upd_fk(...) No. That wouldn't fix this problem (since the OP hasn't got any deferred triggers) and it would break the test we need to make (since the deferred FK isn't necessarily *this* FK, and even if it was, what you propose would prevent it from being checked). If we knew that the just-outdated tuple had been created by an update that didn't change the FK columns, then we could skip applying the check at the new update. But we don't know that, and I don't see any real easy way to shoehorn in the knowledge. We don't have any extra per-tuple state here --- and per-tuple state wouldn't be good enough anyway, if there are multiple FKs. Another possibility is to chain back to the latest tuple version that existed prior to this transaction and compare FK columns against that version ... except we have no good way to do that either; the t_ctid links point the wrong way. AFAICS there is no simple way to improve this. It's an optimization that the first update didn't block. We can't easily extend that optimization to the second update. Sorry. 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
