Re: Application Dependency/Limitations of Postgres Version Upgrade
On 03/09/2018 10:49 PM, amitabh kumar wrote: Hi, I would like to know about application dependency of PostgreSQL version upgrade. 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. As an example of the things that could trip you up: Your 8.4 --> 9.x https://www.postgresql.org/docs/10/static/release-9-1.html#id-1.11.6.121.4 " Change the default value of standard_conforming_strings to on (Robert Haas) By default, backslashes are now ordinary characters in string literals, not escape characters. This change removes a long-standing incompatibility with the SQL standard. escape_string_warning has produced warnings about this usage for years. E'' strings are the proper way to embed backslash escapes in strings and are unaffected by this change. Warning This change can break applications that are not expecting it and do their own string escaping according to the old rules. The consequences could be as severe as introducing SQL-injection security holes. Be sure to test applications that are exposed to untrusted input, to ensure that they correctly handle single quotes and backslashes in text strings. " I would as matter of course also review the release notes for each major release since 8.4 to look for further gotchas. Is there any dependency or limitation of applications in 9.6 after upgrading from these versions ? OS platforms we are using are in Linux 5, 6.7. 6.8 and Windows 10, 12. I am happy to provide more information if need. Regards, Amitabh PostgreSQL DBA -- Adrian Klaver adrian.kla...@aklaver.com
Re: Application Dependency/Limitations of Postgres Version Upgrade
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&to=9.6.8 -- Félix
wrong message when trying to create an already existing index
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: wrong message when trying to create an already existing index
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
Re: wrong message when trying to create an already existing index
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
On Sat, Mar 10, 2018 at 10:54 AM, legrand legrand < legrand_legr...@hotmail.com> wrote: > 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 > > *>message speaks about relation (and not index)>Would it be possible that this message reports the correct object type ?>I thougth that thoses messages where using relation's relkind:>wouldn't it be easier to read for beginners ?PostgreSQL is a "relational" database, and as such _all_ objects in the database are considered _relations_, even indexes. Therefore, the error message is correct, because_relation_ NEWINDEX already exists. I believe that the code is generic as the clause "IF EXISTS" checks against pg_classfor other _relations_ as defined in relkind, and therefore reports a generic message as"relation _relname_ already exists"To report on a specific relation type would be redundant, because you already knowfrom your SQL statement what type/relkind of relation you are trying to CREATE.IE: SQL> create index if not exists NEWINDEX on SCHEMA.TABLE(COL); ^* -- *Melvin Davidson* *Maj. Database & Exploration Specialist* *Universe Exploration Command – UXC* Employment by invitation only!
Re: wrong message when trying to create an already existing index
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 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: momjian.us is down?
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
> 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
Is there a way to create a functional index that tables tableoid column as an arg?
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
