Re: [GENERAL] what Linux to run
On Sat, 2012-03-03 at 14:15 -0700, David Boreham wrote: > > We use CentOS 5 and 6 and install PG from the yum repository detailed > on the postgresql.org web site. Those RPMs will probably be a part of CentOS Testing repository soon. I and Karanbir had a chat about it at FOSDEM this year. -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
[GENERAL] Create topology from a shape file
Greetings to all. I want to know if with postgis 2.0 and postgresql is possible to generate a topology from the geometries contained inside a shape file. If this can be accomplished, where can I find the procedure i must follow? Thanks in advance. signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] || versus concat( ), diff behavior
I have found an another difference. select 'AA '::char(5) || 'AA'::char(5); result: "" while select 'AA '::varchar(5) || 'AA'::varchar(5); gives result: "AA AA" select 'AA '::text || 'AA'::text; gives result: "AA AA" what will be the reason for this? Regards, C P Kulkarni On Sat, Mar 3, 2012 at 3:40 AM, David Johnston wrote: > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Philip Couling > Sent: Friday, March 02, 2012 4:47 PM > To: david.sahag...@emc.com > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] || versus concat( ), diff behavior > > On 02/03/12 20:58, david.sahag...@emc.com wrote: > > Can anybody please point me to where this "difference of behavior" is > explained/documented ? > > Thanks, > > -dvs- > > > > -- version = 9.1.3 > > do $$ > > declare > >v_str char(10); > > begin > >v_str := 'abc' ; > >raise info '%', concat(v_str, v_str) ; > >raise info '%', v_str||v_str ; > > end > > $$; > > > > INFO: abc abc > > INFO: abcabc > > > > > > Concat is a function which concatenates whatever you give it blindly. > Hence it has the behavior that includes the blanks. > > The || operator reflects the more general PostgreSQL principle that > trailing > blanks are insignificant for char fields. You see the same behavior when > comparing char variables. > > > This can be found in the manual: > > http://www.postgresql.org/docs/current/static/datatype-character.html > > Values of type character are physically padded with spaces to the specified > width n, and are stored and displayed that way. However, the padding spaces > are treated as semantically insignificant. Trailing spaces are disregarded > when comparing two values of type character, and they will be removed when > converting a character value to one of the other string types. Note that > trailing spaces are semantically significant in character varying and text > values, and when using pattern matching, e.g. LIKE, regular expressions. > > > Hope this makes it just a little clearer. > > Regards > > > > - > > Philip, > > The question to ask is whether the behavior of the "concat" function is > intentionally different than the "||" operator. Aside from the ability to > take more than two arguments I would suggest they should behave > identically. > Given the newness of the "concat" function I would guess the difference is > unintentional. Regardless, either the documentation or the function code > needs to be modified: either to synchronize the behavior or to explicitly > point out the different treatment of "character" types. > > I'd argue that the "||" behavior is incorrect but at this point it doesn't > matter. Prior to the introduction of the "concat" function how would one > perform a concatenation with a "character" type and preserve the trailing > whitespace? If the new function intends to fix that behavior documenting > such would be helpful. > > DVS, > > From a curiosity standpoint I presume that the "concat" output leaves > whitespace surrounding the second half as well? In the future, when > debugging string content, I would suggest you bracket your output so you > know when there is trailing whitespace. I.E., '[' || string_to_view || ']' > => '[string with trailing whitespace ]' > > Dave > > > > > > > -- > 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] || versus concat( ), diff behavior
Both varchar and text keep the trailing whitespace since it must be significant. A char removes all trailing whitespace (with the || operator) since it cannot distinguish between padding supplied whitespace (insignificant) and user-supplied whitespace (significant). On Mar 4, 2012, at 13:26, c k wrote: > I have found an another difference. > > select 'AA '::char(5) || 'AA'::char(5); > > result: "" > > while > select 'AA '::varchar(5) || 'AA'::varchar(5); > gives > > result: "AA AA" > > select 'AA '::text || 'AA'::text; > gives > > result: "AA AA" > > what will be the reason for this? > > Regards, > > C P Kulkarni > > On Sat, Mar 3, 2012 at 3:40 AM, David Johnston wrote: > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Philip Couling > Sent: Friday, March 02, 2012 4:47 PM > To: david.sahag...@emc.com > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] || versus concat( ), diff behavior > > On 02/03/12 20:58, david.sahag...@emc.com wrote: > > Can anybody please point me to where this "difference of behavior" is > explained/documented ? > > Thanks, > > -dvs- > > > > -- version = 9.1.3 > > do $$ > > declare > >v_str char(10); > > begin > >v_str := 'abc' ; > >raise info '%', concat(v_str, v_str) ; > >raise info '%', v_str||v_str ; > > end > > $$; > > > > INFO: abc abc > > INFO: abcabc > > > > > > Concat is a function which concatenates whatever you give it blindly. > Hence it has the behavior that includes the blanks. > > The || operator reflects the more general PostgreSQL principle that trailing > blanks are insignificant for char fields. You see the same behavior when > comparing char variables. > > > This can be found in the manual: > > http://www.postgresql.org/docs/current/static/datatype-character.html > > Values of type character are physically padded with spaces to the specified > width n, and are stored and displayed that way. However, the padding spaces > are treated as semantically insignificant. Trailing spaces are disregarded > when comparing two values of type character, and they will be removed when > converting a character value to one of the other string types. Note that > trailing spaces are semantically significant in character varying and text > values, and when using pattern matching, e.g. LIKE, regular expressions. > > > Hope this makes it just a little clearer. > > Regards > > > - > > Philip, > > The question to ask is whether the behavior of the "concat" function is > intentionally different than the "||" operator. Aside from the ability to > take more than two arguments I would suggest they should behave identically. > Given the newness of the "concat" function I would guess the difference is > unintentional. Regardless, either the documentation or the function code > needs to be modified: either to synchronize the behavior or to explicitly > point out the different treatment of "character" types. > > I'd argue that the "||" behavior is incorrect but at this point it doesn't > matter. Prior to the introduction of the "concat" function how would one > perform a concatenation with a "character" type and preserve the trailing > whitespace? If the new function intends to fix that behavior documenting > such would be helpful. > > DVS, > > From a curiosity standpoint I presume that the "concat" output leaves > whitespace surrounding the second half as well? In the future, when > debugging string content, I would suggest you bracket your output so you > know when there is trailing whitespace. I.E., '[' || string_to_view || ']' > => '[string with trailing whitespace ]' > > Dave > > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] Return unknown resultset from a function
How do I return an unknown resultset from a function My main problem is that I do not know how many columns or the data type of the columns before runtime. It this possible at all? I also tried to return the data as a text array but I also have trouble with that. Regards Jan -- 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] Return unknown resultset from a function
Hello 2012/3/4 Jan Meyland Andersen : > How do I return an unknown resultset from a function > > My main problem is that I do not know how many columns or the data type of > the columns before runtime. > It this possible at all? > no, or it is not possible simply. PostgreSQL is strongly typed. I know only one workaround - using refcursor http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html Regards Pavel Stehule > I also tried to return the data as a text array but I also have trouble with > that. > > Regards > > Jan > > -- > 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
Re: [GENERAL] Return unknown resultset from a function
On Mar 4, 2012, at 14:52, Jan Meyland Andersen wrote: > How do I return an unknown resultset from a function > > My main problem is that I do not know how many columns or the data type of > the columns before runtime. > It this possible at all? > > I also tried to return the data as a text array but I also have trouble with > that. > > Regards > > Jan > Try HSTORE. You can return RECORD from the function but the SQL user still has to define the names/types at design time. Text array should work but you lose column names which HSTORE maintains. You could also return a delimited string and then split it in the calling routine. David J -- 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] || versus concat( ), diff behavior
c k writes: > what will be the reason for this? The short answer is that || uses cast-to-text semantics, whereas concat uses output-function semantics, and char(n) is one of the weird types for which those are different. Don't blame us, blame the SQL committee. Or rather than blaming anybody, stop using char(n); it's a badly defined data type, and there are almost no cases where varchar or text isn't preferable. 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] Return unknown resultset from a function
On 04/03/12, Jan Meyland Andersen (j...@agile.dk) wrote:
> How do I return an unknown resultset from a function
>
> My main problem is that I do not know how many columns or the data
> type of the columns before runtime.
> It this possible at all?
>
> I also tried to return the data as a text array but I also have
> trouble with that.
There is a section on this in the docs at (for instance)
http://www.postgresql.org/docs/8.4/static/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS
A specific example which may help is
CREATE FUNCTION new_emp() RETURNS emp AS $$
SELECT ROW('None', 1000.0, 25, '(2,2)')::emp;
$$ LANGUAGE SQL;
Note that there are two ways of calling such a function. You probably
want the "SELECT *" form.
Rory
--
Rory Campbell-Lange
r...@campbell-lange.net
Campbell-Lange Workshop
www.campbell-lange.net
0207 6311 555
3 Tottenham Street London W1T 2AF
Registered in England No. 04551928
--
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] Return unknown resultset from a function
On 04/03/12, Rory Campbell-Lange (r...@campbell-lange.net) wrote:
> On 04/03/12, Jan Meyland Andersen (j...@agile.dk) wrote:
> > My main problem is that I do not know how many columns or the data
> > type of the columns before runtime.
> > It this possible at all?
> There is a section on this in the docs at (for instance)
> http://www.postgresql.org/docs/8.4/static/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS
>
> A specific example which may help is
>
> CREATE FUNCTION new_emp() RETURNS emp AS $$
> SELECT ROW('None', 1000.0, 25, '(2,2)')::emp;
> $$ LANGUAGE SQL;
>
> Note that there are two ways of calling such a function. You probably
> want the "SELECT *" form.
My apologies -- if you aren't sure about the return type you will need
to use a RECORD.
eg
http://www.postgresql.org/docs/7.4/static/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS
There is a good answer on stack overflow about returning polymorphic
results here:
http://stackoverflow.com/questions/4547672/how-to-return-multiple-fields-as-a-record-in-postgresql-pl-pgsql
--
Rory Campbell-Lange
r...@campbell-lange.net
Campbell-Lange Workshop
www.campbell-lange.net
0207 6311 555
3 Tottenham Street London W1T 2AF
Registered in England No. 04551928
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] atoi-like function: is there a better way to do this?
One of our tables has a few columns that may be interpreted as strings or may be numbers (data type is varchar, numbers are stored as decimal). Generally, operations are performed on the string, but sometimes we need to parse out a number - without it failing on error. I wrote the following function to approximate to the semantics of atoi: create or replace function str2int(val varchar) returns bigint immutable as $$ begin val=substring(val from '[0-9]*'); if length(val) between 1 and 19 then return val::bigint; end if; return 0; end; $$ language plpgsql; It uses a regular expression to chop the string down to just the leading digits (leading only - '123.45' should parse as 123). Is there a more efficient way to achieve this? Thanks! Chris Angelico -- 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] atoi-like function: is there a better way to do this?
(Hoping you meant for that reply to be on-list as I'm here responding on-list.)
On Mon, Mar 5, 2012 at 2:16 PM, A.M. wrote:
>
> On Mar 4, 2012, at 9:13 PM, Chris Angelico wrote:
>
>> One of our tables has a few columns that may be interpreted as strings
>> or may be numbers (data type is varchar, numbers are stored as
>> decimal). Generally, operations are performed on the string, but
>> sometimes we need to parse out a number - without it failing on error.
>> I wrote the following function to approximate to the semantics of
>> atoi:
>
> I would recommend against such a schema since different data types should
> warrant their own columns, but if you are left with no choice...
The values have to be strings for other reasons (eg '' is valid
everywhere, and this is subsequently processed by a script that
expects all strings). So yeah, no choice there. But I agree that
normally you DO want integers stored in integer columns, and we're
paying a performance penalty for this.
>>
>> create or replace function str2int(val varchar) returns bigint immutable as
>> $$
>> begin
>> val=substring(val from '[0-9]*');
>> if length(val) between 1 and 19 then return val::bigint; end if;
>> return 0;
>> end;
>> $$ language plpgsql;
>
> This can be written as:
> select substring('35345345.45645654' from '\d{1,19}')::bigint;
>
> Be aware that this does not account for:
> 3dogs
> 3 dogs
> 3,dogs
> 3.5.6.7
>
> all of which will return 3::bigint, but I assume that the column is not
> completely free-form or maybe this is what you want. (?)
>
> Cheers,
> M
That is in fact the behaviour I want. Trouble is, that simpler version
returns NULL if given 'asdf' as its input - I want it to return 0.
It's also about the same performance (slightly slower in a quick test)
than the original; it's still doing a regular expression parse. I was
hoping very much to avoid the regex altogether.
ChrisA
--
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] atoi-like function: is there a better way to do this?
On Mar 4, 2012, at 22:31, Chris Angelico wrote:
> (Hoping you meant for that reply to be on-list as I'm here responding
> on-list.)
>
> On Mon, Mar 5, 2012 at 2:16 PM, A.M. wrote:
>>
>> On Mar 4, 2012, at 9:13 PM, Chris Angelico wrote:
>>
>>> One of our tables has a few columns that may be interpreted as strings
>>> or may be numbers (data type is varchar, numbers are stored as
>>> decimal). Generally, operations are performed on the string, but
>>> sometimes we need to parse out a number - without it failing on error.
>>> I wrote the following function to approximate to the semantics of
>>> atoi:
>>
>> I would recommend against such a schema since different data types should
>> warrant their own columns, but if you are left with no choice...
>
> The values have to be strings for other reasons (eg '' is valid
> everywhere, and this is subsequently processed by a script that
> expects all strings). So yeah, no choice there. But I agree that
> normally you DO want integers stored in integer columns, and we're
> paying a performance penalty for this.
>
>>>
>>> create or replace function str2int(val varchar) returns bigint immutable as
>>> $$
>>> begin
>>> val=substring(val from '[0-9]*');
>>> if length(val) between 1 and 19 then return val::bigint; end if;
>>> return 0;
>>> end;
>>> $$ language plpgsql;
>>
>> This can be written as:
>> select substring('35345345.45645654' from '\d{1,19}')::bigint;
>>
>> Be aware that this does not account for:
>> 3dogs
>> 3 dogs
>> 3,dogs
>> 3.5.6.7
>>
>> all of which will return 3::bigint, but I assume that the column is not
>> completely free-form or maybe this is what you want. (?)
>>
>> Cheers,
>> M
>
> That is in fact the behaviour I want. Trouble is, that simpler version
> returns NULL if given 'asdf' as its input - I want it to return 0.
COALESCE is your friend
> It's also about the same performance (slightly slower in a quick test)
> than the original; it's still doing a regular expression parse. I was
> hoping very much to avoid the regex altogether.
>
> ChrisA
>
Any efficient, non-RegEx, alternative would require more context to evaluate
than you provide. Mainly, would it be faster to have a separate field to store
the parsed (at input) number and then query that field directly (even if it is
a text field as well)? Basically cache the parse.
David J..
--
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] atoi-like function: is there a better way to do this?
On Mon, Mar 5, 2012 at 2:50 PM, David Johnston wrote: > Any efficient, non-RegEx, alternative would require more context to evaluate > than you provide. Mainly, would it be faster to have a separate field to > store the parsed (at input) number and then query that field directly (even > if it is a text field as well)? Basically cache the parse. Caching's looking tempting, but I don't know if it'll be worth it (these fields won't be searched-as-int very often compared to search-as-string, and there's potentially a lot of such fields). All I need out of it is the leading digits - I can strip them with trim(), but I can't keep _only_ those digits. The other possibility that may be of value is to write the function in C instead of pl/pgsql, which will then actually call atoi() itself. Is that going to be a better option? ChrisA -- 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] atoi-like function: is there a better way to do this?
On 05/03/12 04:06, Chris Angelico wrote:
On Mon, Mar 5, 2012 at 2:50 PM, David Johnston wrote:
Any efficient, non-RegEx, alternative would require more context to evaluate
than you provide. Mainly, would it be faster to have a separate field to store
the parsed (at input) number and then query that field directly (even if it is
a text field as well)? Basically cache the parse.
Caching's looking tempting, but I don't know if it'll be worth it
(these fields won't be searched-as-int very often compared to
search-as-string, and there's potentially a lot of such fields). All I
need out of it is the leading digits - I can strip them with trim(),
but I can't keep _only_ those digits.
The other possibility that may be of value is to write the function in
C instead of pl/pgsql, which will then actually call atoi() itself. Is
that going to be a better option?
Can you use to_number() here? It sounds like something along the lines
of cast(to_number('0' || field::varchar, '9.') as int) might
give the behaviour you're after, and a quick test seems to indicate that
it's about 4x faster than the original function:
postgres=# explain analyze select cast(to_number('0' ||
generate_series::varchar, '9.') as int) from
generate_series(1,100);
QUERY PLAN
-
Function Scan on generate_series (cost=0.00..22.50 rows=1000 width=4)
(actual time=137.720..1065.752 rows=100 loops=1)
Total runtime: 1144.993 ms
(2 rows)
postgres=# explain analyze select str2int(generate_series::varchar) from
generate_series(1,100);
QUERY PLAN
--
Function Scan on generate_series (cost=0.00..265.00 rows=1000
width=4) (actual time=135.180..4022.408 rows=100 loops=1)
Total runtime: 4121.233 ms
(2 rows)
Hopefully there's a cleaner way of writing that without a long list of
9s in the format string, and if the field is nullable I'd guess you
probably need a coalesce(..., 0) around that as well.
cheers,
Tom
--
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] atoi-like function: is there a better way to do this?
On Mon, Mar 5, 2012 at 3:15 PM, Tom Molesworth wrote:
> Can you use to_number() here? It sounds like something along the lines of
> cast(to_number('0' || field::varchar, '9.') as int) might give the
> behaviour you're after, and a quick test seems to indicate that it's about
> 4x faster than the original function:
I looked at to_number but it seems to find digits anywhere inside the
field - to_number('12.34','9') returns 1234, but I want it to stop
at the decimal.
Nice trick with prepending a zero though - I didn't think of that.
That may save some hassle!
ChrisA
--
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] atoi-like function: is there a better way to do this?
On Mar 5, 2012, at 0:08, Chris Angelico wrote:
> On Mon, Mar 5, 2012 at 3:15 PM, Tom Molesworth wrote:
>> Can you use to_number() here? It sounds like something along the lines of
>> cast(to_number('0' || field::varchar, '9.') as int) might give the
>> behaviour you're after, and a quick test seems to indicate that it's about
>> 4x faster than the original function:
>
> I looked at to_number but it seems to find digits anywhere inside the
> field - to_number('12.34','9') returns 1234, but I want it to stop
> at the decimal.
>
> Nice trick with prepending a zero though - I didn't think of that.
> That may save some hassle!
>
> ChrisA
>
>
0alpha999 -> 0
alpha999 -> 999
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Create topology from a shape file
Greetings to all. I want to know if with postgis 2.0 and postgresql is possible to generate a topology from the geometries contained inside a shape file. If this can be accomplished, where can I find the procedure i must follow? Thanks in advance. -- 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] what Linux to run
On 04/03/12 09:49, John R Pierce wrote: On 03/03/12 2:55 AM, Gavin Flower wrote: My knowledge of Debian is via friend's (an extremely competent and experienced Unix guy who got me into Linux & who still runs Debian) comments and what I've noticed on the web. For a Desktop development machine, I currently prefer Fedora, but for a server I need to be more conservative. One place I worked used Ubuntu, but I quickly switched my machine to Fedora, when I found Ubuntu lacked the desktop things I relied on! So I would interested in the answers, also I would need to be able to install JDK7. the server equivalent to Fedora is, of course, RHEL or CentOS. CentOS 6.2 is working very well for us for a range of stuff. JDK7, I dunno, we're still using JDK 6 and trying very hard to stay away from bleeding edge proprietary features. I sure don't see anything here we need for our work: http://openjdk.java.net/projects/jdk7/features/ but, any version of java can be installed on most anything... JDK's just need to be untarred somewhere (we'll put unpackaged ones in /opt/something) Yes, I'd probably lean towards Centos (RHE if there is the budget). Not only for the reasons you mentioned, but that the site seems to like Centos for some servers -- even though the standard Linux desktop is Ubuntu. Though I'm trying to keep a relatively open mind about the choice of server O/S (so long as it is Linux, of course!). By the time my project is in wide use, JDK7 will no longer be bleeding edge. However, hopefully way short of EOL! :-) The project is in fairly early stages so I have a wide latitude of what software I use. I may go for pg9.2, as the covering indexes and other performance improvements may well prove very useful (possibly nearly essential, for one possible sub project). I expect that once (about) mid year has passed, I will have to switch to a much more conservative approach to new versions. The project is for training and to aid research, so there is more tolerance of errors and other problems, than systems that deal with financial processing. Though obviously I am aiming for a perfect system that is totally reliable! Though in practice: 'good enough', does the 'required job', and is 'sufficiently responsive' are closer to the mark.
Re: [GENERAL] what Linux to run
Hmm... I tend to _mostly_ run workstations rather than servers, & pick my distro to suit my application needs. My workplace is a SLES site, & I use Open Suse. Given most of my Postgres databases are in fact PostGIS databases, and need to work with a variety of other spatial data & GIS related apps, then I have a set of dependencies to work with for every install. Postgres, Postgis, GEOS, Proj, GDAL, mapserver, Java, python. QGIS, GMT, etc. I have liased with the package maintainers who look after the Suse GEO repository, and they are generally able to build any required package, for both server * workstation distros (SLED, SLES, OpenSuse). Having robust packages built by people who know more than I do about this area is core to my selection of distro. While I'm aware that Debian, Ubuntu, Fedora also have GIS related repositories, the OPenSuse ones have, for me at least, the best mix of currency & stability, & fantastic support. If your goal is to run a robust Postgres server, find the mainstream distro which provides what you want out of the box, so you can run the database, not wrestle with compiling it every time something changes. Only consider compiling your own applications if there is no such distro, or you really want to have that level of control & ownership of the system. Also, if you are running a VM as your server, then under Xen commercial tools, for example, SLES is fully supported by the hypervisor. Ubuntu isn't. Makes choosing easy... YMMV :-) Brent Wood GIS/DBA consultant NIWA +64 (4) 4 386-0300 From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of David Boreham [david_l...@boreham.org] Sent: Sunday, March 04, 2012 3:23 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] what Linux to run On 3/3/2012 7:05 PM, Tom Lane wrote: > > [ raised eyebrow... ] As the person responsible for the packaging > you're dissing, I'd be interested to know exactly why you feel that > the Red Hat/CentOS PG packages "can never be trusted". Certainly they > tend to be from older release branches as a result of Red Hat's desire > to not break applications after a RHEL branch is released, but they're > not generally broken AFAIK. > > No dissing intended. I didn't say or mean that OS-delivered PG builds were generally broken (although I wouldn't be entirely surprised to see that happen in some distributions, present company excluded). I'm concerned about things like : a) Picking a sufficiently recent version to get the benefit of performance optimizations, new features and bug fixes. b) Picking a sufficiently old version to reduce the risk of instability. c) Picking a version that is compatible with the on-disk data I already have on some set of existing production machines. d) Deciding which point releases contain fixes that are relevant to our deployment. Respectfully, I don't trust you to come to the correct choice on these issues for me every time, or even once. I stick by my opinion that anyone who goes with the OS-bundled version of a database server, for any sort of serious production use, is making a mistake. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SELECT FOR UPDATE could see commited trasaction partially.
Hi, I hit a UPDATE/LOCK issue in my application and the result has surprised me somewhat. And for the repro, it boils down into this: --- CREATE TABLE x (a int, b bool); INSERT INTO x VALUES (1, TRUE); COMMIT; _THREAD 1_: BEGIN; UPDATE x SET b=FALSE; INSERT INTO x VALUES (2, TRUE); _THREAD 2_: BEGIN; SELECT * FROM x WHERE b=TRUE FOR UPDATE; -- It will be blocked, as expected _THREAD 1_: COMMIT; _THREAD 2_ will be unblocked. It will return no rows. I expect it to return (2, TRUE) instead, when I design the program. If I issue the same SELECT query in THREAD 2 right now, it does indeed return (2, TRUE). For the same SQL statement, in MySQL-InnoDB, it does return (2, TRUE) in the first SELECT. I understand why this happens in PgSQL, (because it first limited the selection and locked the row, upon unlock it recheck the condition). I don't like THERAD 2 only see half of the fact of the committed transaction (it see the effect of the update but not the insert), is there anything I could do? I considered: * ISOLATION serialization - but the thread 2 would abort as deadlock. * Both thread do: LOCK TABLE x AS SHARE ROW EXCLUSIVE MODE - does resolve my issue but it creates a big lock contention problem, and relies on app to do the right thing. * Advisory lock - pretty much the same, except that I could unlock earlier to make the locking period shorter, but nevertheless it's the whole table lock. Thoughts? Thanks, Sam
