Is there a way to create a functional index that tables tableoid column as an arg?

[Ryan Murphy]

Hello Postgressers,

I am using table inheritance and have e.g. the following tables:

create table animal ( ... );
create table dog ( ... ) inherits (animal);
create table cat ( ... ) inherits (animal);
create table person ( ... ) inherits (animal);
create table musician ( ... ) inherits (person);
create table politician ( ... ) inherits (person);

Now I have a query that gets all the "animal"'s except for those that are
"person"'s.

select * from only animal

won't cut it, because it leaves out the dogs and cats.

select *, tableoid::regclass relname from animal
where relname != 'person'::regclass

also won't cut it because it leaves out the musicians and politicians.

So I have created an immutable function is_a_kind_of(tbl regclass,
parent_tbl regclass) that returns true iff tbl is identical with, or
directly or indirectly inherits from, parent_tbl.  For example:

is_a_kind_of('person','person') => true
is_a_kind_of('person','animal') => true
is_a_kind_of('musician','person') => true
is_a_kind_of('animal','person') => false
is_a_kind_of('dog','person') => false

No problems so far.  Now my query works:

select *,tableoid from "animal"
where not is_a_kind_of(tableoid::regclass::text, 'person')

This query is somewhat slow though - I'd like to index the is_a_kind_of()
call.  And Postgres supports functional indexes!  So I try:

create index animal_is_person on animal (
is_a_kind_of(tableoid::regclass, 'person') );

ERROR:  index creation on system columns is not supported

I see that this is because "tableoid" is a system column. Does anyone know
any workaround for this?  So close yet so far away!

Thanks!
Ryan

Re: wrong message when trying to create an already existing index

[legrand legrand]

> regression=# create index mv1 on t1 (f1);
...
> ERROR:  materialized view "mv1" already exists 

Is in fact the one I prefer ;^)

I come from a DBMS world where Tables and Indexes do not share the same name
space,
and have to change my mind !

Thanks you Tom for pointing that.

Regards
PAscal




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: momjian.us is down?

[Bruce Momjian]

On Sun, Mar  4, 2018 at 11:22:56PM -0800, Igal wrote:
> 
> 
> On 03/04/2018 09:57 PM, Tom Lane wrote:
> >Igal  writes:
> >>On 03/04/2018 07:24 PM, Adrian Klaver wrote:
> >>>On 03/04/2018 05:53 PM, Igal wrote:
> I am trying to connect to http://momjian.us/ but I get connection
> timeout (from Firefox):
> >Dunno if it's related, but large parts of the US Northeast were without
> >power over the weekend due to storm damage.
> 
> Looks like the site is hosted in Philadelphia, so very possibly related. 
> I'm glad that the storm is behind you guys now.

Yes, a weather-related power outage was the cause of the 48-hour
downtime.  Sorry.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +

Re: wrong message when trying to create an already existing index

[Tom Lane]

legrand legrand  writes:
> I thougth that thoses messages where using relation's relkind:
> ..
> wouldn't it be easier to read for beginners ?

I doubt it would be an improvement.  Consider this example:

regression=# create table t1 (f1 int);
CREATE TABLE
regression=# create materialized view mv1 as select * from t1;
SELECT 0
regression=# create index mv1 on t1 (f1);
ERROR:  relation "mv1" already exists

You seem to be proposing that the error should read either

ERROR:  index "mv1" already exists

which would be a lie, or

ERROR:  materialized view "mv1" already exists

which while accurate seems to me to be *more* confusing not less.
A person who did not understand that these relation types all
share the same namespace would probably not get enlightened
this way.  Using the generic term "relation" is just as accurate,
and it might help somebody understand that the problem is exactly
that relations of different types share the same namespace.

regards, tom lane

Re: wrong message when trying to create an already existing index

[legrand legrand]

OK, that noted !
thank you for the quick answers

Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: wrong message when trying to create an already existing index

[legrand legrand]

I thougth that thoses messages where using relation's relkind:
  r = ordinary table, 
  i = index, 
  S = sequence, 
  t = TOAST table, 
  v = view, 
  m = materialized view, 
  c = composite type, 
  f = foreign table, 
  p = partitioned table

wouldn't it be easier to read for beginners ?

Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: wrong message when trying to create an already existing index

[Adrian Klaver]

On 03/10/2018 07:00 AM, legrand legrand wrote:

Hello,
When trying to create an already existing index (in pg 9.5)

SQL> create  index if not exists NEWINDEX on SCHEMA.TABLE(COL);
   > relation "NEWINDEX" already exists, skipping

message speaks about relation (and not index)


https://www.postgresql.org/docs/10/static/catalog-pg-class.html

"The catalog pg_class catalogs tables and most everything else that has 
columns or is otherwise similar to a table. This includes indexes (but 
see also pg_index), sequences (but see also pg_sequence), views, 
materialized views, composite types, and TOAST tables; see relkind. 
Below, when we mean all of these kinds of objects we speak of 
“relations”. Not all columns are meaningful for all relation types."





Would it be possible that this message reports the correct object type ?
Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html





--
Adrian Klaver
adrian.kla...@aklaver.com

wrong message when trying to create an already existing index

[legrand legrand]

Hello,
When trying to create an already existing index (in pg 9.5)

SQL> create  index if not exists NEWINDEX on SCHEMA.TABLE(COL);
  > relation "NEWINDEX" already exists, skipping

message speaks about relation (and not index)

Would it be possible that this message reports the correct object type ?
Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Application Dependency/Limitations of Postgres Version Upgrade

[Félix GERZAGUET]

On Sat, Mar 10, 2018 at 3:50 PM, Adrian Klaver 
wrote:

> On 03/09/2018 10:49 PM, amitabh kumar wrote:
>
>> We have multiple servers with PG versions in 8.4, 9.2, 9.3, 9.4 and 9.5,
>> that we want to upgrade to 9.6. We want to be sure that all applications
>> will run smoothly after upgrade.
>>
>
> The only way I know to be sure is to is to set up a test instance of 9.6
> and test your applications.
>
> I would as matter of course also review the release notes for each major
> release since 8.4 to look for further gotchas.
>

One convenient way of reviewing the "cumulated" release notes is:
https://why-upgrade.depesz.com/show?from=8.4=9.6.8

--
Félix