Odd query result

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>

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.*));

Dropping and recreating it fixed it.

Thanks a lot,

Maximilian Tyrtania
http://www.contactking.de

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

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