Re: [GENERAL] Imperative Query Languages
On Wed, Jul 5, 2017 at 8:42 AM, Jason Dusek wrote: > > > If we imagine network databases have one layer: > > Imperative Plan > > And SQL databases have two: > > Declarative Query -> Imperative Plan > > It seems reasonable to say, LINQ, &al. have three: > > Imperative Syntax -> Declarative Query -> Imperative Plan > > Fortress is rather the same, since it translates imperative to functional > to assembly. > I am curious where you see LINQ as starting at an imperative syntax. Here's a good case that illustrates the problem I think. Suppose the following is understood imperatively: FOR x IN RANGE student SELECT WHERE x.age < 25 PROJECT ALL(x), lock_if_possible(x.id) Now, lock_if_possible has side effects. If we understand this to be imperative, then we have no possibility of turning this into a declarative query because we are interested in the side effects. So you cannot say that this is equivalent to the SQL of SELECT *, lock_if_possible(id) FROM student WHERE age < 25 The reason is that while the imperative version represents *one* valid interpretation of the declarative, there are other interpretations of the declarative that are not at all equivalent. The hoops we have to jump through to make this work in an imperative way in SQL are sometimes rather amusing. > > Kind Regards, > Jason > -- 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 writes: > 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. Right, and it's not the issue here. I always do each build in a freshly unpacked source tree. > 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? I thought so, too, but I'm beginning to doubt it. I'm now down to ./configure --prefix=/opt/bb/170705 and the initdb failure is the same. The build machine is a few months old and has current OS and Xcode, and certainly no other build tools or other relevant stuff. I have no special environment variables set. It's been a while since I built PG on macOS, but I regularly do on other operating systems, and haven't had any trouble in a very long time. 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] Strange case of database bloat
On Wed, Jul 5, 2017 at 7:18 AM, Chris Travers wrote: > 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? > Two points I think I forgot to mention: This is PostgreSQL 9.5.1 Last I saw something similar was a more "minor" case on a larger db, on PostgreSQL 9.3.x The more minor case was a small table (maybe 20k rows) which had bloated to 1GB in size due to this same sort of problem but we ignored it because the table was cached all the time and at the RAM we were using, it wasn't a significant drain on performance. However, here it is. First 20 CTIDs from one table: (35,25) (48,15) (76,20) (77,20) (83,20) (96,19) (100,19) (103,13) (111,9) (115,12) (124,11) (120,12) (131,12) (137,12) (150,14) (152,12) (157,20) (162,14) > -- > Best Wishes, > Chris Travers > > Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor > lock-in. > http://www.efficito.com/learn_more > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more
Re: [GENERAL] Imperative Query Languages
On Tue, 4 Jul 2017 at 23:01 Tom Lane wrote: > I'm pretty sure that that is the model that relational databases (and the > SQL language in particular) replaced, back in the 70s or so. Look up > "network" databases (eg CODASYL) and "hierarchical" DBs (eg IMS) for some > ancient history here. Yeah, you can do it like that, but it's seriously > painful to develop and maintain. People were more excited about spending > human effort to save machine cycles forty years ago than they are today. Network database programming is, indeed, imperative; but as I understand it there was not much of a planning layer -- the program was the plan. In C#, one has LINQ; and in Scala and Haskell, monadic comprehensions; and even in Python one can overload iteration to allow a translation of imperative syntax to declarative syntax. The goal with these features, is generally to present a familiar interface to an unfamiliar semantics. If we imagine network databases have one layer: Imperative Plan And SQL databases have two: Declarative Query -> Imperative Plan It seems reasonable to say, LINQ, &al. have three: Imperative Syntax -> Declarative Query -> Imperative Plan Fortress is rather the same, since it translates imperative to functional to assembly. Kind Regards, Jason
Re: [GENERAL] Imperative Query Languages
On Wed, Jul 5, 2017 at 8:34 AM, Jason Dusek wrote: > > > I can not argue these points with you; but Fortress is a good example of > imperative looking code that translates to a functional/declarative core; > as indeed is monadic or applicative code. LINQ is a more recent and > widespread example -- though not encompassing an entire language -- of > something that has an imperative form while being declarative under the > hood. Scala's for comprehensions -- more or less monad comprehensions --are > another. > But Linq effectively is a declarative language that's semi-SQL-like (I wish they used "project" instead of "select" but that's another question). I don't see Linq as semi-imperative. > > With regards to Spark, I assume for comprehensions are an important part > of the interface? > Nope. You have chained generators and you really need to watch what is parallelizable and what is not, and what is running on the partitions and what is running post-gathering/shuffling. Spark has no real facility for parallelising a comprehension. > > Kind Regards, > Jason > >> -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more
Re: [GENERAL] Imperative Query Languages
On Tue, 4 Jul 2017 at 23:22 Chris Travers wrote: > Having done a lot of SQL optimisation stuff I have doubts that this is > possible. The problem is that it is much easier to go from a declarative > to an imperative plan than it is to go the other way. In fact sometimes we > use SQL the way your first code works and then it is often a problem. > > For example, consider the difference between an EXISTS and an IN query, or > between an INNER JOIN and a LATERAL JOIN. PostgreSQL's optimiser is > amazing at identifying cases where these are equivalent and planning > accordingly, but it is extremely easy to get just outside the envelope > where the optimiser gives up and has to default back to an imperative > interpretation of these. Proving that two imperative approaches are > equivalent is a lot harder than proving that two different imperative > approaches implement the same declarative request. In other words, going > imperative -> declarative strikes me as a far, far harder problem than the > other way. > > Also I have done a little bit of work on Apache Spark and there it is > extremely important to understand the imperative side of the data flow in > that case (what is partitioned and what is not). > I can not argue these points with you; but Fortress is a good example of imperative looking code that translates to a functional/declarative core; as indeed is monadic or applicative code. LINQ is a more recent and widespread example -- though not encompassing an entire language -- of something that has an imperative form while being declarative under the hood. Scala's for comprehensions -- more or less monad comprehensions --are another. With regards to Spark, I assume for comprehensions are an important part of the interface? Kind Regards, Jason >
Re: [GENERAL] Imperative Query Languages
On Wed, Jul 5, 2017 at 7: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. > Having done a lot of SQL optimisation stuff I have doubts that this is possible. The problem is that it is much easier to go from a declarative to an imperative plan than it is to go the other way. In fact sometimes we use SQL the way your first code works and then it is often a problem. For example, consider the difference between an EXISTS and an IN query, or between an INNER JOIN and a LATERAL JOIN. PostgreSQL's optimiser is amazing at identifying cases where these are equivalent and planning accordingly, but it is extremely easy to get just outside the envelope where the optimiser gives up and has to default back to an imperative interpretation of these. Proving that two imperative approaches are equivalent is a lot harder than proving that two different imperative approaches implement the same declarative request. In other words, going imperative -> declarative strikes me as a far, far harder problem than the other way. Also I have done a little bit of work on Apache Spark and there it is extremely important to understand the imperative side of the data flow in that case (what is partitioned and what is not). -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more
Re: [GENERAL] Imperative Query Languages
Jason Dusek writes: > 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 I'm pretty sure that that is the model that relational databases (and the SQL language in particular) replaced, back in the 70s or so. Look up "network" databases (eg CODASYL) and "hierarchical" DBs (eg IMS) for some ancient history here. Yeah, you can do it like that, but it's seriously painful to develop and maintain. People were more excited about spending human effort to save machine cycles forty years ago than they are today. 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] Imperative Query Languages
(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
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
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
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
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
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
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
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
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
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
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) * 1000 AS gap > FROM >cor_
Re: [SPAM] Re: [SPAM] Re: [GENERAL] Invalid field size
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
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
"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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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, &fld_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
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
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
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
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
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
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
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
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
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
>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
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