[GENERAL] ERROR: invalid input syntax for integer: ""
G'day,
I hope to be shown to be an idiot, but we are receiving the message
ERROR: invalid input syntax for integer: ""
when using a pl/pgsl function with some quite complex queries that seem to be
working on a developer machine using postgresql 9.1.6, but not on the
production machine using 9.1.7.
The source of our confusion is stemming from the fact that the offending line
(being the join predicate that if removed allows the query to work) is
comparing two values in two tables:
...
FROM reports rep
LEFT JOIN results res
ON res.reportid = rep.id <== this line is causing the error to be
returned
AND res.resulttypeid = 108
AND res.del = false
…
I have included the full query executed by the function at the bottom of the
email.
In the first it is an integer primary key, in the second a not null integer,
as shown below:
Table "data.reports"
Column | Type | Modifiers
---+--+--
id| integer | not null default
nextval('reports_id_seq'::regclass)
projectid | integer |
…
Indexes:
"reports_pkey" PRIMARY KEY, btree (id)
Table "data.results"
Column | Type | Modifiers
---+--+--
id| integer | not null default
nextval('results_id_seq'::regclass)
reportid | integer | not null
…
Indexes:
"results_pkey" PRIMARY KEY, btree (id)
"results_del_btree" btree (del)
"results_idx_reportid" btree (reported)
My questions then are :
Given that the join is between two integer columns, how could it be an invalid
syntax for one of them?
Given the query is working on one machine (using a copy of the database
downloaded and imported from the second machine last night) running 9.1.6, is
there any reason it wouldn't work on the original machine - have there been any
changes in casting that I didn't notice between 9.1.6 and 9.1.7?
cheers
Ben
The full query is :
{{{
SELECT rep.id, res8.reportid,
round(st_distance_sphere('010120BB108716D9CEF7A36240643BDF4F8DA741C0',
post.the_point::geometry)/1000) as dist
FROM reports rep
LEFT JOIN users u ON rep.link = u.id
LEFT JOIN postcodes post ON u.postcode::integer = post.postcode
LEFT JOIN species spe ON rep.species::text like spe.speccode::text AND
spe.synonym = 0
LEFT JOIN results res8 ON res8.reportid = rep.id AND res8.resulttypeid = 108
AND res8.del = false
LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid = 108 AND rlu8.id =
res8.resultvalue::int
WHERE rep.del IS false AND rep.projectid = 51
AND round(st_distance_sphere(
'010120BB1000008716D9CEF7A36240643BDF4F8DA741C0', post.the_point)/1000) <
'150' AND spe.id = '9465' AND rlu8.id = '935';
}}}
--
Ben Madin
m : +61 448 887 220
e : b...@ausvet.com.au
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ERROR: invalid input syntax for integer: "" - more confusion
G'day again, inconceivably, on a completely different issue, I've run into the above error again - this time on both machines, one running 9.1.6, and the other running 9.1.7. This time, I have a table with locations, some states (level = 1) and some shires (level = 2). level is defined as an integer type (no Modifiers or indexes) The distribution of these values is best shown by : SELECT level, count(*) FROM locations GROUP BY level ORDER BY level; level | count ---+--- 1 |18 2 | 876 (2 rows) If I run this query : SELECT l.id, l.location, l.abbrev, l.locationcode FROM locations l WHERE l.level = 2 ORDER BY split_part(locationcode, '.', 1)::int, split_part(locationcode, '.', 2)::int; I get many hundreds of results in the correct order. If I change the level to 1: SELECT l.id, l.location, l.abbrev, l.locationcode FROM locations l WHERE l.level = 1 ORDER BY split_part(locationcode, '.', 1)::int, split_part(locationcode, '.', 2)::int; I get: ERROR: invalid input syntax for integer: "" even more confusing, if I take away the ORDER BY clause, it works. Do I have some corruption somewhere? I have done a dump / reload, any other suggestions? cheers Ben -- Ben Madin m : +61 448 887 220 e : b...@ausvet.com.au -- 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] ERROR: invalid input syntax for integer: ""
Thanks Adrian, On 2013-02-06, at 12:40 , Adrian Klaver wrote: > I am not seeing anything obvious. > Is there a chance the import to the second machine did not go well? Actually, these queries work on the machine that the import was done to - but not the original. However, not all is well on the development machine, I've sent a further email. > Have you looked at the values for id and reportid to see if they look alright? Yes, those I've scanned seem OK - I've tried some tests looking for nulls etc. (there are several million reports, and about 30 times as many results, hence I'm not posting a reproducible example - yet!) cheers Ben -- Ben Madin m : +61 448 887 220 e : b...@ausvet.com.au -- 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] ERROR: invalid input syntax for integer: "" - more confusion
G'day Adrian, On 2013-02-06, at 12:44 , Adrian Klaver wrote: > This one I could see if the split_part yielded an empty string. You are right on this one - the higher level components don't have a split part 2. Thank you. > > What type is locationcode and could you provide an example? Varchar an example would be 6 for level 1 field, and 6.34 for level 2. cheers Ben -- Ben Madin t : +61 8 6102 5535 m : +61 448 887 220 e : b...@ausvet.com.au AusVet Animal Health Services P.O. Box 5467 Broome WA 6725 Australia AusVet's website: http://www.ausvet.com.au This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this email are the opinion of the writer only and are not endorsed by AusVet Animal Health Services unless expressly stated otherwise. Although AusVet uses virus scanning software we do not accept liability for viruses or similar in any attachments. Thanks for reading. -- 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] ERROR: invalid input syntax for integer: ""
Thanks Adrian,
On 2013-02-06, at 12:52 , Adrian Klaver wrote:
> On 02/05/2013 08:24 PM, Ben Madin wrote:
>> The full query is :
>>
>> {{{
>> SELECT rep.id, res8.reportid,
>> round(st_distance_sphere('010120BB108716D9CEF7A36240643BDF4F8DA741C0',
>> post.the_point::geometry)/1000) as dist
>> FROM reports rep
>> LEFT JOIN users u ON rep.link = u.id
>> LEFT JOIN postcodes post ON u.postcode::integer = post.postcode
>> LEFT JOIN species spe ON rep.species::text like spe.speccode::text AND
>> spe.synonym = 0
>> LEFT JOIN results res8 ON res8.reportid = rep.id AND res8.resulttypeid = 108
>> AND res8.del = false
>> LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid = 108 AND rlu8.id =
>> res8.resultvalue::int
>> WHERE rep.del IS false AND rep.projectid = 51
>> AND round(st_distance_sphere(
>> '010120BB108716D9CEF7A36240643BDF4F8DA741C0', post.the_point)/1000)
>> < '150' AND spe.id = '9465' AND rlu8.id = '935';
>> }}}
>>
>
>
> Follow up questions:
>
> 1) Where is this query being run from?
It is meant to be being executed in a pl/pgsql function as part of a loop - the
rep.id is then used to return the corresponding rows. This function is working
on the dev machine. The query I have appended is produced in the function as
below. When I throw the query as above at the psql command line, it works on
the dev machine. (but not on the production box). The final part of the
function looks like :
{{{
RAISE NOTICE 'The final query is : %', querystring;
FOR repid, dist IN EXECUTE querystring LOOP
RETURN QUERY SELECT reportid, surname, city, state, postcode, telephone,
species, breed, status, dist FROM data_view WHERE reportid = repid;
END LOOP;
RETURN;
}}}
> 2) Why are the integers at the end of the query quoted?
I have quote_literal(speciesid) etc, even thought it is an int parameter to the
query. I realise it isn't needed, but it was working on one. FWIW, I have tried
it without all of the quotes (manually removed), but it doesn't make any
difference to the result.
cheers
Ben
--
Ben Madin
t : +61 8 6102 5535
m : +61 448 887 220
e : b...@ausvet.com.au
AusVet Animal Health Services
P.O. Box 5467
Broome WA 6725
Australia
AusVet's website: http://www.ausvet.com.au
This transmission is for the intended addressee only and is confidential
information. If you have received this transmission in error, please delete it
and notify the sender. The contents of this email are the opinion of the writer
only and are not endorsed by AusVet Animal Health Services unless expressly
stated otherwise. Although AusVet uses virus scanning software we do not accept
liability for viruses or similar in any attachments. Thanks for reading.
--
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] ERROR: invalid input syntax for integer: ""
Adrian,
On 2013-02-06, at 13:33 , Adrian Klaver wrote:
> Dim bulb moment.
>
> What happens if you run a simplified version of the query?
>
> One that just LEFT JOINS reports to results ON reportid=rep.id.
A fair question - it only makes it more confusing :
{{{
SELECT rep.id, res8.reportid
FROM reports rep
LEFT JOIN results res8 ON res8.reportid = rep.id AND res8.resulttypeid = 108
AND res8.del = false
LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid = 108 AND rlu8.id =
res8.resultvalue::int
WHERE rep.del IS false
AND rep.projectid = 51
AND rlu8.id = '935';
}}}
works perfectly well - so does:
{{{
SELECT rep.id,
round(st_distance_sphere('010120BB108716D9CEF7A36240643BDF4F8DA741C0',
post.the_point::geometry)/1000) as dist
FROM reports rep
LEFT JOIN users u ON rep.link = u.id
LEFT JOIN postcodes post ON u.postcode::integer = post.postcode
LEFT JOIN species spe ON rep.species::text like spe.speccode::text AND
spe.synonym = 0
WHERE rep.del IS false
AND rep.projectid = 51
AND round(st_distance_sphere(
'010120BB108716D9CEF7A36240643BDF4F8DA741C0', post.the_point)/1000) <
'150'
AND spe.id = '9465';
}}}
but the combination only works on the older db…
{{{
SELECT rep.id, res.reportid,
round(st_distance_sphere('010120BB108716D9CEF7A36240643BDF4F8DA741C0',
post.the_point::geometry)/1000) as dist
FROM reports rep
LEFT JOIN users u ON rep.link = u.id
LEFT JOIN postcodes post ON u.postcode::integer = post.postcode
LEFT JOIN species spe ON rep.species::text like spe.speccode::text AND
spe.synonym = 0
LEFT JOIN results res ON res.reportid = rep.id AND res.resulttypeid = 108 AND
res.del = false
LEFT JOIN resultlookup rlu ON rlu.resulttypesid = 108 AND rlu.id =
res.resultvalue::int
WHERE rep.del IS false
AND rep.projectid = 51
AND round(st_distance_sphere(
'010120BB108716D9CEF7A36240643BDF4F8DA741C0', post.the_point)/1000) <
'150'
AND spe.id = '9465'
AND rlu.id = '935';
ERROR: invalid input syntax for integer: ""
}}}
cheers
Ben
--
Ben Madin
t : +61 8 6102 5535
m : +61 448 887 220
e : b...@ausvet.com.au
AusVet Animal Health Services
P.O. Box 5467
Broome WA 6725
Australia
AusVet's website: http://www.ausvet.com.au
This transmission is for the intended addressee only and is confidential
information. If you have received this transmission in error, please delete it
and notify the sender. The contents of this email are the opinion of the writer
only and are not endorsed by AusVet Animal Health Services unless expressly
stated otherwise. Although AusVet uses virus scanning software we do not accept
liability for viruses or similar in any attachments. Thanks for reading.
--
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] ERROR: invalid input syntax for integer: ""
Thanks Tom,
On 2013-02-06, at 13:42 , Tom Lane wrote:
> The only part of this query that looks like it could possibly produce
> that error is the res8.resultvalue-to-int cast:
>> LEFT JOIN results res8 ON res8.reportid = rep.id AND res8.resulttypeid = 108
>> AND res8.del = false
>> LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid = 108 AND rlu8.id =
>> res8.resultvalue::int
>
> ^
> Presumably, there are some empty strings in results.resultvalue, and if
> the query happens to try to compare one of them to rlu8.id, kaboom.
Yes - this would be the case if it tried to match it against the resultvalue
only - some of the values in the table are NULL, but not for this resulttypeid.
So my understanding, working left to right was that the res.8 table rows should
be limited to those rows which have a resulttypeid = 108. These all have
numeric values, vis :
select distinct resultvalue from results where resulttypeid = 108 order by
resultvalue;
resultvalue
-
932.0
933.0
934.0
935.0
936.0
937.0
938.0
939.0
940.0
3224.0
(10 rows)
and it should then be only these rows that are joined to the resultlookup
table… but it seems that the rlu8.id = res8.resultvalue is being done first.
Can I prevent that? Using a subquery, or a some other approach.
> The way that the error comes and goes depending on seemingly-irrelevant
> changes isn't too surprising. Probably what's happening is that the
> query plan changes around so that that test occurs earlier or later
> relative to other join clauses.
That might just be it - the query explain is different for the same query on
each machine.
Just to confuse the issue, if I take the resultlookup table out completely, I
still get the same error. So maybe it isn't that join at all that is raising
the error.
If I take the results table out… it works(the commented code below being the
change.)
SELECT rep.id, --res.reportid,
round(st_distance_sphere('010120BB108716D9CEF7A36240643BDF4F8DA741C0',
post.the_point)/1000) as dist
FROM reports rep
LEFT JOIN users u ON rep.link = u.id
LEFT JOIN postcodes post ON u.postcode::integer = post.postcode
LEFT JOIN species spe ON rep.species::text like spe.speccode::text AND
spe.synonym = 0
--LEFT JOIN results res ON rep.id = res.reportid AND res.resulttypeid = 108
AND res.del is false
WHERE rep.del IS false AND rep.projectid = 51
AND round(st_distance_sphere(
'010120BB108716D9CEF7A36240643BDF4F8DA741C0', post.the_point)/1000) <
150
AND spe.id = 9465;
I'm really not sure what to do here.
cheers
Ben
--
Ben Madin
m : +61 448 887 220
e : b...@ausvet.com.au
--
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] ERROR: invalid input syntax for integer: ""
Thank you to all for your help on this problem. I've summarised the resolution in the hope that it might help someone else. With all the advice I have gone forward and discovered that the issue related to a postcode anomaly. A client had provided a new postbox postcode (the application normally prevents this for postboxes because we can't locate properties, but because it was new - and our database didn't have a record of it - this check had been bypassed). This meant there was no geometry associated with the postcode, and when it was joined to the postcodes table (which has varchars for postcodes because in Australia some postcodes begin with 0, which needs to be printed to allow automatic sorting) during the distance checking function (which looked like this in pl/pgsql): round(st_distance_sphere( '$$ || pccentre || $$', post.the_point)/1000) If a geometry is NULL, the st_distance_sphere postgis function returned NULL. NULL/1000 = NULL round(NULL) = NULL AND NULL < 150 = NULL so the predicate probably looks like: AND round(NULL/1000) < 150 AND NULL, so no row returned. This can't be used in a comparison, so to get around this (thanks Tom) : coalesce(round(st_distance_sphere( '$$ || pccentre || $$', post.the_point)/1000),0) < $$ || quote_literal(distance); which works - problem no longer being seen. My final throught relates to the message: ERROR: invalid input syntax for integer: '' The '' suggests (I don't think I was the only one who thought this) that we were looking for a string comparison. I guess the NULL value is in there between the quotes. cheers Ben On 2013-02-07, at 00:01 , Tom Lane wrote: > Ben Madin writes: >> On 2013-02-06, at 13:42 , Tom Lane wrote: >>> The only part of this query that looks like it could possibly produce >>> that error is the res8.resultvalue-to-int cast: > >>>> LEFT JOIN results res8 ON res8.reportid = rep.id AND res8.resulttypeid = >>>> 108 AND res8.del = false >>>> LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid = 108 AND rlu8.id = >>>> res8.resultvalue::int >>> >>> ^ >>> Presumably, there are some empty strings in results.resultvalue, and if >>> the query happens to try to compare one of them to rlu8.id, kaboom. > >> Yes - this would be the case if it tried to match it against the resultvalue >> only - some of the values in the table are NULL, but not for this >> resulttypeid. > > NULLs are not the problem (casting a NULL to anything is still a NULL). > The problem you've got is with empty strings, which are not at all the > same thing, even if Oracle can't tell the difference. > >> So my understanding, working left to right was that the res.8 table rows >> should be limited to those rows which have a resulttypeid = 108. > > Please recall the section in the fine manual where it points out that > WHERE clauses are not evaluated left-to-right. In the case at hand > I think the planner may be able to rearrange the join order, such that > the rlu8 join is done first. Now, having said that, I'm not real sure > why the res8.resulttypeid = 108 clause couldn't be applied at scan level > not join level. But you really need to be looking at EXPLAIN output > rather than theorizing about what order the clauses will be checked in. > >> I'm really not sure what to do here. > > You need to make sure the join clause is safe to evaluate for any data > present in the table. The first question I'd ask is why isn't > resultvalue of a numeric type to start with --- this whole problem > smells of crummy schema design. Or at least, why can't you use NULL > for the offending values instead of empty strings. If you really can't > fix the data representation, you need to complicate the join clause to > make it not try to convert non-integral strings to ints. One possible > solution is "nullif(res8.resultvalue, '')::int", if empty strings are > the only hazard. If they're not, you could do something with a CASE > expression using a regex test on the string... > > regards, tom lane -- Ben Madin t : +61 8 6102 5535 m : +61 448 887 220 e : b...@ausvet.com.au AusVet Animal Health Services P.O. Box 5467 Broome WA 6725 Australia AusVet's website: http://www.ausvet.com.au This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this email are the opinion of the writer only and are not endorsed by AusVet Animal Health Services unless expressly stated otherwise. Although AusVet uses virus scanning software we do not accept liability for viruses or similar in any attachments. Thanks for reading. -- 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] Restoring a database dump from 9.0 to 9.2
If Tom's suggestion doesn't work, can you do your text dump by schema, or for a subset of tables, and see if you can isolate the problem table. (using the -n or -t options) Have you changed the locale / languages settings between db versions? If you find a quoting problem in a very large table you can run it through sed to quote the offending bits If you can dump from 9.0 and restore into 9.1, can you dump from 9.1 and try restoring it into 9.2? Cheers Ben -- Ben Madin t: +61 8 6102 5535 m: +61 448 887 220 Sent from my iPhone, hence the speling... On 09/02/2013, at 4:46, Jay McGaffigan wrote: > Hi, > I've been trying to restore a fairly sizeable database dump from my > production server onto my dev box. > Recently upgraded to 9.2.2 and wanted to try it out. > > So I grabbed a text dump of the database and tried the "Createdb dbname; psql > < dmpfile" way of restoring that's always worked for me before upgrading my > dev box and I'm getting errors on import. Some of my columns have 'rich > text' (carriage returns, XML and other markup) in it and I suspect they are > causing the issues (basically the errors I'm seeing seem to imply that the > text formatting is getting out of wack I'm suspecting due to carriage returns > embedded in the dump file).This causes lots of errors as the processing > of the file is now out of sync. > > I had been able to load this same file under PSql 9.1. > > If I get a binary dump file that I need to use something like pg_restore with > . it runs for over 12 hrs locks up my mac adn uses all system memory (i've > 16G RAM on my system) > > This db is like 30G in size. > > Any one have any debugging advice? I'm thinking if I can use the text based > dump that is created with proper escaping then things might work. But so far > reading documentation I haven't really figured out if this is a viable path. > > If this is not a good way to do it I'm open for any and all suggestions. > > Thanks! > Jay -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Installing PostgreSQL 10 on Mac OSX Undefined Symbol _heap_modify_tuple_by_cols
G'day, we are quite excited about the parallelisation enhancements, and keen to try, but trying to build (using the same configure as we have used for 9.6) is giving some warnings and errors. The detail is below, but the oddity I'm really wondering about is the reference in the command to /usr/local/pgsql965/... this was my current 9.6 install (which went smoothly) - but why is it being referenced in the make for 10...? Is this looking for an existing environment variable (which seems unlikely for a build process) or is something else unusual? I am still on Sierra (Darwin orion.local 16.7.0 Darwin Kernel Version 16.7.0: Thu Jun 15 17:36:27 PDT 2017; root:xnu-3789.70.16~2/RELEASE_X86_64 x86_64) and using this configure: ./configure --prefix=/usr/local/pgsql-10 --with-extra-version=BM --with-python --with-openssl --with-bonjour --with-uuid=e2fs --with-libxml --with-libxslt PYTHON=/Library/Frameworks/Python.framework/Versions/3.6/bin/python3 and the build failure ends with: /Applications/Xcode.app/Contents/Developer/usr/bin/make -C ../../../contrib/spi gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 -arch x86_64 -DREFINT_VERBOSE -I. -I./ -I/usr/local/pgsql965/include/server -I/usr/local/pgsql965/include/internal -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.13.sdk/usr/include/libxml2 -I/usr/local/include -c -o autoinc.o autoinc.c autoinc.c:116:14: warning: implicit declaration of function 'heap_modify_tuple_by_cols' is invalid in C99 [-Wimplicit-function-declaration] rettuple = heap_modify_tuple_by_cols(rettuple, tupdesc, ^ autoinc.c:116:12: warning: incompatible integer to pointer conversion assigning to 'HeapTuple' (aka 'struct HeapTupleData *') from 'int' [-Wint-conversion] rettuple = heap_modify_tuple_by_cols(rettuple, tupdesc, ^ 2 warnings generated. gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 -arch x86_64 -L/usr/local/pgsql965/lib -L/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.13.sdk/usr/lib -L/usr/local/lib -Wl,-dead_strip_dylibs -arch x86_64 -L/usr/local/pgsql965/lib/pgxs/src/makefiles/../../src/port -lpgport -bundle -bundle_loader /usr/local/pgsql965/bin/postgres -o autoinc.so autoinc.o ld: warning: directory not found for option '-L/usr/local/pgsql965/lib/pgxs/src/makefiles/../../src/port' Undefined symbols for architecture x86_64: "_heap_modify_tuple_by_cols", referenced from: _autoinc in autoinc.o ld: symbol(s) not found for architecture x86_64 clang: error: linker command failed with exit code 1 (use -v to see invocation) make[3]: *** [autoinc.so] Error 1 make[2]: *** [submake-contrib-spi] Error 2 make[1]: *** [all-test/regress-recurse] Error 2 make: *** [all-src-recurse] Error 2 cheers Ben -- Ben Madin m : +61 448 887 220 w : +61 8 7200 7220 e : b...@ausvet.com.au 5 Shuffrey Street, Fremantle Western Australia on the web: www.ausvet.com.au This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this email are the opinion of the writer only and are not endorsed by Ausvet unless expressly stated otherwise. Although Ausvet uses virus scanning software we do not accept liability for viruses or similar in any attachments.
Re: [GENERAL] Installing PostgreSQL 10 on Mac OSX Undefined Symbol _heap_modify_tuple_by_cols
G'day Tom, Thanks for the feedback. I couldn't find anywhere that suggested that I had set the CPPFLAGS or CFLAGS environment variables, so I removed the following lines from my profile: #export USE_PGXS=1 #export PG_LIB_DIR="/usr/local/pgsql/lib" #export PG_CONFIG="/usr/local/pgsql/bin/pg_config" #export PGDATA="/usr/local/pgsql/data" and tried again - worked a treat! I haven't tried to work out which of these lines caused the problem, but hopefully if anyone else has a similar problem they might benefit! cheers Ben On 24 October 2017 at 02:43, Tom Lane wrote: > Ben Madin writes: > > we are quite excited about the parallelisation enhancements, and keen to > > try, but trying to build (using the same configure as we have used for > 9.6) > > is giving some warnings and errors. > > Something's definitely messed up there: > > > gcc -Wall -Wmissing-prototypes -Wpointer-arith > > -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute > > -Wformat-security -fno-strict-aliasing -fwrapv > > -Wno-unused-command-line-argument -O2 -arch x86_64 -DREFINT_VERBOSE -I. > > -I./ -I/usr/local/pgsql965/include/server > > -I/usr/local/pgsql965/include/internal > > -I/Applications/Xcode.app/Contents/Developer/Platforms/ > MacOSX.platform/Developer/SDKs/MacOSX10.13.sdk/usr/include/libxml2 > > -I/usr/local/include -c -o autoinc.o autoinc.c > > Looking at this example of a v10 build log on macOS: > https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl? > nm=longfin&dt=2017-10-23%2018%3A15%3A34&stg=make > > the compile command for autoinc is > > ccache gcc -Wall -Wmissing-prototypes -Wpointer-arith > -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute > -Wformat-security -fno-strict-aliasing -fwrapv > -Wno-unused-command-line-argument > -g -O2 -fno-common -Wno-deprecated-declarations -Werror -DREFINT_VERBOSE > -I. -I. -I../../src/include -I/Applications/Xcode.app/ > Contents/Developer/Platforms/MacOSX.platform/Developer/ > SDKs/MacOSX10.13.sdk/usr/include/libxml2 -I/usr/local/ssl/include -c -o > autoinc.o autoinc.c > > Some of the discrepancies (e.g. -Werror on the buildfarm machine) are > explainable as different configuration choices, but the references to > /usr/local/pgsql965 in your build sure look like trouble. > > > Is this looking for an existing environment variable (which > > seems unlikely for a build process) or is something else unusual? > > I believe the configure script *does* pay attention to environment > variables, particularly CPPFLAGS and CFLAGS. Most likely you had > version-specific values in those when you ran configure, and they > got absorbed into src/Makefile.global. > > regards, tom lane > -- Dr Ben Madin Managing Director m : +61 448 887 220 e : b...@ausvet.com.au 5 Shuffrey Street, Fremantle Western Australia on the web: www.ausvet.com.au This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this email are the opinion of the writer only and are not endorsed by Ausvet unless expressly stated otherwise. Although Ausvet uses virus scanning software we do not accept liability for viruses or similar in any attachments.
Re: [GENERAL] Installing PostgreSQL 10 on Mac OSX Undefined Symbol _heap_modify_tuple_by_cols
to clarify, I commented them out, hence the # - it wasn't that I removed lines that were already commented out :) On 25 October 2017 at 22:21, Ben Madin wrote: > G'day Tom, > > Thanks for the feedback. I couldn't find anywhere that suggested that I > had set the CPPFLAGS or CFLAGS environment variables, so I removed the > following lines from my profile: > > #export USE_PGXS=1 > #export PG_LIB_DIR="/usr/local/pgsql/lib" > #export PG_CONFIG="/usr/local/pgsql/bin/pg_config" > #export PGDATA="/usr/local/pgsql/data" > > and tried again - worked a treat! > > I haven't tried to work out which of these lines caused the problem, but > hopefully if anyone else has a similar problem they might benefit! > > cheers > > Ben > > > On 24 October 2017 at 02:43, Tom Lane wrote: > >> Ben Madin writes: >> > we are quite excited about the parallelisation enhancements, and keen to >> > try, but trying to build (using the same configure as we have used for >> 9.6) >> > is giving some warnings and errors. >> >> Something's definitely messed up there: >> >> > gcc -Wall -Wmissing-prototypes -Wpointer-arith >> > -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute >> > -Wformat-security -fno-strict-aliasing -fwrapv >> > -Wno-unused-command-line-argument -O2 -arch x86_64 -DREFINT_VERBOSE >> -I. >> > -I./ -I/usr/local/pgsql965/include/server >> > -I/usr/local/pgsql965/include/internal >> > -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOS >> X.platform/Developer/SDKs/MacOSX10.13.sdk/usr/include/libxml2 >> > -I/usr/local/include -c -o autoinc.o autoinc.c >> >> Looking at this example of a v10 build log on macOS: >> https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?n >> m=longfin&dt=2017-10-23%2018%3A15%3A34&stg=make >> >> the compile command for autoinc is >> >> ccache gcc -Wall -Wmissing-prototypes -Wpointer-arith >> -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute >> -Wformat-security -fno-strict-aliasing -fwrapv >> -Wno-unused-command-line-argument -g -O2 -fno-common >> -Wno-deprecated-declarations -Werror -DREFINT_VERBOSE -I. -I. >> -I../../src/include -I/Applications/Xcode.app/Cont >> ents/Developer/Platforms/MacOSX.platform/Developer/SDKs/ >> MacOSX10.13.sdk/usr/include/libxml2 -I/usr/local/ssl/include -c -o >> autoinc.o autoinc.c >> >> Some of the discrepancies (e.g. -Werror on the buildfarm machine) are >> explainable as different configuration choices, but the references to >> /usr/local/pgsql965 in your build sure look like trouble. >> >> > Is this looking for an existing environment variable (which >> > seems unlikely for a build process) or is something else unusual? >> >> I believe the configure script *does* pay attention to environment >> variables, particularly CPPFLAGS and CFLAGS. Most likely you had >> version-specific values in those when you ran configure, and they >> got absorbed into src/Makefile.global. >> >> regards, tom lane >> >
Re: [GENERAL] Catalog Bloat in Development - Frequently dropping/adding schemas and objects
David, the VACUUM FULL VERBOSE command might overcome this - I believe it works by effectively doing what you are proposing with a drop database and recreate. It does however lock the tables during the process (not a problem in a dev environ one assumes) but may not be ideal on a live database. cheers Ben On 30/06/2012, at 4:45 AM, David Johnston wrote: > In my current development environment I often drop some or all of the schemas > in the database and then re-create them schemas and the objects they contain. > When I go to bring up the database in my GUI it takes a considerable amount > of time to initialize. I suspect this is because the catalog tables are > becoming bloated. What is the recommended course of action to de-bloat them? > Running an unqualified vacuum does not seem to help. Is it better to just > periodically drop and recreate the database itself or would a vacuum with > specific tables listed be sufficient – and if so which tables? > > Thanks! > > David J.
Re: [GENERAL] Having two simultaneous and similar database
I think you want something like : http://www.postgresql.org/docs/8.4/interactive/backup.html On 05/10/2010, at 7:02 AM, Sairam Krishnamurthy wrote: > Hi all. > > I am trying to create two databases in two different machines connected over > the lan. Both the databases have similar tables and fields in them. I will be > updating database A always. I want the update to be backed up to database B > automatically during the night when no one will be using the databases. > > Can some one tell if if this can be done and if yes how ? > -- > Thanks, > Sairam Krishnamurthy > +1 612 859 8161
Re: [GENERAL] How to data dump a table content to a CSV or XML format?
Or in psql you can look at the help (\?) and set the output format to unaligned, comma separated, and output the table to disk : database=> \pset fieldsep , Field separator is ",". database=> \a Output format is unaligned. database=> \o table.csv database=> SELECT * FROM table; database=> \o cheers Ben On 05/10/2010, at 6:59 AM, Wang, Mary Y wrote: > Hi All, > > I'd like to do a data dump of a table to a CSV or XML file. > How would I do that? > > I'm running on Postgres 8.3.8. > > Thanks in advance. > Mary Wang > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SQLSTATE XX000 Internal Error 7
G'day all,
I'm going to go slowly on this, but I am intermittently (as in sometimes the
query works fine, sometimes it stops after 5 minutes and I get the message)
receiving the error message below on a long running query that is populating a
newly created table with a PostGIS Geometry column.
The Error Message is :
SQLSTATE[XX000]: Internal error: 7 ERROR: could not open file
"base/102979/430122_fsm": Invalid argument
I don't seem to be able to leverage any search engine to explain what this
message means - To many quotes, colons and brackets for google. I'm not even
sure why a file is being opened, but I assume that the file is part of the data
storage - which maybe I need to know about, but I haven't so far!
I have also fiddled a bit with the postgresql.conf settings to increase work
men etc. The details of the table and query are below.
So my question is really - what does this error message mean, and where do I
start looking for what could be causing it. Should I try a debug trace, or just
looking in the logs (I've attached the log entries at the bottom, but maybe I
should up the logging level)?
I haven't yet posted this to the postgis list, as it looked as though this
message is a postgresql message, not a postgis one.
cheers
Ben
I'm running on :
PostgreSQL 9.1.3 on x86_64-apple-darwin11.3.0, compiled by
i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5658)
(LLVM build 2336.9.00), 64-bit
and PostGIS :
POSTGIS="1.5.3" GEOS="3.3.2-CAPI-1.7.2" PROJ="Rel. 4.7.1, 23 September 2009"
LIBXML="2.7.3" USE_STATS
The table definition at the time that the error is occurring is:
Table "system.ctybnda2009"
Column |Type | Modifiers
---+-+
recordid | integer | not null default
nextval('ctybnda2009_recordid_seq'::regclass)
ccode | character varying(3)|
year | integer | not null default 2005
fips | character varying(2)|
l_1_name | character varying |
l_2_name | character varying |
l_3_name | character varying |
area | numeric |
modfiedon | timestamp without time zone | not null default now()
the_geom | geometry|
Indexes:
"ctybnda2009_recordid_key" UNIQUE CONSTRAINT, btree (recordid)
Check constraints:
"enforce_dims_the_geom" CHECK (st_ndims(the_geom) = 2)
"enforce_geotype_the_geom" CHECK (geometrytype(the_geom) =
'MULTIPOLYGON'::text OR the_geom IS NULL)
"enforce_srid_the_geom" CHECK (st_srid(the_geom) = 4326)
The query that causes the problem is :
INSERT INTO system.ctybnda2009 (ccode, the_geom)
SELECT m.country, st_multi(st_union(m.geom))
FROM maptable m
LEFT OUTER JOIN countries c
ON m.country = c.ccode
WHERE geom IS NOT NULL
AND m.valstart <= 2009
AND (m.valend IS NULL OR m.valend >= 2009)
GROUP BY 1
ORDER BY 1;
postgresql.log
2012-05-03 05:18:23 WSTERROR: could not open file "base/102979/430122_fsm":
Invalid argument
2012-05-03 05:18:23 WSTSTATEMENT: INSERT INTO system.ctybnda2011 (ccode,
the_geom) SELECT m.country, st_multi(st_union(m.geom)) FROM maptable m LEFT
OUTER JOIN countries c ON m.country = c.ccode WHERE geom IS NOT NULL AND
m.valstart <= $1 AND (m.valend IS NULL OR m.valend >= $2) GROUP BY 1 ORDER BY
1;
--
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] SQLSTATE XX000 Internal Error 7
G'day Tom, On 03/05/2012, at 11:57 AM, Tom Lane wrote: > Ben Madin writes: >> SQLSTATE[XX000]: Internal error: 7 ERROR: could not open file >> "base/102979/430122_fsm": Invalid argument > > [ scratches head ... ] AFAICS the only documented reason for open() to > fail with EINVAL on OS X is > > [EINVAL] The value of oflag is not valid. > > which is surely bogus since that code path calls it with a constant > value for oflag --- there's no way it could fail just some of the time. > > So this is smelling like a kernel or filesystem bug. I wonder exactly > which OS X update you're running, and what sort of filesystem the > database is stored on. I think that sounds bad! The OSX Update is 10.7.3 (11D50) The System is a 2.66 GHz Intel Core i7 with 8GB RAM. The database is stored on a partition that looks like : Capacity: 447.69 GB (447 687 770 112 bytes) Available:74.96 GB (74 956 308 480 bytes) Writable: Yes File System: Journaled HFS+ BSD Name: disk0s2 Mount Point: / Content: Apple_HFS and the data is stored in the /usr/local/pgsql-9.1/data directory, but there is a symlink (as I've retained the previous versions when I upgrade.) and so the /usr/local directory looks like : lrwxr-xr-x 1 root wheel 9 1 May 11:11 pgsql -> pgsql-9.1 drwxr-xr-x 11 root wheel 374 17 Feb 21:26 pgsql-8.4 drwxr-xr-x 8 root admin 272 17 Feb 21:26 pgsql-9.0 drwxr-xr-x 8 root admin 272 17 Feb 22:41 pgsql-9.1 and the data directory : drwx-- 20 _postgres _postgres 680 1 May 11:11 data is this the sort of exact information you were wondering? Since I last posted, I have again received : PL/pgSQL function "fill_ctybnda" line 18 at EXECUTE statement ERROR: could not open file "base/102979/430320_fsm": Invalid argument and I went looking and found in the base/102979/ directory: -rw---1 _postgres _postgres1253376 3 May 11:51 430320 -rw---1 _postgres _postgres 24576 3 May 11:51 430320_fsm so it look to my uneducated eye as though it has been able to open the file(quite a few of the files ending in _fsm have 24576 bytes) (PS How did you come to deciding that it was EINVAL - is that 'Error INVALid argument'?) cheers Ben -- 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] COPY from CSV, passing in default value?
Does creating a table with a default not work? CREATE TABLE salaries ( Town varchar(30), County varchar(30) NOT NULL DEFAULT 'Australia', Supervisor varchar(30), StartDate date, Salary int, Benefits int ); You might also want an auto-incrementing primary key, especially if you are importing data so you can delete any duplicates… CREATE TABLE salaries ( id serial unique PRIMARY KEY, Town varchar(30), County varchar(30) NOT NULL DEFAULT 'Australia', Supervisor varchar(30), StartDate date, Salary int, Benefits int ); An alternative that becomes simpler for importing repeatedly is to create a temporary table with the same column names as your csv file, but all the data types varchar. Import the csv (which is now easy even if there are '' in the salary field, which are not int) and then insert (with appropriate casting) the results from the temp table into the real table. cheers Ben On 15/05/2012, at 1:31 AM, adebarros wrote: > Assuming I have a table structured like so: > > CREATE TABLE salaries ( >Town varchar(30), >County varchar(30), >Supervisor varchar(30), >StartDate date, >Salary int, >Benefits int > ); > > If I have a CSV with only three of those fields, I can import like this: > > COPY salaries (Town, Supervisor, Salary) > FROM 'C:\salaries.csv' > WITH (FORMAT CSV); > > However, what if I wanted to assign a default value during import to > populate the County field? In my dreams it would be something like this > (which does not work): > > COPY salaries (Town, 'County Name', Supervisor, Salary) > FROM 'C:\salaries.csv' > WITH (FORMAT CSV); > > Any ideas? > > Thanks. > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/COPY-from-CSV-passing-in-default-value-tp5708672.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
