Thread: Odd query result
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
On Mon, 27 Aug 2012 10:55:43 +0200 Maximilian Tyrtania <lists@contactking.de> 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 <frank@frank.uvena.de>
Attachment
On Mon, Aug 27, 2012 at 12:55 PM, Maximilian Tyrtania <lists@contactking.de> 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
> On Mon, Aug 27, 2012 at 12:55 PM, Maximilian Tyrtania > <lists@contactking.de> 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
On Mon, Aug 27, 2012 at 1:56 PM, Maximilian Tyrtania <lists@contactking.de> 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
On 08/27/2012 12:14 PM, Sergey Konoplev wrote: > On Mon, Aug 27, 2012 at 1:56 PM, Maximilian Tyrtania > <lists@contactking.de> 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
On Mon, Aug 27, 2012 at 2:37 PM, Georges Racinet <gracinet@anybox.fr> 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
On 08/27/2012 12:47 PM, Sergey Konoplev wrote: > On Mon, Aug 27, 2012 at 2:37 PM, Georges Racinet<gracinet@anybox.fr> 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
> On Mon, Aug 27, 2012 at 12:55 PM, Maximilian Tyrtania > <lists@contactking.de> 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
Maximilian Tyrtania <lists@contactking.de> 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
Am 27.08.2012 um 16:18 schrieb Tom Lane <tgl@sss.pgh.pa.us>: > Maximilian Tyrtania <lists@contactking.de> 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