Re: Q: documentation improvement re collation version mismatch

[Julien Rouhaud]

Hi,

On Wed, Nov 09, 2022 at 12:45:17PM +0100, Karsten Hilbert wrote:
> Dear all,
>
> regarding changed collation versions this
>
>   https://www.postgresql.org/docs/devel/sql-altercollation.html
>
> says:
>
>   The following query can be used to identify all
>   collations in the current database that need to be
>   refreshed and the objects that depend on them:
>
>   SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS 
> "Collation",
>  pg_describe_object(classid, objid, objsubid) AS "Object"
> FROM pg_depend d JOIN pg_collation c
>  ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
> WHERE c.collversion <> pg_collation_actual_version(c.oid)
> ORDER BY 1, 2;
>
> I feel the result of that query can be slightly surprising
> because it does not return (to my testing) any objects
> depending on the database default collation, nor the database
> itself (as per a collation version mismatch in pg_database).

Indeed.  The default collation is "pinned", so we don't record any dependency
on it.

But also, getting the list of direct dependency to a collation is also almost
useless as there are so many other scenario where we wouldn't record an index
dependency on a collation.

> Now, there is a line
>
> Perhaps this query (taken from the net)
>
>   SELECT  -- get collation-change endangered indices
>   indrelid::regclass::text,
>   indexrelid::regclass::text,
>   collname,
>   pg_get_indexdef(indexrelid)
>   FROM (
>   SELECT
>   indexrelid,
>   indrelid,
>   indcollation[i] coll
>   FROM
>   pg_index, generate_subscripts(indcollation, 1) 
> g(i)
>   ) s
>   JOIN pg_collation c ON coll=c.oid
>   WHERE
>   collprovider IN ('d', 'c')
>   AND
>   collname NOT IN ('C', 'POSIX');
>
> could be added to the paragraph (or it could be folded into
> the first query by a UNION or some such) ?

That query is a bit better, but unfortunately there are a lot of cases it won't
detect (like some use of collation in expressions or WHERE clauses), so if you
had a collation library upgrade that breaks your collations you can't use that
to reliably fix your indexes.

For now, the only safe way to go is either reindex everything, or everything
except some safe cases (non-partial indexes on plain-non-collatable datatypes
only).  Usually, those safe cases are usually enough to avoid most of useless
reindex activity.

change analyze function for a array type

[黄宁]

I create a new type and want to change its array type analyze function.

I use ALTER TYPE typename SET (ANALYZE = func); not worked?

Aw: Re: Q: documentation improvement re collation version mismatch

[Karsten Hilbert]

Thanks, Julien, for your explanation.

> > regarding changed collation versions this
> >
> > https://www.postgresql.org/docs/devel/sql-altercollation.html
> >
> > says:
> >
> > The following query can be used to identify all
> > collations in the current database that need to be
> > refreshed and the objects that depend on them:
> >
> > SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS 
> > "Collation",
> >pg_describe_object(classid, objid, objsubid) AS "Object"
> >   FROM pg_depend d JOIN pg_collation c
> >ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
> >   WHERE c.collversion <> pg_collation_actual_version(c.oid)
> >   ORDER BY 1, 2;
> >
> > I feel the result of that query can be slightly surprising
> > because it does not return (to my testing) any objects
> > depending on the database default collation, nor the database
> > itself (as per a collation version mismatch in pg_database).
>
> Indeed.  The default collation is "pinned", so we don't record any dependency
> on it.

Indirectly we do, don't we ?  Or else

> > WHERE
> > collprovider IN ('d', 'c')

would not make much sense, right ?

The comment above the query in the official documentation is rather assertive
(even if may true to the letter) and may warrant some more cautionary
wording ?   Added, perhaps, some variation of this:

> For now, the only safe way to go is either reindex everything, or everything
> except some safe cases (non-partial indexes on plain-non-collatable datatypes
> only).

Best,
Karsten

Re: Q: documentation improvement re collation version mismatch

[Julien Rouhaud]

On Thu, Nov 10, 2022 at 11:47:01AM +0100, Karsten Hilbert wrote:
> Thanks, Julien, for your explanation.
> 
> > > regarding changed collation versions this
> > >
> > >   https://www.postgresql.org/docs/devel/sql-altercollation.html
> > >
> > > says:
> > >
> > >   The following query can be used to identify all
> > >   collations in the current database that need to be
> > >   refreshed and the objects that depend on them:
> > >
> > >   SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS 
> > > "Collation",
> > >  pg_describe_object(classid, objid, objsubid) AS "Object"
> > > FROM pg_depend d JOIN pg_collation c
> > >  ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
> > > WHERE c.collversion <> pg_collation_actual_version(c.oid)
> > > ORDER BY 1, 2;
> > >
> > > I feel the result of that query can be slightly surprising
> > > because it does not return (to my testing) any objects
> > > depending on the database default collation, nor the database
> > > itself (as per a collation version mismatch in pg_database).
> >
> > Indeed.  The default collation is "pinned", so we don't record any 
> > dependency
> > on it.
> 
> Indirectly we do, don't we ?  Or else
> 
> > >   WHERE
> > >   collprovider IN ('d', 'c')
> 
> would not make much sense, right ?

What I meant is that we don't insert record in pg_depend to track dependencies
on pinned object, including the default collation.  The collprovider here comes
from pg_index.indcollation which is a different thing.  It can indeed store the
default collation, but it's only a small step toward less false negative.

Try that query with e.g.

CREATE INDEX ON sometable ( (somecol > 'somevalue') );

or

CREATE INDEX ON sometable (someid) WHERE somecol > 'somevalue';

Both clearly can get corrupted if the underlying collation library changes the
result of somecol > 'somevalue', but wouldn't be detected by that query.  There
are likely a lot more cases that would be missed, you can refer to the
discussions from a couple years ago when we tried to properly track all index
collation dependencies.

> The comment above the query in the official documentation is rather assertive
> (even if may true to the letter) and may warrant some more cautionary
> wording ?   Added, perhaps, some variation of this:
> 
> > For now, the only safe way to go is either reindex everything, or everything
> > except some safe cases (non-partial indexes on plain-non-collatable 
> > datatypes
> > only).

I think the comment is very poorly worded, as it leads readers to believe that
objects with a pg_depend dependency on a collation are the only one that would
get corrupted in case of glibc/ICU upgrade.

I agree that there should be a big fat red warning saying something like
"reindex everything if there's any discrepancy between the recorded collation
version and the currently reported one unless you REALLY know what you're
doing."

Re: Q: documentation improvement re collation version mismatch

[Ron]

On 11/10/22 02:33, Julien Rouhaud wrote:
[snip]

For now, the only safe way to go is either reindex everything, or everything
except some safe cases (non-partial indexes on plain-non-collatable datatypes
only).  Usually, those safe cases are usually enough to avoid most of useless
reindex activity.


In this situation, I queried for all indices with text-type columns 
(including UUID) and reindexed them.


--
Angular momentum makes the world go 'round.

Re: Q: documentation improvement re collation version mismatch

[Julien Rouhaud]

On Thu, Nov 10, 2022 at 08:04:37AM -0600, Ron wrote:
> On 11/10/22 02:33, Julien Rouhaud wrote:
> [snip]
> > For now, the only safe way to go is either reindex everything, or everything
> > except some safe cases (non-partial indexes on plain-non-collatable 
> > datatypes
> > only).  Usually, those safe cases are usually enough to avoid most of 
> > useless
> > reindex activity.
> 
> In this situation, I queried for all indices with text-type columns
> (including UUID) and reindexed them.

That may work in your personal use case, but it's not generally safe.  I hope
you don't have partial indexes, expressions or functions that internally relied
on collatable datatypes, or indexes on other plain collatable datatypes.

Re: Q: documentation improvement re collation version mismatch

[Ron]

On 11/10/22 08:33, Julien Rouhaud wrote:

On Thu, Nov 10, 2022 at 08:04:37AM -0600, Ron wrote:

On 11/10/22 02:33, Julien Rouhaud wrote:
[snip]

For now, the only safe way to go is either reindex everything, or everything
except some safe cases (non-partial indexes on plain-non-collatable datatypes
only).  Usually, those safe cases are usually enough to avoid most of useless
reindex activity.

In this situation, I queried for all indices with text-type columns
(including UUID) and reindexed them.

That may work in your personal use case, but it's not generally safe.  I hope
you don't have partial indexes,


Aren't partial indices listed in pg_indexes?


expressions or functions that internally relied
on collatable datatypes,


No such indices.


  or indexes on other plain collatable datatypes.


What other data types besides the text types are collatable?

--
Angular momentum makes the world go 'round.

Re: Q: documentation improvement re collation version mismatch

[Julien Rouhaud]

On Thu, Nov 10, 2022 at 08:39:03AM -0600, Ron wrote:
> On 11/10/22 08:33, Julien Rouhaud wrote:
> > On Thu, Nov 10, 2022 at 08:04:37AM -0600, Ron wrote:
> > > On 11/10/22 02:33, Julien Rouhaud wrote:
> > > [snip]
> > > > For now, the only safe way to go is either reindex everything, or 
> > > > everything
> > > > except some safe cases (non-partial indexes on plain-non-collatable 
> > > > datatypes
> > > > only).  Usually, those safe cases are usually enough to avoid most of 
> > > > useless
> > > > reindex activity.
> > > In this situation, I queried for all indices with text-type columns
> > > (including UUID) and reindexed them.
> > That may work in your personal use case, but it's not generally safe.  I 
> > hope
> > you don't have partial indexes,
> 
> Aren't partial indices listed in pg_indexes?

They are, but you mentioned "indices with text-type columns".  Did that also
include an analysis of the expressions stored in pg_index.indpred (and
pg_index.indexpr), and all underlying code it could call?

> >   or indexes on other plain collatable datatypes.
> 
> What other data types besides the text types are collatable?

varchar, name, custom types, composite types, domains...

pgadmin4 versions on Ubuntu 22.04

[Richard Welty]

i'm currently running pgadmin4 6.12 on a Ubuntu 22.04 desktop.

it's regularly notifying me that 6.15 is available. i installed using the

apt repository method in the docs, and no upgrade is available there

(or at least, that's what apt reports when i ask for one.)



wondering when the repo might get updated, or whether i should

be concerned about it at all.



thanks,

   richard

Re: pgadmin4 versions on Ubuntu 22.04

[Adrian Klaver]

On 11/10/22 07:48, Richard Welty wrote:

i'm currently running pgadmin4 6.12 on a Ubuntu 22.04 desktop.
it's regularly notifying me that 6.15 is available. i installed using the
apt repository method in the docs, and no upgrade is available there
(or at least, that's what apt reports when i ask for one.)


What repo?

Have you run apt update on the repo?

What is the command you are using to do the upgrade?



wondering when the repo might get updated, or whether i should
be concerned about it at all.

thanks,
    richard




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

Re: copy file from a client app to remote postgres isntance

[Вадим Самохин]

чт, 10 нояб. 2022 г. в 01:32, Peter J. Holzer :

> On 2022-11-09 12:57:23 -0600, Ron wrote:
> > On 11/9/22 10:17, Peter J. Holzer wrote:
> > > On 2022-11-07 14:40:40 -0600, Ron wrote:
> > > > On 11/7/22 10:57, Вадим Самохин wrote:
> > > >  I have an application that must copy a local file in csv format
> to a
> > > >  postgres table on a remote host. The closest solution is this
> one (https://
> > > >  stackoverflow.com/a/9327519/618020). It boils down to
> specifying a \copy
> > > >  meta-command in a psql command:
> > > >
> > > >  psql -U %s -p %s -d %s -f - < meta-command \nEOT\n
> > > >
> > > >  and executing it. But it's quite an unnatural way to write
> > > >  database code. Has anything changed in the last ten years?
> > > >  Or, is there a better way to copy file contents in a remote
> > > >  database?
> > > >
> > > >
> > > > I'd write a small Python script, using the csv module to read the
> data and
> > > > psycopg2 to load it.
> > > If you use insert statements it will be significantly slower (which may
> > > not matter for small files or one-off actions). If you use copy_from()
> > > you don't have to parse it (but then why use Python at all?)
> >
> > If OP does not want to embed psql in his app, then he must find a
> different
> > solution.  Python is such an option.
>
> Invoking a program written in Python is just as hard (or simple) as
> invoking a program written in C (psql). But that Python
> program is additional code in their project which has to be first
> written and then maintained.
>
> What they probably should do is write the code in the programming
> language they are already using for their app. And as I wrote just using
> copy (from within their app, not from psql or a python script or
> whatever) is probably the simplest solution. But since the OP chose not
> to tell us what programming language or library they use, it's hard to
> be more specific.
>
> hp
>
> --
>_  | Peter J. Holzer| Story must make more sense than reality.
> |_|_) ||
> | |   | h...@hjp.at |-- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |   challenge!"
>

Fantastic, never thought pdo could have this kind of method --
https://www.php.net/manual/en/pdo.pgsqlcopyfromfile.php! Haven't checked
yet, but it seems it does exactly what I need -- and what its name implies!
Thanks a lot!

Re: pgadmin4 versions on Ubuntu 22.04

[Richard Welty]

may have just found the issue:



# deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal pgadmin4 main # 

disabled on upgrade to jammy



will retry shortly.



richard







 On Thu, 10 Nov 2022 10:58:12 -0500 Adrian Klaver 
 wrote ---



On 11/10/22 07:48, Richard Welty wrote: 
> i'm currently running pgadmin4 6.12 on a Ubuntu 22.04 desktop. 
> it's regularly notifying me that 6.15 is available. i installed using the 
> apt repository method in the docs, and no upgrade is available there 
> (or at least, that's what apt reports when i ask for one.) 
 
What repo? 
 
Have you run apt update on the repo? 
 
What is the command you are using to do the upgrade? 
 
> 
> wondering when the repo might get updated, or whether i should 
> be concerned about it at all. 
> 
> thanks, 
>     richard 
> 
> 
 
-- 
Adrian Klaver 
mailto:adrian.kla...@aklaver.com

Re: pgadmin4 versions on Ubuntu 22.04

[Richard Welty]

and that was it. uncommented the line in 



/etc/apt/sources.list.d/pgadmin4.list



updated and asked for an upgrade and it worked.

only reason i found it was i went looking to verify which repo

i was using and saw the comment.



richard







 On Thu, 10 Nov 2022 11:15:02 -0500 Richard Welty  
wrote ---



may have just found the issue:



# deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal pgadmin4 main # 

disabled on upgrade to jammy



will retry shortly.



richard







 On Thu, 10 Nov 2022 10:58:12 -0500 Adrian Klaver 
 wrote ---











On 11/10/22 07:48, Richard Welty wrote: 
> i'm currently running pgadmin4 6.12 on a Ubuntu 22.04 desktop. 
> it's regularly notifying me that 6.15 is available. i installed using the 
> apt repository method in the docs, and no upgrade is available there 
> (or at least, that's what apt reports when i ask for one.) 
 
What repo? 
 
Have you run apt update on the repo? 
 
What is the command you are using to do the upgrade? 
 
> 
> wondering when the repo might get updated, or whether i should 
> be concerned about it at all. 
> 
> thanks, 
>     richard 
> 
> 
 
-- 
Adrian Klaver 
mailto:adrian.kla...@aklaver.com

Re: pgadmin4 versions on Ubuntu 22.04

[Adrian Klaver]

On 11/10/22 08:19, Richard Welty wrote:

and that was it. uncommented the line in

/etc/apt/sources.list.d/pgadmin4.list

updated and asked for an upgrade and it worked.
only reason i found it was i went looking to verify which repo
i was using and saw the comment.


Did you change

https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal

to

https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/jammy

?



richard



 On Thu, 10 Nov 2022 11:15:02 -0500 *Richard Welty 
* wrote ---


may have just found the issue:

# deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal
 pgadmin4
main #
disabled on upgrade to jammy

will retry shortly.

richard



 On Thu, 10 Nov 2022 10:58:12 -0500 *Adrian Klaver
mailto:adrian.kla...@aklaver.com>>*
wrote ---




On 11/10/22 07:48, Richard Welty wrote:
 > i'm currently running pgadmin4 6.12 on a Ubuntu 22.04 desktop.
 > it's regularly notifying me that 6.15 is available. i
installed using the
 > apt repository method in the docs, and no upgrade is
available there
 > (or at least, that's what apt reports when i ask for one.)

What repo?

Have you run apt update on the repo?

What is the command you are using to do the upgrade?

 >
 > wondering when the repo might get updated, or whether i should
 > be concerned about it at all.
 >
 > thanks,
 >    richard
 >
 >

-- 
Adrian Klaver

adrian.kla...@aklaver.com 





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

Aw: Re: Q: documentation improvement re collation version mismatch

[Karsten Hilbert]

> > The comment above the query in the official documentation is rather 
> > assertive
> > (even if may true to the letter) and may warrant some more cautionary
> > wording ?   Added, perhaps, some variation of this:
> >
> > > For now, the only safe way to go is either reindex everything, or 
> > > everything
> > > except some safe cases (non-partial indexes on plain-non-collatable 
> > > datatypes
> > > only).
>
> I think the comment is very poorly worded, as it leads readers to believe that
> objects with a pg_depend dependency on a collation are the only one that would
> get corrupted in case of glibc/ICU upgrade.
>
> I agree that there should be a big fat red warning saying something like
> "reindex everything if there's any discrepancy between the recorded collation
> version and the currently reported one unless you REALLY know what you're
> doing."

Given that it does not seem straightforward to mechanically detect objects
in need of a collation-associated rebuild I would think that such a warning
would change matters for the better, documentation-wise.

Karsten

IMMUTABLE function to cast enum to/from text?

[Philip Semanchuk]

Hi all,
I know that Postgres' enum_in()/enum_out() functions have a volatility class of 
STABLE, and STABLE is required because enum elements can be renamed. We have an 
enum in our database used in a number of custom functions, all of which require 
casting the enum to/from text. Since enum_in() and enum_out() are STABLE, that 
means our functions that rely on those casts must also be STABLE, and as a 
result we can't use them in generated columns. 

I have seen conversations that suggest creating a custom IMMUTABLE function to 
perform the cast, but I can't figure out how to do that except with a CASE 
statement that enumerates every possible value. Is there a more elegant 
approach?

Thanks 
Philip

Re: IMMUTABLE function to cast enum to/from text?

[Joe Conway]

On 11/10/22 14:52, Philip Semanchuk wrote:

Hi all,
I know that Postgres' enum_in()/enum_out() functions have a
volatility class of STABLE, and STABLE is required because enum
elements can be renamed. We have an enum in our database used in a
number of custom functions, all of which require casting the enum
to/from text. Since enum_in() and enum_out() are STABLE, that means
our functions that rely on those casts must also be STABLE, and as a
result we can't use them in generated columns.

I have seen conversations that suggest creating a custom IMMUTABLE
function to perform the cast, but I can't figure out how to do that
except with a CASE statement that enumerates every possible value. Is
there a more elegant approach?


When asking for help here, it always helps us to help you if you provide 
a self-contained set of SQL that illustrates what you are looking for.


That said, I think you are looking for something like this:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (name text, current_mood mood);
INSERT INTO person VALUES ('Moe', 'happy');
CREATE OR REPLACE FUNCTION mood2text(mood)
RETURNS text AS
$$
 select $1
$$ STRICT IMMUTABLE LANGUAGE sql;

SELECT name, mood2text(current_mood) FROM person;
 name | mood2text
--+---
 Moe  | happy
(1 row)

HTH,

--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Re: IMMUTABLE function to cast enum to/from text?

[Tom Lane]

Joe Conway  writes:
> On 11/10/22 14:52, Philip Semanchuk wrote:
>> I have seen conversations that suggest creating a custom IMMUTABLE
>> function to perform the cast, but I can't figure out how to do that
>> except with a CASE statement that enumerates every possible value. Is
>> there a more elegant approach?

> CREATE OR REPLACE FUNCTION mood2text(mood)
> RETURNS text AS
> $$
>   select $1
> $$ STRICT IMMUTABLE LANGUAGE sql;

Of course, what this is doing is using a SQL-function wrapper to
lie about the mutability of the expression.  Whether you consider
that elegant is up to you ;-) ... but it should work, as long as
you don't break things by renaming the enum's values.

regards, tom lane

Re: IMMUTABLE function to cast enum to/from text?

[Philip Semanchuk]

> On Nov 10, 2022, at 3:39 PM, Tom Lane  wrote:
> 
> Joe Conway  writes:
>> 
>> CREATE OR REPLACE FUNCTION mood2text(mood)
>> RETURNS text AS
>> $$
>>  select $1
>> $$ STRICT IMMUTABLE LANGUAGE sql;
> 
> Of course, what this is doing is using a SQL-function wrapper to
> lie about the mutability of the expression.  Whether you consider
> that elegant is up to you ;-) ... but it should work, as long as
> you don't break things by renaming the enum's values.


Thanks Joe and Tom,
I’m comfortable lying to Postgres occasionally — never for evil, only for good 
of course. :-)


Cheers
Philip

Re: Setting up replication on Windows, v9.4

[Brad White]

On 11/7/2022 3:42 PM, Rob Sargent wrote:
> Care to share some of the ways the app stopped working?  You might 
get a leg up on where best to remediate.

I don't recall, as that was a few months ago.

We are running MS-Access as a front end with Postgres as the back end.
It appeared to read and write data perfectly fine, but failed in the 
business logic on the Access side where there didn't appear to be any DB 
involvement. But switching to v14 caused it and switching away fixed it.

No obvious cause that I could see.
As written, switching databases was very time-consuming.
Since then, I've rewritten the app so that the network admin just 
changes an INI file that the app reads on start up.

If there is a change, it switches all the tables over in about 15 seconds.
We wanted that ability anyway in case we ever needed to switch to using 
the backup server.

Now, I am working on getting replication going.
Next up: back to upgrading.

But, since you ask, I'll post here when I get back to that so y'all can 
follow along as I narrow it down to a specific version.

Re: Setting up replication on Windows, v9.4

[Brad White]

From the error log:
     FATAL:  hot standby is not possible because wal_level was not
   set to "hot_standby" or higher on the master server
     HINT:  Either set wal_level to "hot_standby" on the master, or
   turn off hot_standby here.

I tried setting hot_standby to off, but that didn't help.
I set the wal_level to "hot_standby" and hot_standby to on, and now it 
appears to have made progress.

The service starts up without failing.


   > check if there's an entry in pg_stat_replication on  the primary.
   No. No entries.

There is now an entry on the primary.
But still can't connect from Navicat on the primary server and the log says

    FATAL:  could not receive data from WAL stream: server closed the 
connection unexpectedly

    This probably means the server terminated abnormally
    before or while processing the request.

    LOG:  record with zero length at 6B/6390
    FATAL:  could not connect to the primary server: FATAL:  the 
database system is starting up


    LOG:  started streaming WAL from primary at 6B/6300 on timeline 1
    LOG:  startup process (PID 3368) exited with exit code 1
    LOG:  terminating any other active server processes

¯\_(ツ)_/¯

programmatically retrieve details of a custom Postgres type

[Konstantin Izmailov]

Hello,
I was unable to find how to get column names, sizes and types for a given
composite type.

Example. For a type defines as:
  CREATE TYPE inventory_item AS (
  name text,
  supplier_id integer,
  price numeric
);

I have a plpgsql stored proc that returns SETOF inventory_item (i.e. there
is no table with a column of this type).

I looked into the pg_type table but it only contains oid and typrelid for
the inventory_item type. I need a query that returns information about
structure of the composite type, i.e.:
  ColumnName  |  ColumnType  |  ColumnSize
  name | text| -1
  supplier_id  | integer   | 4
  price  | numeric  | 16

Is this possible? I'm executing queries via libpq...

Thank you!

Re: programmatically retrieve details of a custom Postgres type

[Tom Lane]

Konstantin Izmailov  writes:
> I was unable to find how to get column names, sizes and types for a given
> composite type.

For a composite type, pg_type.typrelid links to pg_class and pg_attribute
entries that work much like a table.  For instance,

# select a.attname, a.atttypid::regtype from pg_type t left join pg_attribute a 
on t.typrelid = a.attrelid where typname = 'complex' order by attnum; 
 attname | atttypid 
-+--
 r   | double precision
 i   | double precision
(2 rows)

regards, tom lane

Re: programmatically retrieve details of a custom Postgres type

[Pavel Stehule]

pá 11. 11. 2022 v 5:02 odesílatel Konstantin Izmailov 
napsal:

> Hello,
> I was unable to find how to get column names, sizes and types for a given
> composite type.
>
> Example. For a type defines as:
>   CREATE TYPE inventory_item AS (
>   name text,
>   supplier_id integer,
>   price numeric
> );
>
> I have a plpgsql stored proc that returns SETOF inventory_item (i.e. there
> is no table with a column of this type).
>
> I looked into the pg_type table but it only contains oid and typrelid for
> the inventory_item type. I need a query that returns information about
> structure of the composite type, i.e.:
>   ColumnName  |  ColumnType  |  ColumnSize
>   name | text| -1
>   supplier_id  | integer   | 4
>   price  | numeric  | 16
>
> Is this possible? I'm executing queries via libpq...
>

 (2022-11-11 05:06:26) postgres=# create type foo as (a int, b varchar, c
numeric);
CREATE TYPE
(2022-11-11 05:06:42) postgres=#
\q
[pavel@localhost isolation]$ psql -E
Assertions: on
psql (16devel)
Type "help" for help.

(2022-11-11 05:06:47) postgres=# \d foo
* QUERY **
SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(foo)$' COLLATE pg_catalog.default
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**

* QUERY **
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules,
c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, false AS
relhasoids, c.relispartition, '', c.reltablespace, CASE WHEN c.reloftype =
0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END,
c.relpersistence, c.relreplident, am.amname
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)
WHERE c.oid = '16389';
**

* QUERY **
SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull,
  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
   WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation
<> t.typcollation) AS attcollation,
  a.attidentity,
  a.attgenerated
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '16389' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
**

  Composite type "public.foo"
┌┬───┬───┬──┬─┐
│ Column │   Type│ Collation │ Nullable │ Default │
╞╪═══╪═══╪══╪═╡
│ a  │ integer   │   │  │ │
│ b  │ character varying │   │  │ │
│ c  │ numeric   │   │  │ │
└┴───┴───┴──┴─┘

Important part is:

(2022-11-11 05:08:20) postgres=# select attname, atttypid::regtype from
pg_attribute where attrelid = 'foo'::regclass;
┌─┬───┐
│ attname │ atttypid  │
╞═╪═══╡
│ a   │ integer   │
│ b   │ character varying │
│ c   │ numeric   │
└─┴───┘
(3 rows)


> Thank you!
>

Regards

Pavel

Re: programmatically retrieve details of a custom Postgres type

[Tom Lane]

I wrote:
> For a composite type, pg_type.typrelid links to pg_class and pg_attribute
> entries that work much like a table.

Actually, you could reverse that: for a table, pg_type.typrelid links to
pg_class and pg_attribute entries that work much like a composite type.

For both relations and composite types, there are pg_class and pg_type
entries that (by convention only) have the same names and namespaces.
They cross-link to each other via pg_class.reltype and pg_type.typrelid.
The associated pg_attribute entries have attrelid matching the pg_class
OID.  The catalog entries for the two cases are pretty nearly
indistinguishable except for pg_class.relkind.  Again, it's only by
convention that we consider that the pg_type entry is primary for a
composite type but pg_class is primary for a relation.

Of course, a relation has some underlying storage (for most relkinds),
and it will likely have associated entries in other catalogs that a
composite type won't.  But the core catalog entries are about the same.

regards, tom lane

reviving "custom" dump

[Rob Sargent]

Short version: Does a current version of postgres tolerate ascii dumps 
from older versions?


I've been charged with reviving an old project.  I have a dump dated 
July of 2021 which file tells me is a "PostgreSQL custom database dump 
V1.13-0".  If our compute centre won't roll me a V12(?) postgres server, 
I'll need to use an "office" machine to bring this dataset back to 
life.  If I pull that off can I make an ascii dump and import that into 
our main db server (V14) or will I have to also install V14 in the 
office and dump/restore with that?

Re: reviving "custom" dump

[David G. Johnston]

On Thu, Nov 10, 2022 at 11:13 PM Rob Sargent  wrote:

> Short version: Does a current version of postgres tolerate ascii dumps
> from older versions?
>
> I've been charged with reviving an old project.  I have a dump dated July
> of 2021 which file tells me is a "PostgreSQL custom database dump
> V1.13-0".  If our compute centre won't roll me a V12(?) postgres server,
> I'll need to use an "office" machine to bring this dataset back to life.
> If I pull that off can I make an ascii dump and import that into our main
> db server (V14) or will I have to also install V14 in the office and
> dump/restore with that?
>
>

 https://www.postgresql.org/docs/current/app-pgdump.html

You should read the description and notes sections for how this command
works and its capabilities and potential limitations.

David J.

Re: reviving "custom" dump

[Tom Lane]

Rob Sargent  writes:
> Short version: Does a current version of postgres tolerate ascii dumps 
> from older versions?

We intend it to.  Have you got an actual problem?

regards, tom lane

Re: reviving "custom" dump

[Rob Sargent]

On 11/10/22 23:29, Tom Lane wrote:

Rob Sargent  writes:

Short version: Does a current version of postgres tolerate ascii dumps
from older versions?

We intend it to.  Have you got an actual problem?

regards, tom lane
I have to lay out options in the morning.  Thanks.  Now off to follow 
David's justified pointer.

Re: reviving "custom" dump

[Rob Sargent]

On 11/10/22 23:29, David G. Johnston wrote:
On Thu, Nov 10, 2022 at 11:13 PM Rob Sargent  
wrote:


Short version: Does a current version of postgres tolerate ascii
dumps from older versions?

I've been charged with reviving an old project.  I have a dump
dated July of 2021 which file tells me is a "PostgreSQL custom
database dump V1.13-0".  If our compute centre won't roll me a
V12(?) postgres server, I'll need to use an "office" machine to
bring this dataset back to life.  If I pull that off can I make an
ascii dump and import that into our main db server (V14) or will I
have to also install V14 in the office and dump/restore with that?



https://www.postgresql.org/docs/current/app-pgdump.html

You should read the description and notes sections for how this 
command works and its capabilities and potential limitations.


David J.

Off by one error here perhaps:  I have a custom dump in hand, it's 
restoring that is the problem.  But pretty sure the take home from 
pg_restore is "Use the newer version both ways".  Though mildly amused 
at the


   ; Dumped from database version: 8.3.5
   ; Dumped by pg_dump version: 8.3.8


in the re-ordering example!