[HACKERS] On Conflict Do nothing errors IF conflict and there is a data type length or check failure

2016-02-16 Thread Regina Obe
I'm guessing this is by design but just wanted to confirm that since it makes this feature not as useful for us. It also wasn't absolutely clear to me from the documentation. We are running PostgreSQL 9.5.1 and if we do something like: CREATE TABLE test(field1 varchar(5) primary key, field2

Re: [HACKERS] Is there a way around function search_path killing SQL function inlining?

2016-03-10 Thread Regina Obe
-Original Message- > From: Andreas Karlsson [mailto:andr...@proxel.se] > Sent: Tuesday, March 08, 2016 10:43 PM > To: Regina Obe <l...@pcorp.us>; 'Robert Haas' <robertmh...@gmail.com> > Cc: pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Is there a way

Re: [HACKERS] Is there a way around function search_path killing SQL function inlining? - and backup / restore issue

2016-03-10 Thread Regina Obe
> Hmm. The meaning of funcs.inline depends on the search_path, not just during > dump restoration but all the time. So anything uses it under a different > search_path setting than the normal one will have this kind of problem; not > just > dump/restore. > I don't have a very good idea

Re: [HACKERS] Is there a way around function search_path killing SQL function inlining? - and backup / restore issue

2016-03-12 Thread Regina Obe
>> On 3/10/16 3:29 PM, Regina Obe wrote: >> Take for example, I have tiger geocoder which relies on fuzzystrmatch. I >> have no idea where someone installs fuzzystrmatch so I can't schema qualify >> those calls. I use that dependent function to use to buil

Re: [HACKERS] PostgreSQL 9.6 behavior change with set returning (funct).*

2016-03-23 Thread Regina Obe
> I'm something of a backwards compatibility zealot, but I've become one for > very good reasons. Personally, I'd rather we'd define precisely the usages > that are deprecated (I guess SRF-tlist in the presence of > FROM) and force them to error out with an appropriate HINT rather than give a

[HACKERS] Can we amend gitignore so git postgresql works with git on windows using Msys/Mingw64

2016-03-25 Thread Regina Obe
Tom et. al, Thanks for fixing the SRF function order by thing. That test now passes now, but there is one other test failing I was going to troubleshoot. The problem is I can't compile from git postgresql, and the issue is I think because my git when it pulls down the files they come in as CRLF

[HACKERS] If a schema is created as part of an extension, should all user created tables created in that schema be considered part of the extension?

2016-03-25 Thread Regina Obe
I just discovered something which was a little alarming to me. In the postgis_tiger_geocoder extension, I had switched to having the schema where user data download is stored created as part of create extension script so I wouldn't need to check during load. So I have a statement like this in

[HACKERS] Can we amend gitattributes so git postgresql works with git on windows using Msys/Mingw64

2016-03-25 Thread Regina Obe
Typo in my last subject line - meant gitattributes. So should have lines added like below *.sheol=lf *.ineol=lf *.h.in eol=lf *.h eol=lf Thanks, Regina -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

[HACKERS] PostgreSQL 9.6 behavior change with set returning (funct).*

2016-03-23 Thread Regina Obe
In the past couple of weeks our PostGIS tests against PostgreSQL 9.6 dev started failing. I traced the issue down to a behavior change in 9.6 when dealing with output of set returning functions when used with (func).* syntax. Here is an example not involving PostGIS. Is this an intentional

Re: [HACKERS] If a schema is created as part of an extension, should all user created tables created in that schema be considered part of the extension?

2016-03-26 Thread Regina Obe
> I think the chain of events is that the tiger_data schema is marked as not to be backed up (because it belongs to an extension) and then all of its tables are marked as not to be backed up because they're in a schema that's not to be backed up. The latter > behavior is meant to implement

Re: [HACKERS] Can we amend gitignore so git postgresql works with git on windows using Msys/Mingw64

2016-03-26 Thread Regina Obe
> You can change the setting with: > git config --global core.autocrlf input > Still, it's fair to wonder if we shouldn't add an entry for this to our .gitattributes. I'm actually wondering why we wouldn't apply it to ALL text files in git, not just the extensions Regina mentioned.

Re: [HACKERS] Is there a way around function search_path killing SQL function inlining?

2016-03-07 Thread Regina Obe
>> On Fri, Mar 4, 2016 at 9:29 PM, Regina Obe <l...@pcorp.us>> wrote: >> I think the answer to this question is NO, but thought I'd ask. >> >> A lot of folks in PostGIS land are suffering from restore issues, >> materialized view issues etc. because we hav

[HACKERS] Is there a way around function search_path killing SQL function inlining?

2016-03-04 Thread Regina Obe
I think the answer to this question is NO, but thought I'd ask. A lot of folks in PostGIS land are suffering from restore issues, materialized view issues etc. because we have functions such as ST_Intersects Which does _ST_Intersects AND && Since _ST_Intersects is not schema qualified,

Re: [HACKERS] Is there a way around function search_path killing SQL function inlining?

2016-08-10 Thread Regina Obe
> Michael Banck writes: >> As I've been bitten by this problem recently, I thought I'd take a >> look at editing the PostGIS extension SQL file to this end, but >> contrary to the above, the @extschema@ feature only applies to >> non-relocatable extensions, from

[HACKERS] PostgreSQL Version 10, missing minor version

2016-08-28 Thread Regina Obe
The routine in PostGIS to parse out the version number from pg_config is breaking in the 10 cycle. Issue seems to be because there is no minor specified. e.g. pgconfig --version returns: PostgreSQL 10devel Instead of expected PostgreSQL 10.0devel Is this the way it's going to be or will

Re: [HACKERS] What is "index returned tuples in wrong order" for recheck supposed to guard against?

2017-01-02 Thread Regina Obe
>> If things are out of order, why isn't just going to was_exact = false >> good enough? >> >> I'm not sure if the mistake is in our PostGIS code or something in >> PostgreSQL recheck logic. >> If I change the elog(ERROR ...) to a elog(NOTICE, the answers are >> correct and sort order is

[HACKERS] What is "index returned tuples in wrong order" for recheck supposed to guard against?

2016-12-29 Thread Regina Obe
I've been trying to troubleshoot the cause of this PostGIS recheck bug we have reported by two people so far. The last test was a nice simple repeatable one that triggered the issue: https://trac.osgeo.org/postgis/ticket/3418 from what I have seen this only affects cases where we are doing a

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

2017-05-26 Thread Regina Obe
> "Regina Obe" <l...@pcorp.us> writes: >> I figured out the culprit was the change in CASE WHEN behavior with >> set returning functions Had a criteria something of the form: >> CASE WHEN some_condition_dependent_on_sometable_that_resolves_to_false &

[HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change?

2017-05-25 Thread Regina Obe
Did something change with how exclusion constraints are handled? I'm trying to troubleshoot a regression we are having with PostGIS raster support. As best I can guess, it's because exclusion constraints that used to work in past versions are failing in PostgreSQL 10 with an error something like

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

2017-05-25 Thread Regina Obe
> Did something change with how exclusion constraints are handled? I'm trying to troubleshoot a regression we are having with PostGIS raster support. > As best I can guess, it's because exclusion constraints that used to work in past versions are failing in PostgreSQL 10 with an error something

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

2017-06-07 Thread Regina Obe
> After chewing on this for awhile, I'm starting to come to the conclusion that we'd be best off to throw an error for SRF-inside-CASE (or COALESCE). Mark is correct that the simplest case of > SELECT x, CASE WHEN y THEN generate_series(1,z) ELSE 5 END > FROM

Re: [HACKERS] PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

2017-06-08 Thread Regina Obe
> But this line of thinking does strengthen my feeling that throwing an error is the right thing to do for the moment. If we allow v10 to accept such cases but do something different from what we used to, that > will greatly complicate any future attempt to try to restore the old behavior. >

Re: [HACKERS] Parallel Aggregation support for aggregate functions that use transitions not implemented for array_agg

2017-06-06 Thread Regina Obe
> On 6/6/17 13:52, Regina Obe wrote: >> It seems CREATE AGGREGATE was expanded in 9.6 to support >> parallelization of aggregate functions using transitions, with the >> addition of serialfunc and deserialfunc to the aggregate definitions. >> >> https://www.

[HACKERS] Parallel Aggregation support for aggregate functions that use transitions not implemented for array_agg

2017-06-06 Thread Regina Obe
It seems CREATE AGGREGATE was expanded in 9.6 to support parallelization of aggregate functions using transitions, with the addition of serialfunc and deserialfunc to the aggregate definitions. https://www.postgresql.org/docs/10/static/sql-createaggregate.html I was looking at the PostgreSQL 10

[HACKERS] pg_upgrade changes can it use CREATE EXTENSION?

2017-08-30 Thread Regina Obe
for the life of 2 major series because we don't break backward compatibility often in a PostGIS minor version got shot down. Any thoughts on this? Thanks, Regina Obe PostGIS PSC member -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http

Re: [HACKERS] pg_upgrade changes can it use CREATE EXTENSION?

2017-08-30 Thread Regina Obe
Sorry for the cross posting on this one, but I think it's important both groups are aware. >> I think this thread covers most of the issues. >> https://lists.osgeo.org/pipermail/postgis-devel/2017-August/026355.html >> My thought was is it possible for pg_upgrade to be taught to use CREATE >>