Re: Odd query result
Re: Odd query result
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Maximilian Tyrtania writes: >> It might be a broken index issue. Please show the EXPLAIN for these queries. > It was. I had an index like this: > CREATE INDEX idx_firmen_iskunde_index > ON firmen > USING btree > (f_firmen_iskunde(firmen.*)); > Sorry, should have mentioned that�Dropping and recreating it fixed it. Hm, had you changed the behavior of that function since creating the index? regards, tom lane
Re: Odd query result
От:
Frank Lanitz <frank@frank.uvena.de>
Дата:
On Mon, 27 Aug 2012 10:55:43 +0200 Maximilian Tyrtania wrote: > Hello from Berlin, > > I can't quite make sense of this (running PG 9.0.3): > > psql (9.0.3) > Type "help" for help. > > FAKDB=# Select _rowid,f_firmen_isKunde(firmen),bezeichnung::text from > firmen > where > (firmen.bezeichnung='Microsoft Deutschland GmbH'); _rowid | > f_firmen_iskunde | bezeichnung > ----------+------------------+---------------------------- > 1214700 | f | Microsoft Deutschland GmbH > 15779700 | t | Microsoft Deutschland GmbH > 166300 | t | Microsoft Deutschland GmbH > (3 rows) > > FAKDB=# Select _rowid,f_firmen_isKunde(firmen),bezeichnung::text from > FAKDB-# firmen where > FAKDB-# (firmen.bezeichnung='Microsoft Deutschland GmbH') and > FAKDB-# (f_firmen_isKunde(firmen)=true) and firmen._rowid=15779700 ; > _rowid | f_firmen_iskunde | bezeichnung > ----------+------------------+---------------------------- > 15779700 | t | Microsoft Deutschland GmbH > (1 row) > > Fine. But this record won't be found if I omit the last condition. > > FAKDB=# Select _rowid,f_firmen_isKunde(firmen),bezeichnung::text from > firmen > where > (firmen.bezeichnung='Microsoft Deutschland GmbH') and > (f_firmen_isKunde(firmen)=true); _rowid | f_firmen_iskunde | > bezeichnung > --------+------------------+---------------------------- > 166300 | t | Microsoft Deutschland GmbH > (1 row) > > > What might be up there? How is f_firmen_isKunde() defined? Cheers, Frank -- Frank Lanitz
Re: Odd query result
От:
Maximilian Tyrtania <maximilian.tyrtania@contactking.de>
Дата:
Am 27.08.2012 um 16:18 schrieb Tom Lane : > Maximilian Tyrtania writes: > >> Sorry, should have mentioned that…Dropping and recreating it fixed it. > > Hm, had you changed the behavior of that function since creating the > index? To be honest - I don't know for sure. I might well have. Maximilian Tyrtania Contact King Software Entwicklung Tel.: ++49/30/664040-544 http://www.contactking.de
Re: Odd query result
От:
Georges Racinet <gracinet@anybox.fr>
Дата:
On 08/27/2012 12:14 PM, Sergey Konoplev wrote: > On Mon, Aug 27, 2012 at 1:56 PM, Maximilian Tyrtania > wrote: >>> It might be a broken index issue. Please show the EXPLAIN for these queries. >> >> It was. I had an index like this: >> >> CREATE INDEX idx_firmen_iskunde_index >> ON firmen >> USING btree >> (f_firmen_iskunde(firmen.*)); >> >> Dropping and recreating it fixed it. > > Note that having such functional index you are risking to face similar > oddities again after you modify the function. You need to manually > reindex all the indexes that uses the function after it has been > modified. Hi, this is quite interesting, I didn't know it were possible to set an index on a function result. I guess one must also reindex in case some record changes as well, right ? Through a trigger maybe ? Thanx for the informative posts, -- Georges Racinet Anybox SAS, http://anybox.fr Bureau: 09 53 53 72 97 Portable: 06 51 32 07 27 GPG: 0x33AB0A35, sur serveurs publics
Re: Odd query result
От:
Georges Racinet <gracinet@anybox.fr>
Дата:
On 08/27/2012 12:47 PM, Sergey Konoplev wrote: > On Mon, Aug 27, 2012 at 2:37 PM, Georges Racinet wrote: >> I guess one must also reindex in case some record changes as well, right ? >> Through a trigger maybe ? > > You do not need to reindex it in case of record changes. The index > values will be recalculated automatically after committing the > changes. > I see, the fact that it's computed through a function is no exception to that. Thank you for the clarification, -- Georges Racinet Anybox SAS, http://anybox.fr Bureau: 09 53 53 72 97 Portable: 06 51 32 07 27 GPG: 0x33AB0A35, sur serveurs publics
Re: Odd query result
От:
Maximilian Tyrtania <maximilian.tyrtania@contactking.de>
Дата:
> On Mon, Aug 27, 2012 at 12:55 PM, Maximilian Tyrtania > wrote: >> What might be up there? > > It might be a broken index issue. Please show the EXPLAIN for these queries. It was. I had an index like this: CREATE INDEX idx_firmen_iskunde_index ON firmen USING btree (f_firmen_iskunde(firmen.*)); Dropping and recreating it fixed it. Thanks a lot, Maximilian Tyrtania http://www.contactking.de
Odd query result
От:
Maximilian Tyrtania <lists@contactking.de>
Дата:
Hello from Berlin, I can't quite make sense of this (running PG 9.0.3): psql (9.0.3) Type "help" for help. FAKDB=# Select _rowid,f_firmen_isKunde(firmen),bezeichnung::text from firmen where (firmen.bezeichnung='Microsoft Deutschland GmbH'); _rowid | f_firmen_iskunde | bezeichnung ----------+------------------+---------------------------- 1214700 | f | Microsoft Deutschland GmbH 15779700 | t | Microsoft Deutschland GmbH 166300 | t | Microsoft Deutschland GmbH (3 rows) FAKDB=# Select _rowid,f_firmen_isKunde(firmen),bezeichnung::text from FAKDB-# firmen where FAKDB-# (firmen.bezeichnung='Microsoft Deutschland GmbH') and FAKDB-# (f_firmen_isKunde(firmen)=true) and firmen._rowid=15779700 ; _rowid | f_firmen_iskunde | bezeichnung ----------+------------------+---------------------------- 15779700 | t | Microsoft Deutschland GmbH (1 row) Fine. But this record won't be found if I omit the last condition. FAKDB=# Select _rowid,f_firmen_isKunde(firmen),bezeichnung::text from firmen where (firmen.bezeichnung='Microsoft Deutschland GmbH') and (f_firmen_isKunde(firmen)=true); _rowid | f_firmen_iskunde | bezeichnung --------+------------------+---------------------------- 166300 | t | Microsoft Deutschland GmbH (1 row) What might be up there? Maximilian Tyrtania http://www.contactking.de
Re: Odd query result
От:
Maximilian Tyrtania <lists@contactking.de>
Дата:
> On Mon, Aug 27, 2012 at 12:55 PM, Maximilian Tyrtania > wrote: >> What might be up there? > > It might be a broken index issue. Please show the EXPLAIN for these queries. It was. I had an index like this: CREATE INDEX idx_firmen_iskunde_index ON firmen USING btree (f_firmen_iskunde(firmen.*)); Sorry, should have mentioned that…Dropping and recreating it fixed it. Thanks a lot, Maximilian Tyrtania http://www.contactking.de
Re: Odd query result
От:
Sergey Konoplev <gray.ru@gmail.com>
Дата:
On Mon, Aug 27, 2012 at 12:55 PM, Maximilian Tyrtania wrote: > What might be up there? It might be a broken index issue. Please show the EXPLAIN for these queries. > > Maximilian Tyrtania > http://www.contactking.de > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sergey Konoplev a database and software architect http://www.linkedin.com/in/grayhemp Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204
Re: Odd query result
От:
Sergey Konoplev <gray.ru@gmail.com>
Дата:
On Mon, Aug 27, 2012 at 2:37 PM, Georges Racinet wrote: > I guess one must also reindex in case some record changes as well, right ? > Through a trigger maybe ? You do not need to reindex it in case of record changes. The index values will be recalculated automatically after committing the changes. -- Sergey Konoplev a database and software architect http://www.linkedin.com/in/grayhemp Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204
Re: Odd query result
От:
Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>
Дата:
On Mon, Aug 27, 2012 at 1:56 PM, Maximilian Tyrtania wrote: >> It might be a broken index issue. Please show the EXPLAIN for these queries. > > It was. I had an index like this: > > CREATE INDEX idx_firmen_iskunde_index > ON firmen > USING btree > (f_firmen_iskunde(firmen.*)); > > Dropping and recreating it fixed it. Note that having such functional index you are risking to face similar oddities again after you modify the function. You need to manually reindex all the indexes that uses the function after it has been modified. To PG hackers. Are there any plans to add dependencies that will resolve such issues somehow? -- Sergey Konoplev a database architect, software developer at PostgreSQL-Consulting.com http://www.postgresql-consulting.com Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204
