Re: How to insert bulk data with libpq in C?
May I ask the proper (fast) way of inserting bulk data and if possible, an example would be very appreciated. Please note that the data I would like to insert contains arrays (float8[]). By the way, my environment is Windows 10, PostgreSQL 11 and compiling with Visual Studio 2017. That would be COPY: https://www.postgresql.org/docs/current/sql-copy.html In particular, copy table (col1, col2, ...) from stdin with binary Hmm, binary requires that you know the binary PostgreSQL representation. PostgreSQL might be faster converting text to its internal representation than your application program... The big exception here is of course bytea, where the binary representation is definitely smaller and easy to create. Hi, yes. I understood the data is already binary and OP wants to store it binary, but of course this might not be the case. Also, I suggest transaction bundling (one commit every 1000 records or so), depending on context. Why that? It might be useful if you expect the load to fail in the middle and want to be able to restart from there. But normally, doing it all in one transaction is simpler. Yes. That would be the special case with one commit every N records, where N is the total count :) I just wanted to make shure OP will not commit after each COPY. Bye. Chris.
Re: How to insert bulk data with libpq in C?
May I ask the proper (fast) way of inserting bulk data and if possible, an example would be very appreciated. Please note that the data I would like to insert contains arrays (float8[]). By the way, my environment is Windows 10, PostgreSQL 11 and compiling with Visual Studio 2017. That would be COPY: https://www.postgresql.org/docs/current/sql-copy.html In particular, copy table (col1, col2, ...) from stdin with binary see also https://www.postgresql.org/docs/10/static/protocol-flow.html#PROTOCOL-COPY https://www.postgresql.org/docs/10/static/sql-copy.html#id-1.9.3.52.9.4 Also, I suggesttransaction bundling (one commit every 1000 records or so), depending on context. Bye, Chris.
Re: postgresql10-server RPM unpacking of archive failed
on a CentOS 7.5 machine with PostgreSQL 10.3 installed from the PGDG yum repo, I have a strange error when trying to update to 10.6. A simple "yum update" updated everything except postgresql10-server.x86_64! That package gives (repeatedly) the error message: Error unpacking rpm package postgresql10-server-10.6-1PGDG.rhel7.x86_64 error: unpacking of archive failed: cpio: lstat I would try cleaning up all the data (packages and metadata) and trying again: yum clean all yum update postgresql10-server Regards, Thanks Martín, a yum clean all did not help. In the end, some time later (and after a reboot) yum update succeeded and we never found out why. I think we can archive this as a one-time glitch with yum. Bye, Chris.
Re: psql is hanging
We're kind of pulling out our hair here, any ideas? You might try issuing the command analyze; right *before* the command that hangs. Clarification: I meant to say: "put it into the script at the location right before the command, that hangs". Bye, Chris.
Re: psql is hanging
We're kind of pulling out our hair here, any ideas? You might try issuing the command analyze; right *before* the command that hangs. The rationale behind this idea is that your script changed data and the "hung" command uses a wrong plan based on outdated statistics. By the time you run it manually it would be fast again, because in the mean time the statistics have been updated automatically. Analyze forces an immediate update of the statistics on the whole database. This can by itself take some time according to size of the database. If my idea works, you can then further optimize by doing analyze only on the changed tables. Bye, Chris.
postgresql10-server RPM unpacking of archive failed
Hi, on a CentOS 7.5 machine with PostgreSQL 10.3 installed from the PGDG yum repo, I have a strange error when trying to update to 10.6. A simple "yum update" updated everything except postgresql10-server.x86_64! That package gives (repeatedly) the error message: Error unpacking rpm package postgresql10-server-10.6-1PGDG.rhel7.x86_64 error: unpacking of archive failed: cpio: lstat (full output below) I'm a bit at loss here... I'd interpret the message towards a corrupted package (?). Has anybody seen something like this? Thanks, Bye, Chris. # yum update Loaded plugins: fastestmirror Determining fastest mirrors epel/x86_64/metalink | 25 kB 00:00:00 * base: centos.mirror.garr.it * epel: epel.mirror.far.fi * extras: centos.mirror.garr.it * updates: centos.mirror.garr.it base | 3.6 kB 00:00:00 epel | 3.2 kB 00:00:00 extras | 3.4 kB 00:00:00 pbiso | 2.5 kB 00:00:00 pgdg10 | 4.1 kB 00:00:00 pgdg96 | 4.1 kB 00:00:00 updates | 3.4 kB 00:00:00 (1/12): base/7/x86_64/group_gz | 166 kB 00:00:00 (2/12): extras/7/x86_64/primary_db | 205 kB 00:00:00 (3/12): pbiso/x86_64/primary_db | 5.1 kB 00:00:00 (4/12): epel/x86_64/group_gz | 88 kB 00:00:00 (5/12): pgdg10/7/x86_64/group_gz | 245 B 00:00:00 (6/12): pgdg96/7/x86_64/group_gz | 249 B 00:00:00 (7/12): pgdg96/7/x86_64/primary_db | 203 kB 00:00:00 (8/12): pgdg10/7/x86_64/primary_db | 176 kB 00:00:00 (9/12): epel/x86_64/updateinfo | 932 kB 00:00:02 (10/12): epel/x86_64/primary | 3.6 MB 00:00:03 (11/12): base/7/x86_64/primary_db | 5.9 MB 00:00:03 (12/12): updates/7/x86_64/primary_db | 6.0 MB 00:00:03 epel 12706/12706 Resolving Dependencies --> Running transaction check ---> Package postgresql10-server.x86_64 0:10.3-1PGDG.rhel7 will be updated ---> Package postgresql10-server.x86_64 0:10.6-1PGDG.rhel7 will be an update --> Finished Dependency Resolution Dependencies Resolved === Package Arch Version Repository Size === Updating: postgresql10-server x86_64 10.6-1PGDG.rhel7 pgdg10 4.6 M Transaction Summary === Upgrade 1 Package Total download size: 4.6 M Is this ok [y/d/N]: y Downloading packages: Delta RPMs disabled because /usr/bin/applydeltarpm not installed. postgresql10-server-10.6-1PGDG.rhel7.x86_64.rpm | 4.6 MB 00:00:01 Running transaction check Running transaction test Transaction test succeeded Running transaction Updating : postgresql10-server-10.6-1PGDG.rhel7.x86_64 1/2 Error unpacking rpm package postgresql10-server-10.6-1PGDG.rhel7.x86_64 error: unpacking of archive failed: cpio: lstat Verifying : postgresql10-server-10.6-1PGDG.rhel7.x86_64 1/2 postgresql10-server-10.3-1PGDG.rhel7.x86_64 was supposed to be removed but is not! Verifying : postgresql10-server-10.3-1PGDG.rhel7.x86_64 2/2 Failed: postgresql10-server.x86_64 0:10.3-1PGDG.rhel7 postgresql10-server.x86_64 0:10.6-1PGDG.rhel7 Complete!
Re: Searching for big differences between values
I need to search for big differences between values. [...] Hi, from an SQL point of view this is not difficult, but you need to carefully define a criteria for the outliers. For example, to find values that are more than a standard deviation away from the mean, do something like this: chris=# select * from val; x --- 20 21 21.5 30 28 46392 46393 40 (8 rows) chris=# select * from val where x > (select avg(x) + stddev(x) from val) or x < (select avg(x) - stddev(x) from val); x --- 46392 46393 (2 rows) Try with n*stddev(x) for n = 2, 3, 4, 5, ... to see if you can get to your outliers... Bye, Chris.
