postgres=# VACUUM FULL pg_statistic => ERROR: missing chunk number 0 for toast value .. in pg_toast_2619

[Justin Pryzby]

I recall seeing various discussions hoping that it'd been finally fixed - Just
wanted to report that this has happened now under postgres 10.4.

It looks like this is not related to: 0408e1ed599b06d9bca2927a50a4be52c9e74bb9
which is for "unexpected chunk number" (?)

Note that this is on the postgres database, which I think is where I saw it on
one of our internal VMs in the past (althought my memory indicates that may
have affected multiple DBs).  In the immediate case, this is customer's centos6
VM running under qemu/KVM: the same configuration as our internal VM which had
this issue (I just found a ticket dated 2016-10-06).

In case it helps:
 - the postgres database has a few things in it, primarily imported CSV logs.
   On this particular server, there's actually a 150GB table with old CSV logs
   from an script I fixed recently to avoid saving many lines than intended
   (something like for each session_id every session_line following an
   error_severity!='LOG')
 - I also have copies of pg_stat_bgwriter, pg_settings, and an aggregated copy
   of pg_buffercache here.
 - nagios: some scripts loop around all DBs; some maybe connect directly to
   postgres (for example, to list DBs).  However, I don't think check_postgres
   probably doesn't connect to postgres DB.

I'll defer fixing this for awhile in case someone wants me to save a copy of
the relation/toast/index.  From last time, I recall this just needs the right
combination of REINDEX/VACUUM/ANALYZE, and the only complication was me
needing to realize the right combination of affected DB(s).

Thanks,
Justin

Re: postgres=# VACUUM FULL pg_statistic => ERROR: missing chunk number 0 for toast value .. in pg_toast_2619

[Tom Lane]

Justin Pryzby  writes:
> I'll defer fixing this for awhile in case someone wants me to save a copy of
> the relation/toast/index.  From last time, I recall this just needs the right
> combination of REINDEX/VACUUM/ANALYZE, and the only complication was me
> needing to realize the right combination of affected DB(s).

If you could come up with such a sequence that causes the problem
reproducibly, that would be of huge interest, and probably lead to
a fix promptly.  But I don't think that we can do much by looking
at the post-mortem state --- once the toast rows are gone, they're
gone, especially if the table's been vacuumed since.

regards, tom lane

Re: postgres=# VACUUM FULL pg_statistic => ERROR: missing chunk number 0 for toast value .. in pg_toast_2619

[Justin Pryzby]

On Sat, May 19, 2018 at 11:08:23AM -0400, Tom Lane wrote:
> Justin Pryzby  writes:
> > I'll defer fixing this for awhile in case someone wants me to save a copy of
> > the relation/toast/index.  From last time, I recall this just needs the 
> > right
> > combination of REINDEX/VACUUM/ANALYZE, and the only complication was me
> > needing to realize the right combination of affected DB(s).
> 
> If you could come up with such a sequence that causes the problem
> reproducibly, that would be of huge interest, and probably lead to
> a fix promptly.  But I don't think that we can do much by looking
> at the post-mortem state --- once the toast rows are gone, they're
> gone, especially if the table's been vacuumed since.

This is unlikely to allow reproducing it, but for sake of completeness here's a
fuller log.  I'll try to trigger on another DB.

postgres=# SELECT log_time, database, session_id, left(message,99) FROM 
postgres_log WHERE log_time BETWEEN '2018-05-19 07:49:01' AND '2018-05-19 
07:50' AND (database IS NULL OR database='postgres') ORDER BY 1 ;
 2018-05-19 07:49:02.232-06 |  | 5afbc238.382f | checkpoint complete: 
wrote 32175 buffers (6.1%); 0 WAL file(s) added, 0 removed, 8 recycled; write=
 2018-05-19 07:49:02.261-06 | postgres | 5b002b4e.65f2 | statement: SHOW 
server_version
 2018-05-19 07:49:02.278-06 | postgres | 5b002b4e.65f7 | statement: SELECT 
pg_get_indexdef('jrn_postgres_log_log_time_idx'::regclass)
 2018-05-19 07:49:02.29-06  | postgres | 5b002b4e.65f9 | statement: SELECT 1 
FROM information_schema.tables WHERE table_name='postgres_log' LIMIT 1
 2018-05-19 07:49:02.311-06 | postgres | 5b002b4e.65fb | statement: SELECT 1 
FROM pg_class WHERE relname='jrn_postgres_log'
 2018-05-19 07:49:02.324-06 | postgres | 5b002b4e.65fd | statement: SELECT 1 
FROM pg_class WHERE relname='jrn_postgres_log_unique_idx'
 2018-05-19 07:49:02.338-06 | postgres | 5b002b4e.65ff | statement: SELECT 1 
FROM pg_class WHERE relname='jrn_postgres_log_log_time_idx'
 2018-05-19 07:49:02.353-06 | postgres | 5b002b4e.6601 | statement: SELECT 1 
FROM pg_class WHERE relname='jrn_postgres_log_error_severity_idx'
 2018-05-19 07:49:02.37-06  | postgres | 5b002b4e.6603 | statement: SELECT 1 
FROM pg_class WHERE relname='jrn_postgres_log_message_system_idx'
 2018-05-19 07:49:02.39-06  | postgres | 5b002b4e.6605 | statement: SELECT 1 
FROM pg_class WHERE relname='jrn_postgres_log_error_message_idx'
 2018-05-19 07:49:02.405-06 | postgres | 5b002b4e.6607 | statement: SELECT 1 
FROM pg_class WHERE relname='jrn_postgres_log_duration_idx'
 2018-05-19 07:49:02.422-06 | postgres | 5b002b4e.6609 | statement: SELECT 1 
FROM pg_class WHERE relname='jrn_postgres_log_quotedquoted_idx'
 2018-05-19 07:49:02.464-06 | postgres | 5b002b4e.6619 | statement: SELECT 1 
FROM pg_class WHERE relname='postgres_log_2018_05_19_0700'
 2018-05-19 07:49:02.482-06 | postgres | 5b002b4e.661c | statement: COPY 
postgres_log_2018_05_19_0700 FROM 
'/var/log/postgresql/postgresql-2018-05-19_074617
 2018-05-19 07:49:04.711-06 | postgres | 5b002b50.6627 | statement: SELECT 1 
FROM pg_class WHERE relname='postgres_log_2018_05_19_0700'
 2018-05-19 07:49:04.724-06 | postgres | 5b002b50.662a | statement: COPY 
postgres_log_2018_05_19_0700 FROM 
'/var/log/postgresql/postgresql-2018-05-19_074643
 2018-05-19 07:49:06.803-06 | postgres | 5b002b52.6637 | statement: SELECT 
pg_get_indexdef('jrn_postgres_log_duration_idx'::regclass)
 2018-05-19 07:49:06.837-06 | postgres | 5b002b52.6639 | statement: SELECT 
inhrelid::regclass::text FROM pg_inherits i LEFT JOIN pg_constraint c ON 
i.inhrel
 2018-05-19 07:49:06.867-06 | postgres | 5b002b52.663b | statement: SELECT 
inhrelid::regclass::text FROM pg_inherits WHERE 
inhparent='postgres_log'::regclas
 2018-05-19 07:49:06.918-06 | postgres | 5b002b52.6641 | statement: SELECT 
log_time

Re: postgres=# VACUUM FULL pg_statistic => ERROR: missing chunk number 0 for toast value .. in pg_toast_2619

[Justin Pryzby]

On Sat, May 19, 2018 at 11:24:57AM -0500, Justin Pryzby wrote:
> On Sat, May 19, 2018 at 11:08:23AM -0400, Tom Lane wrote:
> > Justin Pryzby  writes:
> > > I'll defer fixing this for awhile in case someone wants me to save a copy 
> > > of
> > > the relation/toast/index.  From last time, I recall this just needs the 
> > > right
> > > combination of REINDEX/VACUUM/ANALYZE, and the only complication was me
> > > needing to realize the right combination of affected DB(s).
> > 
> > If you could come up with such a sequence that causes the problem
> > reproducibly, that would be of huge interest, and probably lead to
> > a fix promptly.  But I don't think that we can do much by looking
> > at the post-mortem state --- once the toast rows are gone, they're
> > gone, especially if the table's been vacuumed since.
> 
> This is unlikely to allow reproducing it, but for sake of completeness here's 
> a
> fuller log.  I'll try to trigger on another DB.

Did not take long...

[pryzbyj@database ~]$ while :; do for db in `psql postgres -Atc "SELECT datname 
FROM pg_database WHERE datallowconn"`; do for t in pg_statistic pg_attrdef 
pg_constraint; do echo "$db.$t..."; PGOPTIONS=-cstatement_timeout='9s' psql $db 
-qc "VACUUM FULL $t"; done; done; done

...
postgres.pg_statistic...
postgres.pg_attrdef...
postgres.pg_constraint...
template1.pg_statistic...
template1.pg_attrdef...
template1.pg_constraint...
ts.pg_statistic...
ERROR:  canceling statement due to statement timeout
ts.pg_attrdef...
ts.pg_constraint...
postgres.pg_statistic...
ERROR:  missing chunk number 0 for toast value 3372855171 in pg_toast_2619

I'm running this again on another DB, but I wonder if that's enough for anyone
else to reproduce it with some consistency ?  I think that took something like
10min before failing.

Justin

View Filtered Rows technique

[Dale Seaburg]

I am looking for a convenient way to search a specific table where a 
specific column contains a string like this: "\2016\    \" (within the 
quote-marks, but not including the quote-marks).  I haven't found 
anything in the documentation (8.4.5).  I am using the View Data | View 
Filtered Rows method.  I was hoping there might be a 'CONTAINS' clause 
that would do the trick.  Any clues or help is appreciated.


Dale

Re: View Filtered Rows technique

[David G. Johnston]

On Sat, May 19, 2018 at 9:55 AM, Dale Seaburg  wrote:

> I am looking for a convenient way to search a specific table where a
> specific column contains a string like this: "\2016\\" (within the
> quote-marks, but not including the quote-marks).  I haven't found anything
> in the documentation (8.4.5).  I am using the View Data | View Filtered
> Rows method.  I was hoping there might be a 'CONTAINS' clause that would do
> the trick.  Any clues or help is appreciated.


​This sounds like a pgAdmin question and should be addressed to them.

​https://www.pgadmin.org/support/list/

You should also be checking the pgAdmin docs, not PostgreSQL proper (the
version you indicate is an ancient and unsupported PostgreSQL release).
Using pure SQL you'd do something like:

col ~ '\\\d{6}\\\s+\\' (regular expression match operator)

depending upon what exactly you mean by "like this"...

David J.

Re: postgres=# VACUUM FULL pg_statistic => ERROR: missing chunk number 0 for toast value .. in pg_toast_2619

[Tom Lane]

Justin Pryzby  writes:
> [pryzbyj@database ~]$ while :; do for db in `psql postgres -Atc "SELECT 
> datname FROM pg_database WHERE datallowconn"`; do for t in pg_statistic 
> pg_attrdef pg_constraint; do echo "$db.$t..."; 
> PGOPTIONS=-cstatement_timeout='9s' psql $db -qc "VACUUM FULL $t"; done; done; 
> done

> ...
> postgres.pg_statistic...
> postgres.pg_attrdef...
> postgres.pg_constraint...
> template1.pg_statistic...
> template1.pg_attrdef...
> template1.pg_constraint...
> ts.pg_statistic...
> ERROR:  canceling statement due to statement timeout
> ts.pg_attrdef...
> ts.pg_constraint...
> postgres.pg_statistic...
> ERROR:  missing chunk number 0 for toast value 3372855171 in pg_toast_2619

Hm, so was the timeout error happening every time through on that table,
or just occasionally, or did you provoke it somehow?  I'm wondering how
your 9s timeout relates to the expected completion time.

I don't have any test DBs with anywhere near large enough stats to
require 9s to vacuum pg_statistic, but I'm trying this with a
much-reduced value of statement_timeout, and so far no failures ...

regards, tom lane

initdb failing (10.4 centos7)

[Michael Nolan]

initdb is failing on Centos 7 with 10.4 because the install procedure does
not change the ownership of the /usr/local/pgsql directory to postgres.

Changing the ownership fixes the problem, but the install procedure should
do this.
--
Mike Nolan

Re: initdb failing (10.4 centos7)

[Adrian Klaver]

On 05/19/2018 12:10 PM, Michael Nolan wrote:
initdb is failing on Centos 7 with 10.4 because the install procedure 
does not change the ownership of the /usr/local/pgsql directory to 
postgres.


Changing the ownership fixes the problem, but the install procedure 
should do this.


Did you follow the below?:

https://www.postgresql.org/download/linux/redhat/
"
For RHEL / CentOS / SL / OL 7 or Fedora 27 and later derived distributions:

  postgresql-setup initdb
  systemctl enable postgresql.service
  systemctl start postgresql.service
"

If you did, what user did you do it as?



--
Mike Nolan



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

Re: initdb failing (10.4 centos7)

[Don Seiler]

On Sat, May 19, 2018 at 2:10 PM, Michael Nolan  wrote:

> initdb is failing on Centos 7 with 10.4 because the install procedure does
> not change the ownership of the /usr/local/pgsql directory to postgres.
>
> Changing the ownership fixes the problem, but the install procedure should
> do this.
>


Are you using the PGDG repo packages, or the default CentOS repo packages?
You should use PGDG and those should install under /var/lib/pgsql.

Don.
-- 
Don Seiler
www.seiler.us

Re: postgres=# VACUUM FULL pg_statistic => ERROR: missing chunk number 0 for toast value .. in pg_toast_2619

[Justin Pryzby]

On Sat, May 19, 2018 at 02:39:26PM -0400, Tom Lane wrote:
> Hm, so was the timeout error happening every time through on that table,
> or just occasionally, or did you provoke it somehow?  I'm wondering how
> your 9s timeout relates to the expected completion time.

I did not knowingly provoke it :)

Note that my script's non-artificial failure this morning, vac full of
pg_statistic DIDN'T timeout but the relation before it (pg_attrdef) DID.  I
guess the logs I sent earlier were incomplete.

I don't know if it times out every time..but I'm thinking timeout is
implicated, but I don't see how a time of on a previous command can cause an
error on a future session, for a non-"shared" relation.

However, I see this happened (after a few hours) on one server where I was
looping WITHOUT timeout.  So hopefully they have the same root cause and
timeout will be a good way to help trigger it.

postgres.pg_statistic...
ERROR:  missing chunk number 0 for toast value 615791167 in 
pg_toast_2619
Sat May 19 17:18:03 EDT 2018

I should have sent the output from my script:

<

Re: initdb failing (10.4 centos7)

[Michael Nolan]

On Sat, May 19, 2018 at 2:16 PM, Don Seiler  wrote:

> On Sat, May 19, 2018 at 2:10 PM, Michael Nolan  wrote:
>
>> initdb is failing on Centos 7 with 10.4 because the install procedure
>> does not change the ownership of the /usr/local/pgsql directory to
>> postgres.
>>
>> Changing the ownership fixes the problem, but the install procedure
>> should do this.
>>
>
>
> Are you using the PGDG repo packages, or the default CentOS repo packages?
> You should use PGDG and those should install under /var/lib/pgsql.
>
> Don.
> --
> Don Seiler
> www.seiler.us
>

I was installing from source code, so I did a configure and a build (as
postgres) and then an install (as root).
--
Mike Nolan

Re: initdb failing (10.4 centos7)

[Adrian Klaver]

On 05/19/2018 03:16 PM, Michael Nolan wrote:



On Sat, May 19, 2018 at 2:16 PM, Don Seiler > wrote:


On Sat, May 19, 2018 at 2:10 PM, Michael Nolan mailto:htf...@gmail.com>> wrote:

initdb is failing on Centos 7 with 10.4 because the install
procedure does not change the ownership of the /usr/local/pgsql
directory to postgres.

Changing the ownership fixes the problem, but the install
procedure should do this. 




Are you using the PGDG repo packages, or the default CentOS repo
packages? You should use PGDG and those should install under
/var/lib/pgsql.

Don.
-- 
Don Seiler

www.seiler.us 


I was installing from source code, so I did a configure and a build (as 
postgres) and then an install (as root).


Then setting up the $DATADIR is on you:

https://www.postgresql.org/docs/10/static/install-short.html



--
Mike Nolan



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

Re: initdb failing (10.4 centos7)

[Michael Nolan]

On Sat, May 19, 2018 at 5:23 PM, Adrian Klaver 
wrote:

> On 05/19/2018 03:16 PM, Michael Nolan wrote:
>
>>
>>
>> On Sat, May 19, 2018 at 2:16 PM, Don Seiler > d...@seiler.us>> wrote:
>>
>> On Sat, May 19, 2018 at 2:10 PM, Michael Nolan > > wrote:
>>
>> initdb is failing on Centos 7 with 10.4 because the install
>> procedure does not change the ownership of the /usr/local/pgsql
>> directory to postgres.
>>
>> Changing the ownership fixes the problem, but the install
>> procedure should do this.
>>
>>
>> Are you using the PGDG repo packages, or the default CentOS repo
>> packages? You should use PGDG and those should install under
>> /var/lib/pgsql.
>>
>> Don.
>> -- Don Seiler
>> www.seiler.us 
>>
>>
>> I was installing from source code, so I did a configure and a build (as
>> postgres) and then an install (as root).
>>
>
> Then setting up the $DATADIR is on you:
>
> https://www.postgresql.org/docs/10/static/install-short.html
>
>
Shouldn't this also be mentioned in the INSTALL file in the source
directory then?
--
Mike Nolan

>
>
> --
>> Mike Nolan
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: initdb failing (10.4 centos7)

[Adrian Klaver]

On 05/19/2018 03:44 PM, Michael Nolan wrote:



On Sat, May 19, 2018 at 5:23 PM, Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> wrote:


On 05/19/2018 03:16 PM, Michael Nolan wrote:



On Sat, May 19, 2018 at 2:16 PM, Don Seiler mailto:d...@seiler.us> >> wrote:

     On Sat, May 19, 2018 at 2:10 PM, Michael Nolan
mailto:htf...@gmail.com>
     >> wrote:

         initdb is failing on Centos 7 with 10.4 because the install
         procedure does not change the ownership of the
/usr/local/pgsql
         directory to postgres.

         Changing the ownership fixes the problem, but the install
         procedure should do this.


     Are you using the PGDG repo packages, or the default CentOS
repo
     packages? You should use PGDG and those should install under
     /var/lib/pgsql.

     Don.
     --     Don Seiler
www.seiler.us  


I was installing from source code, so I did a configure and a
build (as postgres) and then an install (as root).


Then setting up the $DATADIR is on you:

https://www.postgresql.org/docs/10/static/install-short.html



Shouldn't this also be mentioned in the INSTALL file in the source 
directory then?


In the INSTALL file that came with the 10.4 version I downloaded it is:

head -n 30 INSTALL


  PostgreSQL Installation from Source Code 






   This document describes the installation of PostgreSQL using the 
source 

   code distribution. (If you are installing a pre-packaged 
distribution, 

   such as an RPM or Debian package, ignore this document and read the 



   packager's instructions instead.) 



 __ 






Short Version 






./configure 



make 



su 



make install 



adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

   The long version is the rest of this document.
 __

Requirements

   In general, a modern Unix-compatible platform should be able to run
   PostgreSQL. The platforms that had received specific testing at the

...

Setting up $DATADIR is left up to the user as it a matter of choice for 
where you want to locate it.




--
Mike Nolan



--
Mike Nolan



-- 
Adrian Klaver

adrian.kla...@aklaver.com 





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

Re: initdb failing (10.4 centos7)

[Adrian Klaver]

On 05/19/2018 03:44 PM, Michael Nolan wrote:



On Sat, May 19, 2018 at 5:23 PM, Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> wrote:






Then setting up the $DATADIR is on you:

https://www.postgresql.org/docs/10/static/install-short.html



Shouldn't this also be mentioned in the INSTALL file in the source 
directory then?


It is also in the Getting Started portion of INSTALL.


--
Mike Nolan



--
Mike Nolan



-- 
Adrian Klaver

adrian.kla...@aklaver.com 





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

initdb failing (10.4 centos7)

[David G. Johnston]

On Saturday, May 19, 2018, Adrian Klaver  wrote:

> On 05/19/2018 03:44 PM, Michael Nolan wrote:
>
>>
>>
>> On Sat, May 19, 2018 at 5:23 PM, Adrian Klaver > > wrote:
>>
>>
>
>> Then setting up the $DATADIR is on you:
>>
>> https://www.postgresql.org/docs/10/static/install-short.html
>> 
>>
>>
>> Shouldn't this also be mentioned in the INSTALL file in the source
>> directory then?
>>
>
> It is also in the Getting Started portion of INSTALL.


IIUC the INSTALL file is simply an artifact of the build process for which
the doc sgml file is the source so they should indeed match.

While practically speaking one must initdb at least once after installation
in order for the product to be useful initdb is itself not really part of
the core installation routine.  IOW it is outside of what the make routine
is responsible for and, as documented, it doesn't deal with permissions
explicitly nor does it create parent directories.

David J.

Re: initdb failing (10.4 centos7)

[Adrian Klaver]

On 05/19/2018 04:06 PM, David G. Johnston wrote:
On Saturday, May 19, 2018, Adrian Klaver > wrote:


On 05/19/2018 03:44 PM, Michael Nolan wrote:



On Sat, May 19, 2018 at 5:23 PM, Adrian Klaver
mailto:adrian.kla...@aklaver.com>
>> wrote:



     Then setting up the $DATADIR is on you:

https://www.postgresql.org/docs/10/static/install-short.html


>


Shouldn't this also be mentioned in the INSTALL file in the
source directory then?


It is also in the Getting Started portion of INSTALL. 



IIUC the INSTALL file is simply an artifact of the build process for 
which the doc sgml file is the source so they should indeed match.


They are close but they do not match, there is no Getting Started 
section in the Installation portion of the Web docs:


https://www.postgresql.org/docs/10/static/installation.html



While practically speaking one must initdb at least once after 
installation in order for the product to be useful initdb is itself not 
really part of the core installation routine.  IOW it is outside of what 
the make routine is responsible for and, as documented, it doesn't deal 
with permissions explicitly nor does it create parent directories.


David J.



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

Re: initdb failing (10.4 centos7)

[Michael Nolan]

Just call me blind!
--
Mike Nolan

Re: Importing data from CSV into a table with array and composite types

[a]

Thank you so much. BTW, may I ask one more question that, how should I select 
every first element of the array??


I know that "select p[:] from f" will print all element of the p array, but I 
probably would want to present result as "select p[:].a from f", but the 
statement does not work.


May I as the correct statement of displaying all first element of the composite 
type in an array??


Thank you!


Shore 




-- Original message --
From: "Pavel Stehule"; 
Sendtime: Friday, May 18, 2018 5:46 PM
To: "a"<372660...@qq.com>; 
Cc: "pgsql-general"; 
Subject: Re: Importing data from CSV into a table with array and composite types



Hi

2018-05-18 10:37 GMT+02:00 a <372660...@qq.com>:
Hi:


I would like to import data from a csv table. But the table that is going to be 
inserted is constructed with arrays and composite types, also with array of 
composite.


I have tried many ways of inserting but fail. Can anyone help? Thank you so 
much.




create type p as (a int, b int);
create table f(pv p[], c int);
insert into f values(array[(10,20),(30,40)]::p[], 1000);
insert into f values(array[(1,20),(3,40)]::p[], -1000);


postgres=# copy f to stdout csv;
"{""(10,20)"",""(30,40)""}",1000
"{""(1,20)"",""(3,40)""}",-1000
Time: 0,391 ms



So you have to respect this format. CSV doesn't know a arrays, doesn't know 
composite - so these values are passed as string


Regards


Pavel

Re: Importing data from CSV into a table with array and composite types

[Pavel Stehule]

2018-05-20 7:36 GMT+02:00 a <372660...@qq.com>:

> Thank you so much. BTW, may I ask one more question that, how should I
> select every first element of the array??
>
> I know that "select p[:] from f" will print all element of the p array,
> but I probably would want to present result as "select p[:].a from f", but
> the statement does not work.
>
> May I as the correct statement of displaying all first element of the
> composite type in an array??
>

it is not easy - for example, that I sent you can write a query

postgres=# select array_agg(a) from f, unnest(pv) where f.c = 1000;
┌───┐
│ array_agg │
╞═══╡
│ {10,30}   │
└───┘
(1 row)


> Thank you!
>
> Shore
>
>
> -- Original message --
> *From:* "Pavel Stehule";
> *Sendtime:* Friday, May 18, 2018 5:46 PM
> *To:* "a"<372660...@qq.com>;
> *Cc:* "pgsql-general";
> *Subject:* Re: Importing data from CSV into a table with array and
> composite types
>
> Hi
>
> 2018-05-18 10:37 GMT+02:00 a <372660...@qq.com>:
>
>> Hi:
>>
>> I would like to import data from a csv table. But the table that is going
>> to be inserted is constructed with arrays and composite types, also with
>> array of composite.
>>
>> I have tried many ways of inserting but fail. Can anyone help? Thank you
>> so much.
>>
>
> create type p as (a int, b int);
> create table f(pv p[], c int);
> insert into f values(array[(10,20),(30,40)]::p[], 1000);
> insert into f values(array[(1,20),(3,40)]::p[], -1000);
>
> postgres=# copy f to stdout csv;
> "{""(10,20)"",""(30,40)""}",1000
> "{""(1,20)"",""(3,40)""}",-1000
> Time: 0,391 ms
>
> So you have to respect this format. CSV doesn't know a arrays, doesn't
> know composite - so these values are passed as string
>
> Regards
>
> Pavel
>