Re: [GENERAL] Imperative Query Languages

[John Turner]

(copying the list)

On Wed, Jul 5, 2017 at 12:22 AM, Jason Dusek  wrote:

> Are there any “semi-imperative” query languages that have been tried in
> the past?
>
not particularly relevant to the Unix or Windows worlds, but on OpenVMS
there's Datatrieve:
https://en.wikipedia.org/wiki/DATATRIEVE

-John

On Wed, Jul 5, 2017 at 12:22 AM, Jason Dusek  wrote:

> Hi All,
>
> This more of a general interest than specifically Postgres question. Are
> there any “semi-imperative” query languages that have been tried in the
> past? I’m imagining a language where something like this:
>
> for employee in employees:
> for department in department:
> if employee.department == department.department and
>department.name == "infosec":
> yield employee.employee, employee.name, employee.location, 
> employee.favorite_drink
>
> would be planned and executed like this:
>
> SELECT employee.employee, employee.name, employee.location, 
> employee.favorite_drink
>   FROM employee JOIN department USING (department)
>  WHERE department.name == "infosec"
>
> The only language I can think of that is vaguely like this is Fortress, in
> that it attempts to emulate pseudocode and Fortran very closely while being
> fundamentally a dataflow language.
>
> Kind Regards,
>
>   Jason
> ​
>

[GENERAL] Imperative Query Languages

[Jason Dusek]

Hi All,

This more of a general interest than specifically Postgres question. Are
there any “semi-imperative” query languages that have been tried in the
past? I’m imagining a language where something like this:

for employee in employees:
for department in department:
if employee.department == department.department and
   department.name == "infosec":
yield employee.employee, employee.name, employee.location,
employee.favorite_drink

would be planned and executed like this:

SELECT employee.employee, employee.name, employee.location,
employee.favorite_drink
  FROM employee JOIN department USING (department)
 WHERE department.name == "infosec"

The only language I can think of that is vaguely like this is Fortress, in
that it attempts to emulate pseudocode and Fortran very closely while being
fundamentally a dataflow language.

Kind Regards,

  Jason
​

[GENERAL] Strange case of database bloat

[Chris Travers]

Hi;

First, I haven't seen major problems of database bloat in a long time which
is why I find this case strange.  I wanted to ask here what may be causing
it.

Problem:
==
Database is in the 100GB to 200GB size range, running on btrfs (not my
choice) with nodatacow enabled (which I set up to fix a performance
issue).  The workload is a very heavy batch-update workload.

The database bloats linearly.  I have measured this on one  table (of 149M
rows).

After vacuum full this table is (including indexes): 17GB
Every 24 hrs, seems to add its original space in size to the file system
+/-.

Bloat seems to be affecting both indexes and underlying tables.

Vacuum verbose does not indicate a disproportionate number of rows being
unremovable.  So autovacuum is keeping up without too much difficulty.


Troubleshooting so far
===

 filefrag finds a single extent on each file, so copy-on-write is not the
culprit

Selecting the smallest 10 values of ctid from one of the bloating tables
shows the first page used is around page 35 with one row per used page (and
large gaps in between).

Questions
===
I assume that it is the fact that rows update frequently which is the
problem here? But why doesn't Postgres re-use any of the empty disk pages?

More importantly, is there anything that can be done to mitigate this issue
other than a frequent vacuum full?

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: [GENERAL] 64bit initdb failure on macOS 10.11 and 10.12

[Tom Lane]

Adrian Klaver  writes:
> On 07/04/2017 01:29 PM, Rainer J.H. Brandt wrote:
>> Good to know.  I removed those options and tried again.

> Did you run make clean before re-running ./configure?

Personally I do "make distclean" before changing any configure options.
I'm not sure how much difference that really makes, but why waste brain
cells chasing such issues?  Build cycles are cheap.

The whole thing's odd though --- certainly many people are building
PG successfully on macOS.  There's got to be something unusual about
Rainer's build environment, but what?

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using 'WITH SELECT' Results Do Not Match 'SELECT FROM ' Results

[Jerry Regan]

Adrian,

Working from my phone wasn't such a good idea!

When I said 'INSERT' I meant 'WITH'. My excuse is that the 'WITH' statement is 
building a temporary table ( at least logically ) so there is at least an 
implicit 'INSERT' there.

/s/jr
Sent from my iPhone

> On Jul 3, 2017, at 23:12, Adrian Klaver  wrote:
> 
>> On 07/03/2017 05:20 PM, Jerry Regan wrote:
>> Adrian,
>> Thank you for your reply!
>> I apologize in advance for not being detailed below. Hard to do from my 
>> phone.
>> I did have to move the 'ORDER BY', but not outside the 'WITH'. My first 
>> workaround parenthesized the select containing the 'ORDER BY', forcing it to 
>> be evaluated before the 'INSERT'. That worked.
> 
> Not sure where the INSERT comes into the picture, but glad you got it working.
> 
>> But I never liked using a sequence for the c_id column. And using the 
>> sequence on my personal workstation was maybe safe, but given that sequences 
>> not are guaranteed to be without gaps, that was not very portable.
> 
> Yeah, that concerned me also, still I figured one problem at a time.
> 
>> So I searched a bit and found I could use 'row_number()' instead. That 
>> approach allowed me to use the 'ORDER BY' required by 'row_number()'.
>> That worked and is far more portable to other postgresql instances.
>> I really do appreciate your response. It is also my nature to continue my 
>> research even after asking for help. However I find my answer, one validates 
>> the other.
> 
> Sometimes it just a matter a little push to get out of the rut:)
> 
>> Thanks again!
>> /s/jr
>> Sent from my iPhone
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot

[Melvin Davidson]

On Tue, Jul 4, 2017 at 5:55 PM, Stephen Frost  wrote:

> Greetings,
>
> * hvjunk (hvj...@gmail.com) wrote:
> > I’ve previously done ZFS snapshot backups like this:
> >
> > psql -c “select pg_start_backup(‘snapshot’);”
> > zfs snapshot TANK/postgresql@`date ‘+%Ymd’`
> > psql -c “select * from  pg_stop_backup();”
>
> Hopefully you are also doing WAL archiving...
>
> > Reading the PostgreSQL9.6 documentation, the advice/future is to use the
> non-exclusive method, where I’ll need to keep a session *open* while the
> snapshot takes place, and after that I’ll have to issue the
> pg_stop_backup(false); in that active connection that issued the
> pg_start_backup(‘backup’,false,false);
>
> Right.
>
> > How is this done inside a shell script?
>
> Generally, it's not.  I suppose it might be possible to use '\!' with
> psql and then have a shell snippet that looks for some file that's
> touched when the snapshot has finished, but really, trying to perform a
> PG backup using hacked together shell scripts isn't recommended and
> tends to have problems.
>
> In particular WAL archiving- there's no simple way for a shell script
> which is being used for archiving to confirm that the WAL it has
> "archived" has been completely written out to disk (which is required
> for proper archiving).  Further, simple shell scripts also don't check
> that all of the WAL has been archived and that there aren't any holes in
> the WAL between the starting point of the backup and the end point.
>
> > Especially how to do error checking from the commands as psql -c “select
> pg_start_backup{‘test’,false,false);” not going to work?
>
> I'd recommend considering one of the existing PG backup tools which know
> how to properly perform WAL archiving and tracking the start/stop points
> in the WAL of the backup.  Trying to write your own using shell scripts,
> even with ZFS snapshots, isn't trivial.  If you trust the ZFS snapshot
> to be perfectly atomic across all filesystems/tablespaces used for PG,
> you could just take a snapshot and forget the rest- PG will do crash
> recovery when you have to restore from that snapshot but that's not much
> different from having to do WAL replay of the WAL generated during the
> backup.
>
> As for existing solutions, my preference/bias is for pgBackRest, but
> there are other options out there which also work, such as barman.
>
> Thanks!
>
> Stephen
>

Here is a model shell script I use to do a base backup to set up a slave.
See attached ws_base_backup.sh

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.


ws_base_backup.sh
Description: Bourne shell script

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot

[Stephen Frost]

Greetings,

* hvjunk (hvj...@gmail.com) wrote:
> I’ve previously done ZFS snapshot backups like this:
> 
> psql -c “select pg_start_backup(‘snapshot’);”
> zfs snapshot TANK/postgresql@`date ‘+%Ymd’`
> psql -c “select * from  pg_stop_backup();”

Hopefully you are also doing WAL archiving...

> Reading the PostgreSQL9.6 documentation, the advice/future is to use the 
> non-exclusive method, where I’ll need to keep a session *open* while the 
> snapshot takes place, and after that I’ll have to issue the 
> pg_stop_backup(false); in that active connection that issued the 
> pg_start_backup(‘backup’,false,false);

Right.

> How is this done inside a shell script?

Generally, it's not.  I suppose it might be possible to use '\!' with
psql and then have a shell snippet that looks for some file that's
touched when the snapshot has finished, but really, trying to perform a
PG backup using hacked together shell scripts isn't recommended and
tends to have problems.

In particular WAL archiving- there's no simple way for a shell script
which is being used for archiving to confirm that the WAL it has
"archived" has been completely written out to disk (which is required
for proper archiving).  Further, simple shell scripts also don't check
that all of the WAL has been archived and that there aren't any holes in
the WAL between the starting point of the backup and the end point.

> Especially how to do error checking from the commands as psql -c “select 
> pg_start_backup{‘test’,false,false);” not going to work?

I'd recommend considering one of the existing PG backup tools which know
how to properly perform WAL archiving and tracking the start/stop points
in the WAL of the backup.  Trying to write your own using shell scripts,
even with ZFS snapshots, isn't trivial.  If you trust the ZFS snapshot
to be perfectly atomic across all filesystems/tablespaces used for PG,
you could just take a snapshot and forget the rest- PG will do crash
recovery when you have to restore from that snapshot but that's not much
different from having to do WAL replay of the WAL generated during the
backup.

As for existing solutions, my preference/bias is for pgBackRest, but
there are other options out there which also work, such as barman.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] 64bit initdb failure on macOS 10.11 and 10.12

[Rainer J.H. Brandt]

Adrian Klaver writes:
> On 07/04/2017 01:29 PM, Rainer J.H. Brandt wrote:
> > Tom Lane writes:
> >> r...@bb-c.de (Rainer J.H. Brandt) writes:
> 
> >>
> >> This makes little sense to me.  64-bit builds have been the default on
> >> macOS for some time.
> > Good to know.  I removed those options and tried again.
> 
> Did you run make clean before re-running ./configure?
I removed everything, unpacked the sources, and ran the command given
below.

> >> It's possible that by overriding LDFLAGS you're removing linker switches
> >> that need to be there ...
> > Thanks for that suggestion.  I'm now using this (with 9.6.3):
> > 
> > ./configure PERL=/opt/bb/170704/bin/perl --with-perl --prefix=/opt/bb/170704

Rainer
-- 
Email: r...@bb-c.de
Telefon: 0172/9593205

Brandt & Brandt Computer GmbH
Am Wiesenpfad 6, 53340 Meckenheim
Geschäftsführer: Rainer J.H. Brandt und Volker A. Brandt
Handelsregister: Amtsgericht Bonn, HRB 10513


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 64bit initdb failure on macOS 10.11 and 10.12

[Adrian Klaver]

On 07/04/2017 01:29 PM, Rainer J.H. Brandt wrote:

Tom Lane writes:

r...@bb-c.de (Rainer J.H. Brandt) writes:




This makes little sense to me.  64-bit builds have been the default on
macOS for some time.

Good to know.  I removed those options and tried again.


Did you run make clean before re-running ./configure?




It's possible that by overriding LDFLAGS you're removing linker switches
that need to be there ...

Thanks for that suggestion.  I'm now using this (with 9.6.3):

./configure PERL=/opt/bb/170704/bin/perl --with-perl --prefix=/opt/bb/170704

I still get the error.  I don't see how Perl can make the difference,
but I guess it's not relevant that I can't see it ;-(

Thanks for taking the time to answer.
Rainer




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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 64bit initdb failure on macOS 10.11 and 10.12

[Rainer J.H. Brandt]

Tom Lane writes:
> r...@bb-c.de (Rainer J.H. Brandt) writes:
> > I got this initdb error for a 64bit-build on macOS El Capitan and Sierra:
> 
> > creating conversions ... FATAL:  could not load library 
> > "/opt/bb/170704/lib/postgresql/ascii_and_mic.so": 
> > dlopen(/opt/bb/170704/lib/postgresql/ascii_and_mic.so, 10): Symbol not 
> > found: _check_encoding_conversion_args
> >   Referenced from: /opt/bb/170704/lib/postgresql/ascii_and_mic.so
> >   Expected in: /opt/bb/170704/bin/postgres
> >  in /opt/bb/170704/lib/postgresql/ascii_and_mic.so
> 
> check_encoding_conversion_args() should certainly be there in any PG
> version released since 2009 (see src/backend/utils/mb/wchar.c).  But it's
> unreferenced in the core Postgres executable, only in the loadable
> conversion libraries.  I wonder if you have somehow enabled a link-time
> optimization to remove "unreferenced" symbols, or at least not export them
> to libraries.  Are you using Apple's stock toolchain, or something else?
Yes: current Xcode, nothing else.

> > I configured with ./configure CC='gcc -m64' --prefix=/opt/bb/170704
> > and also tried with CFLAGS=-m64 LDFLAGS=-m64.
> > With the same procedures, but 32-bit-builds, I don't get this error,
> > and everything works fine.  Unfortunately, I need the 64-bit version.
> 
> This makes little sense to me.  64-bit builds have been the default on
> macOS for some time.
Good to know.  I removed those options and tried again.

> It's possible that by overriding LDFLAGS you're removing linker switches
> that need to be there ...
Thanks for that suggestion.  I'm now using this (with 9.6.3):

./configure PERL=/opt/bb/170704/bin/perl --with-perl --prefix=/opt/bb/170704

I still get the error.  I don't see how Perl can make the difference,
but I guess it's not relevant that I can't see it ;-(

Thanks for taking the time to answer.
Rainer
-- 
Email: r...@bb-c.de
Telefon: 0172/9593205

Brandt & Brandt Computer GmbH
Am Wiesenpfad 6, 53340 Meckenheim
Geschäftsführer: Rainer J.H. Brandt und Volker A. Brandt
Handelsregister: Amtsgericht Bonn, HRB 10513


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using 'WITH SELECT' Results Do Not Match 'SELECT FROM ' Results

[Jerry Regan]

Adrian,

Thank you for your reply!

I apologize in advance for not being detailed below. Hard to do from my phone. 

I did have to move the 'ORDER BY', but not outside the 'WITH'. My first 
workaround parenthesized the select containing the 'ORDER BY', forcing it to be 
evaluated before the 'INSERT'. That worked.

But I never liked using a sequence for the c_id column. And using the sequence 
on my personal workstation was maybe safe, but given that sequences not are 
guaranteed to be without gaps, that was not very portable.

So I searched a bit and found I could use 'row_number()' instead. That approach 
allowed me to use the 'ORDER BY' required by 'row_number()'.

That worked and is far more portable to other postgresql instances.

I really do appreciate your response. It is also my nature to continue my 
research even after asking for help. However I find my answer, one validates 
the other. 

Thanks again!

/s/jr
Sent from my iPhone

> On Jul 3, 2017, at 18:21, Adrian Klaver  wrote:
> 
>> On 07/02/2017 10:33 AM, Jerry Regan wrote:
>> For reasons beyond my control, I am using Postgresql 9.4 on a MacBookPro 
>> (development system). I use pgadminIII and psql for clients (I tried and 
>> didn’t like the pgadmin4 UI; pg_dump, pg_restore also seem to be dumbed 
>> down).
>> My question:
>> I have some performance test results in table cor_duration_report. One 
>> column, c_entered_ion is of type timestamptz, another, c_scenario, is of 
>> type text. I want to calculate the difference between succeeding 
>> c_entered_ion rows to learn the rate at which entry events occur. In 
>> cor_duration_report, c_entered_ion columns are NOT in ascending sort order.
>> For a first attempt, I created another table cor_temp_gap as:
>>CREATE TABLE cor_temp_gap
>>(
>>   c_id serial NOT NULL,
>>   c_entered_ion timestamp with time zone NOT NULL,
>>   c_scenario text NOT NULL
>>)
>>WITH (
>>   OIDS=FALSE
>>);
>> and loaded it with:
>>INSERT into cor_temp_gap (c_entered_ion, c_scenario) SELECT
>>c_entered_ion, c_scenario from cor_duration_report order by
>>c_entered_ion;
>> The c_id column is loaded with the default value - the next sequence value.
>> I then generated my report with:
>>select count( gap ) as gaps, sum(gap) as sum,
>>mode() within group (order by gap) as mode,
>>percentile_disc(0.5) within group (order by gap) as  median,
>>avg( gap::integer ) as mean,
>>min( gap ) as min,
>>max( gap ) as max
>>from ( select extract( epoch from ( f.c_entered_ion -
>>s.c_entered_ion)::interval) * 1000 as gap
>>from cor_temp_gap s, cor_temp_gap f
>>where s.c_scenario = '20170628tc04'
>>and s.c_id+1 = f.c_id ) vals;
>> This seems to give me the results I want:
>>  gaps |   sum  | mode| median | mean 
>>| min |  max
>>--+-+--++---+-+
>>  307412 | 6872207 |1   |  8  | 22.3550381897908995
>>|   0 | 10846
>> The min value of zero is accurate. The mode value of 1 is reasonable, as is 
>> the median value of 8. Using a totally different method, the mean value is 
>> accurate, as is gaps (there are 307,413 rows in the table).
>> I do know enough sql to believe my cor_temp_gap table could probably be 
>> replace by a ‘WITH SELECT….’
>> I attempted this:
>>with cor_entry_time as ( select nextval('cor_temp_select_c_id_seq')
>>as c_id, c_entered_ion, c_scenario
>>from cor_duration_report where c_scenario = '20170628tc04' order by
>>c_entered_ion )
>>select count( gap ) as gaps,
>>sum(gap::integer) as sum,
>>mode() within group (order by gap) as mode,
>>percentile_disc(0.5) within group (order by gap) as  median,
>>avg( gap::integer ) as mean,
>>min( gap::integer ) as min,
>>max( gap::integer ) as max
>>from ( select extract( epoch from ( f.c_entered_ion -
>>s.c_entered_ion)::interval) * 1000 as gap
>>from cor_entry_time s, cor_entry_time f
>>where s.c_id+1 = f.c_id ) vals;
> 
> I used this site to reformat the above:
> 
> http://sqlformat.darold.net/
> 
> WITH cor_entry_time AS (
>SELECT
>nextval('cor_temp_select_c_id_seq') AS c_id,
>c_entered_ion,
>c_scenario
>FROM
>cor_duration_report
>WHERE
>c_scenario = '20170628tc04'
>ORDER BY
>c_entered_ion
> )
> SELECT
>count(gap) AS gaps,
>sum(gap::INTEGER) AS SUM,
>MODE ()
>WITHIN
> GROUP (
> ORDER BY
>gap) AS MODE,
> percentile_disc (0.5)
> WITHIN
> GROUP (
> ORDER BY
>gap) AS median,
> avg(gap::INTEGER) AS mean,
> min(gap::INTEGER) AS MIN,
> max(gap::INTEGER) AS MAX
> FROM (
>SELECT
>extract(EPOCH
>FROM (f.c_entered_ion - s.c_entered_ion)::interval) * 

Re: [SPAM] Re: [SPAM] Re: [GENERAL] Invalid field size

[Adrian Klaver]

On 07/04/2017 10:57 AM, Moreno Andreo wrote:

Il 04/07/2017 19:28, Tom Lane ha scritto:

Moreno Andreo  writes:

So the hint is to abandon manual COPY and let pg_dump do the hard work?

If it is a newline-conversion problem, compressed pg_dump archives would
be just as subject to corruption as your binary COPY file is.  I'd say
the hint is to be more careful about how you do the cross-machine file
transfers.  I suspect what is really happening is you're not always
doing that the same way, and that some of the methods allow a newline
conversion to happen to the file while others don't.

regards, tom lane


Well, I have no control on how the user transfers back and forth among 
machines.
Imagine you have a zip file where you backup your daily work. After 
you've done your backup, you put it on a pendrive and go home. When 
you're at home you copy this file to your computer and decompress it.
Our application works exactly the same way, except that it does not work 
with raw files, but with PostgreSQL data.
So I don't know how a user handles its backup files once he has made his 
backup...


Well that leads to four observations:

1) How the user handles their backup files is something that might need 
to be known.


2) By using your own backup code procedure you have taken possession of 
any resultant bugs:( The list might be able to help with those anyway, 
if it is possible for you to share the code you use to create the backups.


3) 1) and 2) could be moot if Daniel's hardware corruption theory is 
correct.


4) This is probably not going to be solved until you are able to access 
the actual file(s) in question.









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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 64bit initdb failure on macOS 10.11 and 10.12

[Tom Lane]

r...@bb-c.de (Rainer J.H. Brandt) writes:
> I got this initdb error for a 64bit-build on macOS El Capitan and Sierra:

> creating conversions ... FATAL:  could not load library 
> "/opt/bb/170704/lib/postgresql/ascii_and_mic.so": 
> dlopen(/opt/bb/170704/lib/postgresql/ascii_and_mic.so, 10): Symbol not found: 
> _check_encoding_conversion_args
> Referenced from: /opt/bb/170704/lib/postgresql/ascii_and_mic.so
> Expected in: /opt/bb/170704/bin/postgres
>in /opt/bb/170704/lib/postgresql/ascii_and_mic.so

check_encoding_conversion_args() should certainly be there in any PG
version released since 2009 (see src/backend/utils/mb/wchar.c).  But it's
unreferenced in the core Postgres executable, only in the loadable
conversion libraries.  I wonder if you have somehow enabled a link-time
optimization to remove "unreferenced" symbols, or at least not export them
to libraries.  Are you using Apple's stock toolchain, or something else?

> I configured with ./configure CC='gcc -m64' --prefix=/opt/bb/170704
> and also tried with CFLAGS=-m64 LDFLAGS=-m64.
> With the same procedures, but 32-bit-builds, I don't get this error,
> and everything works fine.  Unfortunately, I need the 64-bit version.

This makes little sense to me.  64-bit builds have been the default on
macOS for some time.

It's possible that by overriding LDFLAGS you're removing linker switches
that need to be there ...

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Invalid field size

[Tom Lane]

"Daniel Verite"  writes:
>   Tom Lane wrote:
>> If it is a newline-conversion problem, compressed pg_dump archives would
>> be just as subject to corruption as your binary COPY file is. 

> It's mentioned in [1] that the signature at the beginning of these files
> embed a CRLF to detect this newline-conversion problem early on,

Oh, I'd forgotten about that.

> so I would expect COPY IN to stumble on a corrupted signature
> and abort earlier in the process, if that conversion occurred.

Right.  I'm probably barking up the wrong tree, then.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] 64bit initdb failure on macOS 10.11 and 10.12

[Rainer J.H. Brandt]

Hi,

I got this initdb error for a 64bit-build on macOS El Capitan and Sierra:

initdb -D /data/pg/hawk -E UTF8 --locale=C"
The files belonging to this database system will be owned by user "rjhb".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /data/pg/hawk ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /data/pg/hawk/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... FATAL:  could not load library 
"/opt/bb/170704/lib/postgresql/ascii_and_mic.so": 
dlopen(/opt/bb/170704/lib/postgresql/ascii_and_mic.so, 10): Symbol not found: 
_check_encoding_conversion_args
  Referenced from: /opt/bb/170704/lib/postgresql/ascii_and_mic.so
  Expected in: /opt/bb/170704/bin/postgres
 in /opt/bb/170704/lib/postgresql/ascii_and_mic.so
STATEMENT:  CREATE OR REPLACE FUNCTION ascii_to_mic (INTEGER, INTEGER, CSTRING, 
INTERNAL, INTEGER) RETURNS VOID AS '$libdir/ascii_and_mic', 'ascii_to_mic' 
LANGUAGE C STRICT;

child process exited with exit code 1
initdb: removing contents of data directory "/data/pg/hawk"
Exit 1


This is reproducible with 9.4.5, 9.5.4, 9.6.3, and 10 beta.
I configured with ./configure CC='gcc -m64' --prefix=/opt/bb/170704
and also tried with CFLAGS=-m64 LDFLAGS=-m64.

With the same procedures, but 32-bit-builds, I don't get this error,
and everything works fine.  Unfortunately, I need the 64-bit version.

Does the error ring a bell somewhere?

Thanks, Rainer

-- 
Email: r...@bb-c.de
Telefon: 0172/9593205

Brandt & Brandt Computer GmbH
Am Wiesenpfad 6, 53340 Meckenheim
Geschäftsführer: Rainer J.H. Brandt und Volker A. Brandt
Handelsregister: Amtsgericht Bonn, HRB 10513


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Invalid field size

[Daniel Verite]

Tom Lane wrote:

> Moreno Andreo  writes:
> > So the hint is to abandon manual COPY and let pg_dump do the hard work?
> 
> If it is a newline-conversion problem, compressed pg_dump archives would
> be just as subject to corruption as your binary COPY file is. 

It's mentioned in [1] that the signature at the beginning of these files
embed a CRLF to detect this newline-conversion problem early on,
so I would expect COPY IN to stumble on a corrupted signature
and abort earlier in the process, if that conversion occurred.
Instead the report says it fails after a number of tuples:

> ERROR:  invalid field size
> CONTEXT:  COPY tab, line 619, column thumbnail

[1] https://www.postgresql.org/docs/current/static/sql-copy.htm 

The file header consists of 15 bytes of fixed fields, followed by a
variable-length header extension area. The fixed fields are:

Signature
  11-byte sequence PGCOPY\n\377\r\n\0 — note that the zero byte is a
  required part of the signature. (The signature is designed to allow
  easy identification of files that have been munged by a
  non-8-bit-clean transfer. This signature will be changed by
  end-of-line-translation filters, dropped zero bytes, dropped high
  bits, or parity changes.)
  ...


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [SPAM] Re: [SPAM] Re: [GENERAL] Invalid field size

[Moreno Andreo]

Il 04/07/2017 19:28, Tom Lane ha scritto:

Moreno Andreo  writes:

So the hint is to abandon manual COPY and let pg_dump do the hard work?

If it is a newline-conversion problem, compressed pg_dump archives would
be just as subject to corruption as your binary COPY file is.  I'd say
the hint is to be more careful about how you do the cross-machine file
transfers.  I suspect what is really happening is you're not always
doing that the same way, and that some of the methods allow a newline
conversion to happen to the file while others don't.

regards, tom lane


Well, I have no control on how the user transfers back and forth among 
machines.
Imagine you have a zip file where you backup your daily work. After 
you've done your backup, you put it on a pendrive and go home. When 
you're at home you copy this file to your computer and decompress it.
Our application works exactly the same way, except that it does not work 
with raw files, but with PostgreSQL data.
So I don't know how a user handles its backup files once he has made his 
backup...




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

[Jeff Janes]

On Mon, Jul 3, 2017 at 10:39 AM, rajan  wrote:

> Thanks, Jeff.
>
> Now I am going back to my old question.
>
> Even though *Session 2* fails to update with UPDATE 0 message, its txid is
> saved in xmax of updated(by *Session 1*) tuple.
>
> As it becomes an old txid, how come new txids are able to view it?
>

The database can see everything.  That is its job.  It constructs the
principles of ACID out of non-ACID components.  But once you use
pageinspect or select the system columns mxin and xmax, you start to peek
through that illusion.

Cheers,

Jeff

Re: [SPAM] Re: [SPAM] Re: [GENERAL] Invalid field size

[Tom Lane]

Moreno Andreo  writes:
> So the hint is to abandon manual COPY and let pg_dump do the hard work?

If it is a newline-conversion problem, compressed pg_dump archives would
be just as subject to corruption as your binary COPY file is.  I'd say
the hint is to be more careful about how you do the cross-machine file
transfers.  I suspect what is really happening is you're not always
doing that the same way, and that some of the methods allow a newline
conversion to happen to the file while others don't.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [SPAM] Re: [SPAM] Re: [GENERAL] Invalid field size

[Adrian Klaver]

On 07/04/2017 10:13 AM, Moreno Andreo wrote:

Il 04/07/2017 18:25, Adrian Klaver ha scritto:

On 07/04/2017 09:02 AM, Moreno Andreo wrote:

Il 04/07/2017 17:39, Adrian Klaver ha scritto:


So what you are saying is "in the last 5 years you've been 
extremely lucky?" :-)


Your original post went back and forth on whether you where lucky in 
the past:


"... that's been working well in the last 5 years (and it's still 
working, since this is a single, isolated case)"


"As for many error I got in the past I assume we are trying to COPY 
FROM corrupted data (when using cheap pendrives we get often this 
error)."
The bunch of errors I mention here is related to file management 
(issues with file copying or unzipping), sometines I had errors like 
"unrecognized Unicode character: 0xFF", and making a new backup 
always resolved the error.

This is the very first time we have this kind of error.


One could say your current error is just a variation of the above.

On the basis of what Daniel wrote, I think you're absolutely right.



If I had the source machine I'd try to make a new backup...


That would be a useful data point, though given the above if it 
succeeds it mainly proves Tom's point, that using BINARY in your 
situation is a hit and miss exercise.


Have you tried doing something like?:

pg_dump -d production -U postgres -t projection  -a > proj_txt.sql

pg_dump -d production -U postgres -t projection  -a  -Z 5 > 
proj_txt.sql.gz



l  -h proj_txt.sql*
-rw-r--r-- 1 aklaver users 3.2M Jul  4 09:23 proj_txt.sql
-rw-r--r-- 1 aklaver users 560K Jul  4 09:23 proj_txt.sql.gz


So the hint is to abandon manual COPY and let pg_dump do the hard work?


Not necessarily, you could modify your existing code to use the text COPY.


It means rewriting the whole backup logic, but if it has to be done, 
I'll manage to do it.


Thanks!
Moreno







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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [SPAM] Re: [SPAM] Re: [GENERAL] Invalid field size

[Moreno Andreo]

Il 04/07/2017 18:25, Adrian Klaver ha scritto:

On 07/04/2017 09:02 AM, Moreno Andreo wrote:

Il 04/07/2017 17:39, Adrian Klaver ha scritto:


So what you are saying is "in the last 5 years you've been 
extremely lucky?" :-)


Your original post went back and forth on whether you where lucky in 
the past:


"... that's been working well in the last 5 years (and it's still 
working, since this is a single, isolated case)"


"As for many error I got in the past I assume we are trying to COPY 
FROM corrupted data (when using cheap pendrives we get often this 
error)."
The bunch of errors I mention here is related to file management 
(issues with file copying or unzipping), sometines I had errors like 
"unrecognized Unicode character: 0xFF", and making a new backup 
always resolved the error.

This is the very first time we have this kind of error.


One could say your current error is just a variation of the above.

On the basis of what Daniel wrote, I think you're absolutely right.



If I had the source machine I'd try to make a new backup...


That would be a useful data point, though given the above if it 
succeeds it mainly proves Tom's point, that using BINARY in your 
situation is a hit and miss exercise.


Have you tried doing something like?:

pg_dump -d production -U postgres -t projection  -a > proj_txt.sql

pg_dump -d production -U postgres -t projection  -a  -Z 5 > 
proj_txt.sql.gz



l  -h proj_txt.sql*
-rw-r--r-- 1 aklaver users 3.2M Jul  4 09:23 proj_txt.sql
-rw-r--r-- 1 aklaver users 560K Jul  4 09:23 proj_txt.sql.gz


So the hint is to abandon manual COPY and let pg_dump do the hard work?
It means rewriting the whole backup logic, but if it has to be done, 
I'll manage to do it.


Thanks!
Moreno




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Invalid field size

[Moreno Andreo]

Il 04/07/2017 18:55, Daniel Verite ha scritto:

  I don't quite see from your posts whether that
particular file to import was tried and failed only once or retried
and failed again.
Only once, and until the user will not return from holidays I'll not be 
able to reproduce it.


Cheers
Moreno



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Invalid field size

[Daniel Verite]

Moreno Andreo wrote:

> So if it's the case (hardware error), recalling a new backup should 
> reproduce the error, right?

If the error happened when writing the file, I wouldn't expect
any other backup having the same error (assuming an error in
the bit-flip category).

And if it was a transient read error, a second run of the import
could even work. I don't quite see from your posts whether that
particular file to import was tried and failed only once or retried
and failed again.

Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_start/stop_backup non-exclusive scripts to snapshot

[hvjunk]

Hi there,

 I’ve previously done ZFS snapshot backups like this:

psql -c “select pg_start_backup(‘snapshot’);”
zfs snapshot TANK/postgresql@`date ‘+%Ymd’`
psql -c “select * from  pg_stop_backup();”

Reading the PostgreSQL9.6 documentation, the advice/future is to use the 
non-exclusive method, where I’ll need to keep a session *open* while the 
snapshot takes place, and after that I’ll have to issue the 
pg_stop_backup(false); in that active connection that issued the 
pg_start_backup(‘backup’,false,false);

How is this done inside a shell script?
Especially how to do error checking from the commands as psql -c “select 
pg_start_backup{‘test’,false,false);” not going to work?

Hendrik

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [SPAM] Re: [GENERAL] Invalid field size

[Adrian Klaver]

On 07/04/2017 09:02 AM, Moreno Andreo wrote:

Il 04/07/2017 17:39, Adrian Klaver ha scritto:


So what you are saying is "in the last 5 years you've been extremely 
lucky?" :-)


Your original post went back and forth on whether you where lucky in 
the past:


"... that's been working well in the last 5 years (and it's still 
working, since this is a single, isolated case)"


"As for many error I got in the past I assume we are trying to COPY 
FROM corrupted data (when using cheap pendrives we get often this 
error)."
The bunch of errors I mention here is related to file management (issues 
with file copying or unzipping), sometines I had errors like 
"unrecognized Unicode character: 0xFF", and making a new backup always 
resolved the error.

This is the very first time we have this kind of error.


One could say your current error is just a variation of the above.


If I had the source machine I'd try to make a new backup...


That would be a useful data point, though given the above if it succeeds 
it mainly proves Tom's point, that using BINARY in your situation is a 
hit and miss exercise.


Have you tried doing something like?:

pg_dump -d production -U postgres -t projection  -a > proj_txt.sql

pg_dump -d production -U postgres -t projection  -a  -Z 5 > proj_txt.sql.gz


l  -h proj_txt.sql*
-rw-r--r-- 1 aklaver users 3.2M Jul  4 09:23 proj_txt.sql
-rw-r--r-- 1 aklaver users 560K Jul  4 09:23 proj_txt.sql.gz










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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Invalid field size

[Adrian Klaver]

On 07/04/2017 08:37 AM, Moreno Andreo wrote:

Il 04/07/2017 17:25, Tom Lane ha scritto:

Moreno Andreo  writes:

Il 04/07/2017 16:51, Tom Lane ha scritto:
Pushing binary data around on Windows is always a hazardous 
proposition.

So what you are saying is "in the last 5 years you've been extremely
lucky?" :-)

Yup, particularly now that you mention moving the files between machines.
What did you do that with exactly?
Trying to answer your question (I hope I understood correctly, English 
is not my mother tongue)
What I do is, given a database, to COPY every table to a file, and then 
pack them up in one with a zip (except this table, that's been excluded 
from compression for its size and consequent compression time), so my 
backup is made up by 2 files, one with "normal data" and one with the 
result of COPYing this table to a file.


A question that comes while I'm writing: but pg_dump with custom format 
is not using COPY with binary format?


A quick look through the source indicates to me that it is not using 
BINARY. Then again I am not a C programmer, so take that into account. 
It would stand to reason that it would not use BINARY as using 
pg_dump/pg_restore is supposed to be portable across OS, machine 
architecture and to a certain degree Postgres versions. COPY WITH BINARY 
would work against that:


https://www.postgresql.org/docs/9.1/static/sql-copy.html

"The binary format option causes all data to be stored/read as binary 
format rather than as text. It is somewhat faster than the text and CSV 
formats, but a binary-format file is less portable across machine 
architectures and PostgreSQL versions.




Thanks
Moreno






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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Invalid field size

[Moreno Andreo]

Il 04/07/2017 17:42, Daniel Verite ha scritto:

Moreno Andreo wrote:


As you can see I have 2 bytea fields, blob and thumbnail (the one it
seems it's giving the error), but AFAIK the former is never used, so it
should be always null.
Googling around did not help.


Despite the auto-correction mechanisms in place in modern drives [1],
the probability of a non-correctable error is not negligible,
so it's plausible that it's what you're experiencing.

If that's the case and only byte is wrong in the whole file, you could
theorically fix it by finding the offset of the offending length and patch
the wrong byte with a 0xff value.


[1]
https://en.wikipedia.org/wiki/Hard_disk_drive#Error_rates_and_handling

So if it's the case (hardware error), recalling a new backup should 
reproduce the error, right?

When the user comes back from holidays I'll call him and check this.

Thanks
Moreno.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [SPAM] Re: [GENERAL] Invalid field size

[Moreno Andreo]

Il 04/07/2017 17:42, Adrian Klaver ha scritto:

On 07/04/2017 08:37 AM, Moreno Andreo wrote:

Il 04/07/2017 17:25, Tom Lane ha scritto:

Moreno Andreo  writes:

Il 04/07/2017 16:51, Tom Lane ha scritto:
Pushing binary data around on Windows is always a hazardous 
proposition.

So what you are saying is "in the last 5 years you've been extremely
lucky?" :-)
Yup, particularly now that you mention moving the files between 
machines.

What did you do that with exactly?
Trying to answer your question (I hope I understood correctly, 
English is not my mother tongue)


I believe what Tom was asking is what mechanism/tools do you use to 
move the 2 files below from one machine to another? 

Sorry :-)
Files are copied simply with Windows Explorer or the backup is directly 
written on the external device, if requested by the user.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [SPAM] Re: [GENERAL] Invalid field size

[Moreno Andreo]

Il 04/07/2017 17:39, Adrian Klaver ha scritto:

On 07/04/2017 08:19 AM, Moreno Andreo wrote:

Il 04/07/2017 16:51, Tom Lane ha scritto:

Moreno Andreo  writes:

I've implemented a backup procedure in C# with Npgsql (using COPY TO I
dump all tables in a compressed file) that's been working well in the
last 5 years (and it's still working, since this is a single, isolated
case).
OS: Windows 7
PG: 9.1.6 (I know, it's EOL, but I think it's not matter here)
[ got corrupted data with: ]
2017-07-04 12:55:27 CEST STATEMENT:  COPY
tab(cod,guid,data,blob,thumbnail,descr,type,url,user,home,codrec,table,op,dagg,last) 


FROM STDIN WITH BINARY
Pushing binary data around on Windows is always a hazardous 
proposition.

I'd bet something somewhere did a newline format conversion on your
data, either adding or removing CR characters.  There might not have
been any CR or LF bytes in the data fields proper, but it'd be quite
plausible for some of the length words used in binary-COPY format to
contain such bytes.

You might be better off using plain text COPY format; it can withstand
this sort of thing much better.

regards, tom lane

When we wrote this function, we first used plain COPY format, but we 
were not satisfied by the file size we got (too big compared to data 
size), so we switched to BINARY (I don't remember if there was also 
some performance matter involved).
So what you are saying is "in the last 5 years you've been extremely 
lucky?" :-)


Your original post went back and forth on whether you where lucky in 
the past:


"... that's been working well in the last 5 years (and it's still 
working, since this is a single, isolated case)"


"As for many error I got in the past I assume we are trying to COPY 
FROM corrupted data (when using cheap pendrives we get often this 
error)."
The bunch of errors I mention here is related to file management (issues 
with file copying or unzipping), sometines I had errors like 
"unrecognized Unicode character: 0xFF", and making a new backup always 
resolved the error.

This is the very first time we have this kind of error.
If I had the source machine I'd try to make a new backup...




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Invalid field size

[Daniel Verite]

Moreno Andreo wrote:

> As you can see I have 2 bytea fields, blob and thumbnail (the one it 
> seems it's giving the error), but AFAIK the former is never used, so it 
> should be always null.
> Googling around did not help.

In COPY BINARY, NULL is represented as -1 (all bits set)
in the 32-bit length word for the corresponding field.

So if any bit from this word except the bit sign would get flipped
by a hardware error, you'd get the error you mentioned because the
resulting length would come out as negative. From the source code:

  if (!CopyGetInt32(cstate, _size))
ereport(ERROR,
(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
 errmsg("unexpected EOF in COPY data")));
  if (fld_size == -1)
  {
*isnull = true;
return ReceiveFunctionCall(flinfo, NULL, typioparam, typmod);
  }
  if (fld_size < 0)
ereport(ERROR,
(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
 errmsg("invalid field size")));

Despite the auto-correction mechanisms in place in modern drives [1],
the probability of a non-correctable error is not negligible,
so it's plausible that it's what you're experiencing.

If that's the case and only byte is wrong in the whole file, you could
theorically fix it by finding the offset of the offending length and patch
the wrong byte with a 0xff value.


[1]
https://en.wikipedia.org/wiki/Hard_disk_drive#Error_rates_and_handling


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Invalid field size

[Adrian Klaver]

On 07/04/2017 08:37 AM, Moreno Andreo wrote:

Il 04/07/2017 17:25, Tom Lane ha scritto:

Moreno Andreo  writes:

Il 04/07/2017 16:51, Tom Lane ha scritto:
Pushing binary data around on Windows is always a hazardous 
proposition.

So what you are saying is "in the last 5 years you've been extremely
lucky?" :-)

Yup, particularly now that you mention moving the files between machines.
What did you do that with exactly?
Trying to answer your question (I hope I understood correctly, English 
is not my mother tongue)


I believe what Tom was asking is what mechanism/tools do you use to move 
the 2 files below from one machine to another?


What I do is, given a database, to COPY every table to a file, and then 
pack them up in one with a zip (except this table, that's been excluded 
from compression for its size and consequent compression time), so my 
backup is made up by 2 files, one with "normal data" and one with the 
result of COPYing this table to a file.


A question that comes while I'm writing: but pg_dump with custom format 
is not using COPY with binary format?


Thanks
Moreno






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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Invalid field size

[Adrian Klaver]

On 07/04/2017 08:19 AM, Moreno Andreo wrote:

Il 04/07/2017 16:51, Tom Lane ha scritto:

Moreno Andreo  writes:

I've implemented a backup procedure in C# with Npgsql (using COPY TO I
dump all tables in a compressed file) that's been working well in the
last 5 years (and it's still working, since this is a single, isolated
case).
OS: Windows 7
PG: 9.1.6 (I know, it's EOL, but I think it's not matter here)
[ got corrupted data with: ]
2017-07-04 12:55:27 CEST STATEMENT:  COPY
tab(cod,guid,data,blob,thumbnail,descr,type,url,user,home,codrec,table,op,dagg,last) 


FROM STDIN WITH BINARY

Pushing binary data around on Windows is always a hazardous proposition.
I'd bet something somewhere did a newline format conversion on your
data, either adding or removing CR characters.  There might not have
been any CR or LF bytes in the data fields proper, but it'd be quite
plausible for some of the length words used in binary-COPY format to
contain such bytes.

You might be better off using plain text COPY format; it can withstand
this sort of thing much better.

regards, tom lane

When we wrote this function, we first used plain COPY format, but we 
were not satisfied by the file size we got (too big compared to data 
size), so we switched to BINARY (I don't remember if there was also some 
performance matter involved).
So what you are saying is "in the last 5 years you've been extremely 
lucky?" :-)


Your original post went back and forth on whether you where lucky in the 
past:


"... that's been working well in the last 5 years (and it's still 
working, since this is a single, isolated case)"


"As for many error I got in the past I assume we are trying to COPY FROM 
corrupted data (when using cheap pendrives we get often this error)."





Thanks
Moreno.






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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Invalid field size

[Moreno Andreo]

Il 04/07/2017 17:25, Tom Lane ha scritto:

Moreno Andreo  writes:

Il 04/07/2017 16:51, Tom Lane ha scritto:

Pushing binary data around on Windows is always a hazardous proposition.

So what you are saying is "in the last 5 years you've been extremely
lucky?" :-)

Yup, particularly now that you mention moving the files between machines.
What did you do that with exactly?
Trying to answer your question (I hope I understood correctly, English 
is not my mother tongue)
What I do is, given a database, to COPY every table to a file, and then 
pack them up in one with a zip (except this table, that's been excluded 
from compression for its size and consequent compression time), so my 
backup is made up by 2 files, one with "normal data" and one with the 
result of COPYing this table to a file.


A question that comes while I'm writing: but pg_dump with custom format 
is not using COPY with binary format?


Thanks
Moreno



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Invalid field size

[Tom Lane]

Moreno Andreo  writes:
> Il 04/07/2017 16:51, Tom Lane ha scritto:
>> Pushing binary data around on Windows is always a hazardous proposition.

> So what you are saying is "in the last 5 years you've been extremely 
> lucky?" :-)

Yup, particularly now that you mention moving the files between machines.
What did you do that with exactly?

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Invalid field size

[Moreno Andreo]

Il 04/07/2017 16:51, Tom Lane ha scritto:

Moreno Andreo  writes:

I've implemented a backup procedure in C# with Npgsql (using COPY TO I
dump all tables in a compressed file) that's been working well in the
last 5 years (and it's still working, since this is a single, isolated
case).
OS: Windows 7
PG: 9.1.6 (I know, it's EOL, but I think it's not matter here)
[ got corrupted data with: ]
2017-07-04 12:55:27 CEST STATEMENT:  COPY
tab(cod,guid,data,blob,thumbnail,descr,type,url,user,home,codrec,table,op,dagg,last)
FROM STDIN WITH BINARY

Pushing binary data around on Windows is always a hazardous proposition.
I'd bet something somewhere did a newline format conversion on your
data, either adding or removing CR characters.  There might not have
been any CR or LF bytes in the data fields proper, but it'd be quite
plausible for some of the length words used in binary-COPY format to
contain such bytes.

You might be better off using plain text COPY format; it can withstand
this sort of thing much better.

regards, tom lane

When we wrote this function, we first used plain COPY format, but we 
were not satisfied by the file size we got (too big compared to data 
size), so we switched to BINARY (I don't remember if there was also some 
performance matter involved).
So what you are saying is "in the last 5 years you've been extremely 
lucky?" :-)


Thanks
Moreno.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [SPAM] Re: [GENERAL] Invalid field size

[Moreno Andreo]

Il 04/07/2017 16:36, Adrian Klaver ha scritto:

On 07/04/2017 04:16 AM, Moreno Andreo wrote:
I've implemented a backup procedure in C# with Npgsql (using COPY TO 
I dump all tables in a compressed file) that's been working well in 
the last 5 years (and it's still working, since this is a single, 
isolated case).


OS: Windows 7
PG: 9.1.6 (I know, it's EOL, but I think it's not matter here)


Are you restoring to same as above or to another machine or Postgres 
instance? 
Yes, that's what this function is intended to do... users can move their 
data from a computer to another one, having the application installed.

(OS: always windows, Postgresql: always 9.1.6)
Unfortunately I can't restore this file in the source machine (user is 
away).




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Invalid field size

[Tom Lane]

Moreno Andreo  writes:
> I've implemented a backup procedure in C# with Npgsql (using COPY TO I 
> dump all tables in a compressed file) that's been working well in the 
> last 5 years (and it's still working, since this is a single, isolated 
> case).
> OS: Windows 7
> PG: 9.1.6 (I know, it's EOL, but I think it's not matter here)
> [ got corrupted data with: ]
> 2017-07-04 12:55:27 CEST STATEMENT:  COPY 
> tab(cod,guid,data,blob,thumbnail,descr,type,url,user,home,codrec,table,op,dagg,last)
>  
> FROM STDIN WITH BINARY

Pushing binary data around on Windows is always a hazardous proposition.
I'd bet something somewhere did a newline format conversion on your
data, either adding or removing CR characters.  There might not have
been any CR or LF bytes in the data fields proper, but it'd be quite
plausible for some of the length words used in binary-COPY format to
contain such bytes.

You might be better off using plain text COPY format; it can withstand
this sort of thing much better.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Invalid field size

[Moreno Andreo]

  
  
Il 04/07/2017 16:30, Glyn Astill ha
  scritto:


  
>On Tuesday, 4 July 2017, 12:16:57 GMT+1, Moreno Andreo
 wrote:
>
>
> Any ideas? As for many error I got in the past I assume we
are trying to 
> COPY FROM corrupted data (when using cheap pendrives we get
often this 
> error). Should it be reasonable or I have to search
elsewhere?

I'd start by looking at the data on line 619 of your file,
perhaps you could post it?


  

Unfortunately no, because it's about 3 GB in size, and binary
encoded, so I have no idea of what I'd be searching for
Also it's not on my computer but on a customer's.
If needed, I can try to contact him and extract that line from the
file (not sure that in a binary encoded file I can exactly find
start and finish of a given line).

Thanks
Moreno.
  

Re: [GENERAL] Invalid field size

[Adrian Klaver]

On 07/04/2017 04:16 AM, Moreno Andreo wrote:
I've implemented a backup procedure in C# with Npgsql (using COPY TO I 
dump all tables in a compressed file) that's been working well in the 
last 5 years (and it's still working, since this is a single, isolated 
case).


OS: Windows 7
PG: 9.1.6 (I know, it's EOL, but I think it's not matter here)


Are you restoring to same as above or to another machine or Postgres 
instance?




While restoring (with COPY FROM) I get this error:
2017-07-04 12:55:27 CEST ERROR:  invalid field size
2017-07-04 12:55:27 CEST CONTEXT:  COPY tab, line 619, column thumbnail
2017-07-04 12:55:27 CEST STATEMENT:  COPY 
tab(cod,guid,data,blob,thumbnail,descr,type,url,user,home,codrec,table,op,dagg,last) 
FROM STDIN WITH BINARY


with this table definition:

CREATE TABLE public.tab
(
   cod uuid NOT NULL DEFAULT uuid_generate_v4(),
   guid uuid NOT NULL,
   data timestamp without time zone NOT NULL,
   blob bytea,
   thumbnail bytea,
   descr character varying(255) DEFAULT NULL::character varying,
   type character varying(50) DEFAULT NULL::character varying,
   url character varying(255) DEFAULT NULL::character varying,
   user character varying(255) DEFAULT NULL::character varying,
   home character varying(255) DEFAULT NULL::character varying,
   codrec uuid,
   table character varying(30) DEFAULT NULL::character varying,
   op character(1) DEFAULT NULL::bpchar,
   dagg timestamp without time zone,
   last character varying(16) DEFAULT NULL::character varying
)

As you can see I have 2 bytea fields, blob and thumbnail (the one it 
seems it's giving the error), but AFAIK the former is never used, so it 
should be always null.

Googling around did not help.

Any ideas? As for many error I got in the past I assume we are trying to 
COPY FROM corrupted data (when using cheap pendrives we get often this 
error). Should it be reasonable or I have to search elsewhere?


Thanks in advance
Moreno.






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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Invalid field size

[Glyn Astill]

>On Tuesday, 4 July 2017, 12:16:57 GMT+1, Moreno Andreo 
> wrote:
>
>
> Any ideas? As for many error I got in the past I assume we are trying to 
> COPY FROM corrupted data (when using cheap pendrives we get often this 
> error). Should it be reasonable or I have to search elsewhere?

I'd start by looking at the data on line 619 of your file, perhaps you could 
post it?

[GENERAL] Invalid field size

[Moreno Andreo]

I've implemented a backup procedure in C# with Npgsql (using COPY TO I 
dump all tables in a compressed file) that's been working well in the 
last 5 years (and it's still working, since this is a single, isolated 
case).


OS: Windows 7
PG: 9.1.6 (I know, it's EOL, but I think it's not matter here)

While restoring (with COPY FROM) I get this error:
2017-07-04 12:55:27 CEST ERROR:  invalid field size
2017-07-04 12:55:27 CEST CONTEXT:  COPY tab, line 619, column thumbnail
2017-07-04 12:55:27 CEST STATEMENT:  COPY 
tab(cod,guid,data,blob,thumbnail,descr,type,url,user,home,codrec,table,op,dagg,last) 
FROM STDIN WITH BINARY


with this table definition:

CREATE TABLE public.tab
(
  cod uuid NOT NULL DEFAULT uuid_generate_v4(),
  guid uuid NOT NULL,
  data timestamp without time zone NOT NULL,
  blob bytea,
  thumbnail bytea,
  descr character varying(255) DEFAULT NULL::character varying,
  type character varying(50) DEFAULT NULL::character varying,
  url character varying(255) DEFAULT NULL::character varying,
  user character varying(255) DEFAULT NULL::character varying,
  home character varying(255) DEFAULT NULL::character varying,
  codrec uuid,
  table character varying(30) DEFAULT NULL::character varying,
  op character(1) DEFAULT NULL::bpchar,
  dagg timestamp without time zone,
  last character varying(16) DEFAULT NULL::character varying
)

As you can see I have 2 bytea fields, blob and thumbnail (the one it 
seems it's giving the error), but AFAIK the former is never used, so it 
should be always null.

Googling around did not help.

Any ideas? As for many error I got in the past I assume we are trying to 
COPY FROM corrupted data (when using cheap pendrives we get often this 
error). Should it be reasonable or I have to search elsewhere?


Thanks in advance
Moreno.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general