Re: [BUGS] BUG #6325: Useless Index updates
Thank you all for your explanations. I will continue this on pgsql-general. Daniel Migowski Von: Craig Ringer [ring...@ringerc.id.au] Gesendet: Sonntag, 4. Dezember 2011 15:02 Bis: Daniel Migowski Cc: pgsql-bugs@postgresql.org Betreff: Re: [BUGS] BUG #6325: Useless Index updates On 12/04/2011 08:54 PM, dmigow...@ikoffice.de wrote: > The following bug has been logged on the website: > > Bug reference: 6325 > Logged by: Daniel Migowski > Email address: dmigow...@ikoffice.de > PostgreSQL version: 8.3.16 > Operating system: Linux > Description: > > It seems that an update to a row in a table always removes the element from > an index and adds it again. Wouldn't it be faster to check for equality of > the index parameters in the OLD and NEW record first? - This isn't a bug report, it's a feature/enhancement request. Please use the mailing lists. - You're reporting this issue against an old patch release of an old major release. Why not check with 9.1? - The index isn't always updated. Check out HOT (introduced in 8.4, the release after your current one) which reduces unnecessary index updates in cases where the old and new row can fit on the same heap page. - In most other cases the index update can't be avoided, because the new and old rows are on different database pages. The old index entry has to remain in place so that still-running transactions that can see the old row can still find it in the index, so it can't be overwritten and instead a new entry has to be added. > I have this problem with an functional index using a relative expensive > index function, and noticed that the index function is always called even if > the parameter to the index function has not changed. Wouldn't it be better > to validate that the input to the index functions has not changed, instead > of calling the index function over and over again? Especially since the > index functions seems to be called with the new and the old value anyway. That's a more interesting one. Perhaps you could write it up in more detail, with a test case, and submit it to the pgsql-general mailing list? This isn't just about functions anyway. Pg would have to compare *all* inputs to the old index expression to see if they were the same. Otherwise, in an expression like f(g(x,y),z) Pg would not have any stored value for the result of g(x,y) to compare against. It'd have to instead compare (x1,y1,z1) to (x2,y2,z2) and decide that if they were the same the result of the index expression hadn't changed. That's probably possible, but I'm not sure it'd be a win over just evaluating the expression in most cases. How would Pg know when to do it? Using function COST parameters? Essentially, this isn't as simple as it looks at face value. > I can understand that this might be a precaution in the case that the index > function isn't stable (is it even possible to use such a function for an > index?) No, it isn't possible. Index functions must be immutable, not just stable, so their output must be determined entirely by their parameters. At least on newer versions STABLE or VOLATILE functions should be rejected in index expressions. -- Craig Ringer -- 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 #6327: Prefix full-text-search fails for hosts with complicated names
On Mon, 5 Dec 2011, Tom Lane wrote:
marcin.kasper...@mekk.waw.pl writes:
Synopsis
=
'goog:*' matches google.com
but
'e-goog:*' does not match e-google.com
The reason for this seems to be that the pattern is treated as a
hyphenated word:
regression=# select TO_TSQUERY('english', 'e-goog:*');
to_tsquery
---
'e-goog':* & 'e':* & 'goog':*
(1 row)
but the hostname isn't:
regression=# select TO_TSVECTOR('english', 'See e-google.com');
to_tsvector
--
'e-google.com':2 'see':1
(1 row)
If you change the text so it's not recognized as a hostname, you get
lexemes that would match the query:
regression=# select TO_TSVECTOR('english', 'See e-google com');
to_tsvector
-
'com':5 'e':3 'e-googl':2 'googl':4 'see':1
(1 row)
Possibly we could fix this by hacking the ts parser so that it would
also apply the hyphenated-word rules to a hostname containing a dash.
In general though, there are always going to be cases where prefix
match doesn't work because of dictionary transformations ...
I'd index 'after dictionary transformations' lexemes as well as an
original to let prefix march always work.
regards, tom lane
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
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 #6328: Wrong error message for insert-sql
The following bug has been logged on the website:
Bug reference: 6328
Logged by: Rigoletto Eikenberg
Email address: eikenb...@golze.de
PostgreSQL version: 9.0.0
Operating system: windows server 2008 R2
Description:
The sql
INSERT INTO auftragpos (orderno,position,item) VALUES
('testorder18',1,00123);
works fine,
but
INSERT INTO auftragpos (orderno,position,item) VALUES
(testorder18,1,00123);
brings error:
FEHLER: Spalte »testorder18« existiert nicht (Column »testorder18« not
exists)
LINE 1: ...T INTO auftragpos (orderno,position,item) VALUES (testorder1...
But »testorder18« is a value, not a column-name!?!?
If there is a problem it should bring a message that the value for column
"orderno" has the wrong format.
Regards
Rigoletto
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] copy cmd err
Helo
After running below command am getting error
had run this command before but now am not able to run it
any suggestion ?
Server Ver : psql (PostgreSQL) 8.1.11
OS Platform : Red Hat Enterprise Linux Server release 5.3 (Tikanga)
COPY (SELECT * from function_name (2, 0, '-mm-dd','-mm-dd')) TO
STDOUT
ERROR: syntax error at or near "(" at character 6
LINE 1: COPY (SELECT * from function_name (2, 0, '2011-08-29','2011...
Thanx
--
☺ııııllıı ✉ j!ℓℯñ ✉ lıııll☺
Re: [BUGS] BUG #6328: Wrong error message for insert-sql
wrote: >INSERT INTO auftragpos (orderno,position,item) VALUES > (testorder18,1,00123); > brings error: > FEHLER: Spalte *testorder18* existiert nicht (Column > *testorder18* not exists) > LINE 1: ...T INTO auftragpos (orderno,position,item) VALUES > (testorder1... > > But *testorder18* is a value, not a column-name!?!? > If there is a problem it should bring a message that the value for > column "orderno" has the wrong format. 'testorder18' is a literal value. Without the apostrophe quoting it is taken as an identifier, such as a column. It is entirely correct and appropriate for an error to be generated. The wording of the message could perhaps be adjusted to help the user understand their mistake more easily, since there are no columns in context from any table here; but the message is not actually wrong. Certain reserved words (such as CURRENT_DATE) would be allowed here, as would expressions of arbitrary complexity -- sub-selects, CASE predicates, functions, etc.; it would take a bit of work to sort out when something might be a malformed attempt at a literal versus a misspelled column name in a subquery. -Kevin -- 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] copy cmd err
Jivendra writes: > Server Ver : psql (PostgreSQL) 8.1.11 > OS Platform : Red Hat Enterprise Linux Server release 5.3 (Tikanga) > COPY (SELECT * from function_name (2, 0, '-mm-dd','-mm-dd')) TO > STDOUT That COPY syntax doesn't exist in PG 8.1 --- the option to have a parenthesized query as the source was added in 8.2. Even within the RHEL5 world, 8.1.11 is mighty obsolete. Red Hat is currently shipping 8.1.23, or you can get 8.4.x in the postgresql84 package set, or you could use Devrim's packages if you don't feel a need to be all-Red-Hat. 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 #6329: ODBC ANSI driver psqlODBC v09.00.0310.1
The following bug has been logged on the website: Bug reference: 6329 Logged by: Erik Boisen Email address: eboi...@hotmail.com PostgreSQL version: 9.0.3 Operating system: Windows XP Description: ODBC ANSI driver psqlODBC v09.00.0310.1 does not properly pass values when data is of type "INTERVAL" to Crystal Reports. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
