Re: [GENERAL] Best filesystem for a high load db
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/25/2014 05:54 PM, Bill Moran wrote: > On Tue, 25 Nov 2014 17:27:18 +0100 Christoph Berg > wrote: > >> Re: Bill Moran 2014-11-25 >> <20141125111630.d05d58a9eb083c7cf80ed...@potentialtech.com> >>> Anything with a journal is a performance problem. PostgreSQL >>> effectivly does its own journalling with the WAL logs. That's >>> not to say that there's no value to crash recovery to having a >>> journalling filesystem, but it's just to say that our >>> experience showed journaling filesystems to be slower. That >>> rules out ext4, unless you disable the journal. I seem to >>> remember ext4 with journalling disabled being one of the faster >>> filesystems, but I could be remembering wrong. >> >> If you are using a non-journalling FS, you'll be waiting for a >> full fsck after a system crash. Not sure that's an improvement. > > It's an improvement if: a) You're investing in high-quality > hardware, so the chance of a system crash is very low. b) The > database is replicated, so your plan in the event of a primary > crash is to fail over to the backup anyway. > > If both of those are in place (as they were at my previous job) > then the time it takes to fsck isn't an issue, and taking action > that causes the database to run faster when nothing is wrong can be > considered. > > Obviously, the OP needs to assess the specific needs of the product > in question. Your point is very valid, and I'm glad you brought it > up (as a lot of people forget about it) but sometimes it's not the > most important factor. > Thank you a lot to have shared with me your experiences. Indeed we will have two servers in cluster with high quality hardware so a fsck restore shouldn't be a big problem. I will analize the xfs option as well and then I will decide. Thank you again, Maila Fatticcioni - -- __ Maila Fatticcioni __ Mediterranean Broadband Infrastructure s.r.l. via Francesco Squartini n°7 56121 Ospedaletto (PI) - ITALY __ Phone: +39(050)3870851 Fax: +39(050)3870809 __ -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlR1qI4ACgkQi2q3wPb3FcO72QCg2zEq+5SRfpcVkq8+QprPHiu1 SZ4An3cVJCRePrIlNDQFLJde3uLYoS0k =/FjW -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] issue in postgresql 9.1.3 in using arrow key in Solaris platform
Hi all, We are facing following issue in postgresql 9.1.3 in using arrow key in Solaris platform. Can you please help us to resolve it or any new release has fix for this or any workaround for this? issue: psql client generates a core when up arrow is used twice. Platfrom: Solaris X86 Steps to reproduce: = 1. Login to any postgres database 2. execute any quer say "\list" 3. press up arrow twice. 4. segmentation fault occurs and core is generated. Also session is terminated. PLease find example below # ./psql -U super -d mgrdb Password for user super: psql (9.1.3) Type "help" for help. mgrdb=# \l List of databases Name| Owner | Encoding | Collate |Ctype| Access privileg es ---+--+--+-+-+-- - mgrdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/post gres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/post gres (4 rows) mgrdb=# mgrdb=# select count(1) from operator_msm;Segmentation Fault (core dumped) Regards Tarkeshwar
[GENERAL] Active/Active clustering in postgres
Hi All, I am looking for PostgreSQL active/active clustering and whether PostgreSQL support any form of shared-storage clustering . Is there any methods or tools for implementing active/active clustering on Postgres supported by community or any third party tools. Regards Manmohan
Re: [GENERAL] issue in postgresql 9.1.3 in using arrow key in Solaris platform
On 11/26/2014 02:16 AM, M Tarkeshwar Rao wrote: Hi all, We are facing following issue in postgresql 9.1.3 in using arrow key in Solaris platform. *Can you please help us to resolve it or any new release has fix for this or any workaround for this?* Would seem to me to be an interaction between Postgres and readline. Not sure exactly what, but some information would be helpful for those that might know: 1) What version of Solaris? 2) How was Postgres installed and from what source? 3) What version of readline is installed? 4) Are you using a psql client that is the same version as the server? issue: psql client generates a core when up arrow is used twice. Regards Tarkeshwar -- 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] Best filesystem for a high load db
On 11/26/2014 4:16 AM, Maila Fatticcioni wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/25/2014 05:54 PM, Bill Moran wrote: On Tue, 25 Nov 2014 17:27:18 +0100 Christoph Berg wrote: Re: Bill Moran 2014-11-25 <20141125111630.d05d58a9eb083c7cf80ed...@potentialtech.com> Anything with a journal is a performance problem. PostgreSQL effectivly does its own journalling with the WAL logs. That's not to say that there's no value to crash recovery to having a journalling filesystem, but it's just to say that our experience showed journaling filesystems to be slower. That rules out ext4, unless you disable the journal. I seem to remember ext4 with journalling disabled being one of the faster filesystems, but I could be remembering wrong. If you are using a non-journalling FS, you'll be waiting for a full fsck after a system crash. Not sure that's an improvement. It's an improvement if: a) You're investing in high-quality hardware, so the chance of a system crash is very low. b) The database is replicated, so your plan in the event of a primary crash is to fail over to the backup anyway. If both of those are in place (as they were at my previous job) then the time it takes to fsck isn't an issue, and taking action that causes the database to run faster when nothing is wrong can be considered. Obviously, the OP needs to assess the specific needs of the product in question. Your point is very valid, and I'm glad you brought it up (as a lot of people forget about it) but sometimes it's not the most important factor. Thank you a lot to have shared with me your experiences. Indeed we will have two servers in cluster with high quality hardware so a fsck restore shouldn't be a big problem. I will analize the xfs option as well and then I will decide. Thank you again, Maila Fatticcioni Also, if you do some timings, please share it with us, it'd be nice to have some more data points. -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] FW: Latest Postgresql DB version
Hello Team, I am working for Ericsson Multi Mediation product and we are using Postgresql as DB in our product. Please let me know the latest Postgresql DB version available on RHEL ? Which Postgresql DB version will be supported on RHEL 7.x version and when the same will be available ? Regards/// Deepti Sharma Sr. Configuration Engineer (ITIL 2011 Foundation Certified)
Re: [GENERAL] FW: Latest Postgresql DB version
On 11/25/2014 12:39 AM, Deepti Sharma S wrote: Hello Team, I am working for Ericsson Multi Mediation product and we are using Postgresql as DB in our product. Please let me know the latest Postgresql DB version available on RHEL ? I do not have a RHEL 7 instance available, but looking at the Centos 7 package list: http://mirror.centos.org/centos/7/os/x86_64/Packages/ I see Postgres 9.2.7 Which Postgresql DB version will be supported on RHEL 7.x version and when the same will be available ? You can use the Postgres community repos: http://yum.postgresql.org/repopackages.php and get either 9.3 or if you want a beta/rc 9.4. *Regards///** **Deepti Sharma** **Sr. Configuration Engineer* *(ITIL 2011 Foundation Certified) * -- 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] issue in postgresql 9.1.3 in using arrow key in Solaris platform
M Tarkeshwar Rao writes: > We are facing following issue in postgresql 9.1.3 in using arrow key in > Solaris platform. > Can you please help us to resolve it or any new release has fix for this or > any workaround for this? > issue: psql client generates a core when up arrow is used twice. Almost certainly, this is not psql's fault, but rather a bug in the readline or libedit library it's using for command history. If you're using libedit, I can't say that I'm astonished, as we've seen a depressingly large number of bugs reported in various versions of libedit. In any case, try to get a newer version of that library; or if you've linked psql to libedit, consider rebuilding against libreadline. regards, tom lane PS: this was cross-posted inappropriately. I've trimmed the cc list to just 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] Best filesystem for a high load db
Currently I use FreeBSD 10 with ZFS filesystem for our Production database. Speed wise it's fine, i'm sure other filesystems could be faster, even though we have never compared it with other filesystems. The reason we do ZFS is to take advantage of the data compression and snapshots. It is very easy to generate a new slave just by copying the filesystem to another machine. Having different compression for tablespaces that don't get accessed as much, or tablespaces on faster disks. Doing big data migrations or pushes we are able to rollback if something fails. Also when upgrading to a newer version of Postgres, just take a snapshot and upgrade that. Same with database backups. We issue a pg_start_backup(), take a few snapshots, issue pg_stop_backup(). Then ship the entire filesystem to a different machine and that's your backup. One thing I am pushing to do is using SSDs for the ZIL and L2ARC. This would allow for a pretty nice boost in speed. -Joseph On Wed, Nov 26, 2014 at 9:50 AM, Andy Colson wrote: > On 11/26/2014 4:16 AM, Maila Fatticcioni wrote: > >> -BEGIN PGP SIGNED MESSAGE- >> Hash: SHA1 >> >> On 11/25/2014 05:54 PM, Bill Moran wrote: >> >>> On Tue, 25 Nov 2014 17:27:18 +0100 Christoph Berg >>> wrote: >>> >>> Re: Bill Moran 2014-11-25 <20141125111630.d05d58a9eb083c7cf80ed...@potentialtech.com> > Anything with a journal is a performance problem. PostgreSQL > effectivly does its own journalling with the WAL logs. That's > not to say that there's no value to crash recovery to having a > journalling filesystem, but it's just to say that our > experience showed journaling filesystems to be slower. That > rules out ext4, unless you disable the journal. I seem to > remember ext4 with journalling disabled being one of the faster > filesystems, but I could be remembering wrong. > If you are using a non-journalling FS, you'll be waiting for a full fsck after a system crash. Not sure that's an improvement. >>> >>> It's an improvement if: a) You're investing in high-quality >>> hardware, so the chance of a system crash is very low. b) The >>> database is replicated, so your plan in the event of a primary >>> crash is to fail over to the backup anyway. >>> >>> If both of those are in place (as they were at my previous job) >>> then the time it takes to fsck isn't an issue, and taking action >>> that causes the database to run faster when nothing is wrong can be >>> considered. >>> >>> Obviously, the OP needs to assess the specific needs of the product >>> in question. Your point is very valid, and I'm glad you brought it >>> up (as a lot of people forget about it) but sometimes it's not the >>> most important factor. >>> >>> >> Thank you a lot to have shared with me your experiences. >> Indeed we will have two servers in cluster with high quality hardware >> so a fsck restore shouldn't be a big problem. >> I will analize the xfs option as well and then I will decide. >> >> Thank you again, >> Maila Fatticcioni >> >> > Also, if you do some timings, please share it with us, it'd be nice to > have some more data points. > > -Andy > > > > > > -- > 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] Active/Active clustering in postgres
On 11/26/2014 2:36 AM, Postgres India wrote: I am looking for PostgreSQL active/active clustering and whether PostgreSQL support any form of shared-storage clustering . Is there any methods or tools for implementing active/active clustering on Postgres supported by community or any third party tools. you might look into PostgreSQL-XC and -XL (-XL is a fork of -XC). both of these are multi-master clusters based on postgres. -- john r pierce 37N 122W somewhere on the middle of the left coast
Re: [GENERAL] [ADMIN] Active/Active clustering in postgres
It look's like you're searching for Postgres equivalent of Oracle RAC. I don't know if there is any solution to do this right now in the postgres On Wed, Nov 26, 2014 at 8:36 AM, Postgres India wrote: > Hi All, > > I am looking for PostgreSQL active/active clustering and whether PostgreSQL > support any form of shared-storage clustering . Is there any methods or > tools for implementing active/active clustering on Postgres supported by > community or any third party tools. > > > > Regards > > Manmohan >
Re: [GENERAL] Active/Active clustering in postgres
(2014/11/27 2:20), John R Pierce wrote: On 11/26/2014 2:36 AM, Postgres India wrote: I am looking for PostgreSQL active/active clustering and whether PostgreSQL support any form of shared-storage clustering . Is there any methods or tools for implementing active/active clustering on Postgres supported by community or any third party tools. you might look into PostgreSQL-XC and -XL (-XL is a fork of -XC). both of these are multi-master clusters based on postgres. In addition, XC and XL are shared-nothing DB clusters. I think that there is no multi-master shared-storage DB cluster based on PostgreSQL. -- 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] Active/Active clustering in postgres
* Suzuki Hironobu (hiron...@interdb.jp) wrote: > (2014/11/27 2:20), John R Pierce wrote: > >On 11/26/2014 2:36 AM, Postgres India wrote: > >>I am looking for PostgreSQL active/active clustering and whether > >>PostgreSQL support any form of shared-storage clustering . Is there > >>any methods or tools for implementing active/active clustering on > >>Postgres supported by community or any third party tools. > > > >you might look into PostgreSQL-XC and -XL (-XL is a fork of -XC). both > >of these are multi-master clusters based on postgres. > > In addition, XC and XL are shared-nothing DB clusters. > > I think that there is no multi-master shared-storage DB cluster > based on PostgreSQL. I'm not aware of any. It would require the equivilant of the distributed lock management which exists in RAC (and is also what makes RAC limited in its ability to scale..). Note that applications which are written to use RAC are likely easier to migrate to shared-nothing sharded database systems since they would have already had to deal with the complications associated with determining which node to send writes to. If your application isn't doing that then you're very unlikely to be using RAC in a performant manner (as it relates to write-load distribution, specifically) and would operate just fine with a single master and slave replicas, with appropriate systems in place to handle failover. Thanks, Stephen signature.asc Description: Digital signature
[GENERAL] Range type bounds
I am trying out the range types:
http://www.postgresql.org/docs/9.3/interactive/rangetypes.html
and got confused by the documentation wording for specifying no lower or
upper bound:
"The lower-bound may be either a string that is valid input for the
subtype, or empty to indicate no lower bound. Likewise, upper-bound may
be either a string that is valid input for the subtype, or empty to
indicate no upper bound."
What I saw was this:
aklaver@test=> select daterange('2014-11-01'::date,) ;
ERROR: syntax error at or near ")"
LINE 1: select daterange('2014-11-01'::date,) ;
aklaver@test=> select '[2014-11-01,)'::daterange;
daterange
---
[2014-11-01,)
(1 row)
aklaver@test=> select daterange('2014-11-01'::date, Null) ;
daterange
---
[2014-11-01,)
(1 row)
which when I got further into the docs was shown in this example:
8.17.6. Constructing Ranges
-- Using NULL for either bound causes the range to be unbounded on that
side.
SELECT numrange(NULL, 2.2);
I will leave it to philosophers to decide whether NULL is empty, but it
seems the documentation could be more explicit on what constitutes empty
in the text versus constructor method of creating a range.
Thanks,
--
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] Lock Management: Waiting on locks
Thanks Bill ! On Wed, Nov 26, 2014 at 9:07 AM, Bill Moran wrote: > In addition to what you're getting from that query, include the xact_start > and > state_change columns from pg_stat_activity. My guess is that your code is > starting a transaction, then running a query, then processing the query > results before committing the transaction. Since query locks are held for > the duration of the transaction, this will cause the locks to be held for a > long time if the processing step takes a while. > Actually each of this transaction is just single INSERT or single UPDATE query. > If that turns out not to be the problem, then you'll probably need to > provide > a bit more detail before anyone will be able to provide a better answer. I > mean, I'm even guessing that it's an app making the queries. Can you let me know what exact details should be provided here? Regards...
Re: [GENERAL] Range type bounds
Adrian Klaver-4 wrote
> I will leave it to philosophers to decide whether NULL is empty, but it
> seems the documentation could be more explicit on what constitutes empty
> in the text versus constructor method of creating a range.
Would it be sufficient to simply add another paragraph:
"The lower-bound may be either a string that is valid input for the subtype,
or NULL to indicate no lower bound. Likewise, upper-bound may be either a
string that is valid input for the subtype, or NULL to indicate no upper
bound."
?
@ 8.17.6. Constructing Ranges
I'm not particularly enamored with the title since "Range Input" is a means
of "Constructing [a] Range"...incorporating the word function into that
would seem warranted.
How about: 8.17.6 Functional Range Construction ?
For 8.17.5 The concept of "Input/Output" implies that we are dealing with
string-like literals and while not something an absolute beginner might pick
up on is likely sufficient and thus omitting the word "Literal" is OK by me.
All that said it is taken for granted that you cannot have an empty function
argument so ('val',) is invalid on its face. The question becomes whether
you should use ('val','') or ('val',NULL). The only place that is answered
is a single example. It should be in the body of the text too.
David J.
--
View this message in context:
http://postgresql.nabble.com/Range-type-bounds-tp5828396p5828402.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
Re: [GENERAL] [ADMIN] Active/Active clustering in postgres
There is BDR (Bi-Directional Replication) from 2ndQuadrant available in 9.4. http://2ndquadrant.com/en/resources/bdr/ -Greg On Wed, Nov 26, 2014 at 11:09 AM, Leonardo Carneiro wrote: > It look's like you're searching for Postgres equivalent of Oracle RAC. I > don't know if there is any solution to do this right now in the postgres > > On Wed, Nov 26, 2014 at 8:36 AM, Postgres India > wrote: > >> Hi All, >> >> I am looking for PostgreSQL active/active clustering and whether PostgreSQL >> support any form of shared-storage clustering . Is there any methods or >> tools for implementing active/active clustering on Postgres supported by >> community or any third party tools. >> >> >> >> Regards >> >> Manmohan >> > >
Re: [GENERAL] [ADMIN] Active/Active clustering in postgres
Is BDR still in beta? Here’s the postgres wiki with a chart: https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling From: pgsql-admin-ow...@postgresql.org [mailto:pgsql-admin-ow...@postgresql.org] On Behalf Of Greg Spiegelberg Sent: Wednesday, November 26, 2014 2:20 PM To: Leonardo Carneiro Cc: Postgres India; pgsql-general@postgresql.org >> PG-General Mailing List; [ADMIN] Subject: Re: [ADMIN] Active/Active clustering in postgres There is BDR (Bi-Directional Replication) from 2ndQuadrant available in 9.4. http://2ndquadrant.com/en/resources/bdr/ -Greg On Wed, Nov 26, 2014 at 11:09 AM, Leonardo Carneiro mailto:chesterma...@gmail.com>> wrote: It look's like you're searching for Postgres equivalent of Oracle RAC. I don't know if there is any solution to do this right now in the postgres On Wed, Nov 26, 2014 at 8:36 AM, Postgres India mailto:pgbugin...@gmail.com>> wrote: Hi All, I am looking for PostgreSQL active/active clustering and whether PostgreSQL support any form of shared-storage clustering . Is there any methods or tools for implementing active/active clustering on Postgres supported by community or any third party tools. Regards Manmohan
Re: [GENERAL] Range type bounds
On 11/26/2014 11:07 AM, David G Johnston wrote:
Adrian Klaver-4 wrote
I will leave it to philosophers to decide whether NULL is empty, but it
seems the documentation could be more explicit on what constitutes empty
in the text versus constructor method of creating a range.
Would it be sufficient to simply add another paragraph:
"The lower-bound may be either a string that is valid input for the subtype,
or NULL to indicate no lower bound. Likewise, upper-bound may be either a
string that is valid input for the subtype, or NULL to indicate no upper
bound."
Except that does not work in the text mode:( :
test=> select '[2014-11-01, NULL)'::daterange;
ERROR: invalid input syntax for type date: " NULL"
LINE 1: select '[2014-11-01, NULL)'::daterange;
test=> select '[2014-11-01, "NULL")'::daterange;
ERROR: invalid input syntax for type date: " NULL"
LINE 1: select '[2014-11-01, "NULL")'::daterange;
While testing the above I also got this:
test=> select '[2014-11-01, )'::daterange;
ERROR: invalid input syntax for type date: " "
LINE 1: select '[2014-11-01, )'::daterange;
while:
test=> select '[2014-11-01,)'::daterange;
daterange
---
[2014-11-01,)
(1 row)
worked. Seems there is a specific meaning to empty.
?
@ 8.17.6. Constructing Ranges
I'm not particularly enamored with the title since "Range Input" is a means
of "Constructing [a] Range"...incorporating the word function into that
would seem warranted.
How about: 8.17.6 Functional Range Construction ?
For 8.17.5 The concept of "Input/Output" implies that we are dealing with
string-like literals and while not something an absolute beginner might pick
up on is likely sufficient and thus omitting the word "Literal" is OK by me.
I guess what is confusing to me is the transition between the text mode
and the constructor mode is not clear. In particular the page starts
with examples using the constructor mode but then goes to explanations
that actually apply to the text mode before getting back to explaining
the constructor mode. I eventually figured it out. I just thought it
might make it easier for others to make the distinction clearer.
All that said it is taken for granted that you cannot have an empty function
argument so ('val',) is invalid on its face. The question becomes whether
you should use ('val','') or ('val',NULL). The only place that is answered
is a single example. It should be in the body of the text too.
Well I spend my time in Python for the most part so:
def test_fnc(a, b=None):
print a, b
In [12]: test_fnc('a',)
a None
I will have to plead ignorance on C.
David J.
--
View this message in context:
http://postgresql.nabble.com/Range-type-bounds-tp5828396p5828402.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
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] Range type bounds
> I guess what is confusing to me is the transition between the text mode
> and the constructor mode is not clear. In particular the page starts with
> examples using the constructor mode but then goes to explanations that
> actually apply to the text mode before getting back to explaining the
> constructor mode.
They are contained in separate subsections of the documentation...the
syntax described in each section only applies to that section. The concept
of empty doesn't apply to constructor functions at all.
The part that is problematic is the overloaded use of empty to mean a range
without bounds (a value) and a means to specify an infinite bound (an
input). Using "omitted" for the input case would probably add clarity.
I eventually figured it out. I just thought it might make it easier for
> others to make the distinction clearer.
Suggestions welcomed
>
>> All that said it is taken for granted that you cannot have an empty
>> function
>> argument so ('val',) is invalid on its face. The question becomes whether
>> you should use ('val','') or ('val',NULL). The only place that is
>> answered
>> is a single example. It should be in the body of the text too.
>>
>
> Well I spend my time in Python for the most part so:
>
> def test_fnc(a, b=None):
> print a, b
>
> In [12]: test_fnc('a',)
> a None
>
> I will have to plead ignorance on C.
It gets to be very verbose if we try to anticipate cross-language
differences and preemptively explain them away...
David J.
Re: [GENERAL] Range type bounds
On 11/26/2014 12:34 PM, David Johnston wrote:
I guess what is confusing to me is the transition between the text
mode and the constructor mode is not clear. In particular the page
starts with examples using the constructor mode but then goes to
explanations that actually apply to the text mode before getting
back to explaining the constructor mode.
They are contained in separate subsections of the documentation...the
syntax described in each section only applies to that section. The
concept of empty doesn't apply to constructor functions at all.
You get that from this?:
"
8.17.2. Examples
CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
(1108, '[2010-01-01 14:30, 2010-01-01 15:30)');
-- Containment
SELECT int4range(10, 20) @> 3;
-- Overlaps
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);
-- Extract the upper bound
SELECT upper(int8range(15, 25));
-- Compute the intersection
SELECT int4range(10, 20) * int4range(15, 25);
-- Is the range empty?
SELECT isempty(numrange(1, 5));
See Table 9-44 and Table 9-45 for complete lists of operators and
functions on range types.
8.17.3. Inclusive and Exclusive Bounds
Every non-empty range has two bounds, the lower bound and the upper
bound. All points between these values are included in the range. An
inclusive bound means that the boundary point itself is included in the
range as well, while an exclusive bound means that the boundary point is
not included in the range.
In the text form of a range, an inclusive lower bound is represented by
"[" while an exclusive lower bound is represented by "(". Likewise, an
inclusive upper bound is represented by "]", while an exclusive upper
bound is represented by ")". (See Section 8.17.5 for more details.)
The functions lower_inc and upper_inc test the inclusivity of the lower
and upper bounds of a range value, respectively.
8.17.4. Infinite (Unbounded) Ranges
The lower bound of a range can be omitted, meaning that all points less
than the upper bound are included in the range. Likewise, if the upper
bound of the range is omitted, then all points greater than the lower
bound are included in the range. If both lower and upper bounds are
omitted, all values of the element type are considered to be in the range.
This is equivalent to considering that the lower bound is "minus
infinity", or the upper bound is "plus infinity", respectively. But note
that these infinite values are never values of the range's element type,
and can never be part of the range. (So there is no such thing as an
inclusive infinite bound — if you try to write one, it will
automatically be converted to an exclusive bound.)
"
The part that is problematic is the overloaded use of empty to mean a
range without bounds (a value) and a means to specify an infinite bound
(an input). Using "omitted" for the input case would probably add clarity.
I eventually figured it out. I just thought it might make it easier
for others to make the distinction clearer.
Suggestions welcomed
Understood. Will see what I can come up with.
All that said it is taken for granted that you cannot have an
empty function
argument so ('val',) is invalid on its face. The question
becomes whether
you should use ('val','') or ('val',NULL). The only place that
is answered
is a single example. It should be in the body of the text too.
Well I spend my time in Python for the most part so:
def test_fnc(a, b=None):
print a, b
In [12]: test_fnc('a',)
a None
I will have to plead ignorance on C.
It gets to be very verbose if we try to anticipate cross-language
differences and preemptively explain them away...
Agreed. My example was as a counterpoint to your statement:
"All that said it is taken for granted that you cannot have an empty
function argument so ('val',) is invalid on its face."
It is not invalid on its face, just for this use case. I am not saying
explain all the exceptions, just the rule. In other words for the
purpose of this function at least two arguments must be provided. I
realize it does get covered in 8.17.6., but that is after the section I
quoted at the top which would seem to imply different. Have spent too
much time on this already, time to actually implement the suggestions:)
David J.
--
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] Range type bounds
On Wednesday, November 26, 2014, Adrian Klaver
wrote:
> On 11/26/2014 12:34 PM, David Johnston wrote:
>
>>
>> I guess what is confusing to me is the transition between the text
>> mode and the constructor mode is not clear. In particular the page
>> starts with examples using the constructor mode but then goes to
>> explanations that actually apply to the text mode before getting
>> back to explaining the constructor mode.
>>
>>
>> They are contained in separate subsections of the documentation...the
>> syntax described in each section only applies to that section. The
>> concept of empty doesn't apply to constructor functions at all.
>>
>
> You get that from this?:
>
> "
>
> 8.17.2. Examples
>
>
Examples do not constitute syntax specifications so while this useful for
understanding it is not enough to generalize from.
> Every non-empty range has two bounds, the lower bound and the upper bound.
> All points between these values are included in the range. An inclusive
> bound means that the boundary point itself is included in the range as
> well, while an exclusive bound means that the boundary point is not
> included in the range.
Use of empty as a value.
>
> The lower bound of a range can be omitted,
Omit is the best concept - implemented by a lack of value in a literal or a
null in a function call.
It gets to be very verbose if we try to anticipate cross-language
>> differences and preemptively explain them away...
>>
>
> Agreed. My example was as a counterpoint to your statement:
>
> "All that said it is taken for granted that you cannot have an empty
> function argument so ('val',) is invalid on its face."
>
> It is not invalid on its face, just for this use case.
Less a use case than a language/system. I do not recall any case where you
can call a function defined in PostgreSQL and leave an argument position
defined but empty. You can skip providing the given position and use
defaults but dangling commas are not allowed. That is taken for granted by
the people writing documentation.
> I am not saying explain all the exceptions, just the rule. In other words
> for the purpose of this function at least two arguments must be provided. I
> realize it does get covered in 8.17.6., but that is after the section I
> quoted at the top which would seem to imply different. Have spent too much
> time on this already, time to actually implement the suggestions:)
>
>
Yes, examples before definition is not all the common in the docs...
David J.
[GENERAL] change data type from text to numeric
Hi everyone, sorry to bother you with a simple question, (I'm a new user of postgresql), how do I change the data type of a column of text, to numeric or integer? I tried with: ALTER TABLE table_name ALTER COLUMN col_name TYPE integer ; It gives me a mistake ERROR: la columna no puede convertirse automáticamente al tipo integer SUGERENCIA: Especifique una expresión USING para llevar a cabo la conversión. translation: the column can't be converted automatically to type integer specify an expression using USING to make the conversion. I also tried on pgadmin3, but couldn't found how to do it... thanks, Daniel
Re: [GENERAL] change data type from text to numeric
Daniel Torres wrote > Hi everyone, > > sorry to bother you with a simple question, (I'm a new user of > postgresql), > how do I change the data type of a column of text, to numeric or integer? > > I tried with: > > ALTER TABLE table_name > ALTER COLUMN col_name TYPE integer > ; > > It gives me a mistake > ERROR: la columna no puede convertirse automáticamente al tipo integer > SUGERENCIA: Especifique una expresión USING para llevar a cabo la > conversión. > > translation: the column can't be converted automatically to type integer > specify an expression using USING to make the conversion. > > I also tried on pgadmin3, but couldn't found how to do it... > > thanks, > Daniel There is a serviceable example of the USING variation of ALTER TABLE .. ALTER COLUMN in the documentation. http://www.postgresql.org/docs/9.3/interactive/sql-altertable.html The simplest conversion is simply "col_name::numeric"... David J. -- View this message in context: http://postgresql.nabble.com/change-data-type-from-text-to-numeric-tp5828434p5828438.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
Re: [GENERAL] change data type from text to numeric
On Wed, 26 Nov 2014 15:40:53 -0600 Daniel Torres wrote: > Hi everyone, > > sorry to bother you with a simple question, (I'm a new user of postgresql), > how do I change the data type of a column of text, to numeric or integer? > > I tried with: > > ALTER TABLE table_name > ALTER COLUMN col_name TYPE integer > ; > > It gives me a mistake > ERROR: la columna no puede convertirse automáticamente al tipo integer > SUGERENCIA: Especifique una expresión USING para llevar a cabo la > conversión. > > translation: the column can't be converted automatically to type integer > specify an expression using USING to make the conversion. See the docs for ALTER TABLE, the section on USING: http://www.postgresql.org/docs/9.3/static/sql-altertable.html Error messages are your friend, read them. But short answer: ALTER TABLE table_name ALTER COLUMN col_name TYPE integer USING CAST(col_name AS INT); Which will work as long as all the values can be cast to an INT without error. If you have values that can't be cast without error, you'll have to fix them before you can do the ALTER. -- Bill Moran I need your help to succeed: http://gamesbybill.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] is there a warm standby sync trigger?
On Tue, Nov 25, 2014 at 6:37 PM, Sameer Kumar wrote: > > On Fri, Oct 24, 2014 at 8:01 PM, Michael Paquier > wrote: >> >> On Fri, Oct 24, 2014 at 12:30 AM, John Smith >> wrote: >> > >> > i want to setup a warm standby that listens 24/7 but only syncs when >> > told to (ie only when i am ok with the database updates, will i >> > trigger the sync). >> > can i? >> > >> > i don't want to manually backup and restore like i do now. >> >> That's what pause_at_recovery_target is aimed for: >> http://www.postgresql.org/docs/devel/static/recovery-target-settings.html >> Simply set up the recovery target you want to check, and use >> pause_at_recovery_target to put the standby in a latent state you can >> check. If the state of your server does not satisfy your needs, >> shutdown the server and change the target. Note that operations are >> not backward btw. > > > If you were using hot-standby, you could have used pg_pause_recovery() / > pg_resume_recovery() > > To pause when needed and continue later. e.g. when you are doing bulk loads > or during a window when you see network congestion. > > > Best Regards, > > Sameer Kumar | Database Consultant > > ASHNIK PTE. LTD. > > 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533 > > M: +65 8110 0350 T: +65 6438 3504 | www.ashnik.com > > > > > > This email may contain confidential, privileged or copyright material and is > solely for the use of the intended recipient(s). I have never heard of pg_pause_recovery() or pg_resume_recovery(). However, you can use pg_xlog_replay_pause() and pg_xlog_replay_resume() to pause and resume recovery. As Sameer mentioned, they require the system to be in hot standby mode to use them. -- 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] Avoiding deadlocks when performing bulk update and delete operations
On Wed, Nov 26, 2014 at 11:47 AM, Bill Moran wrote: > On Wed, 26 Nov 2014 10:41:56 +1100 > Sanjaya Vithanagama wrote: > > > > > * How frequently do deadlocks occur? > > > > We are seeing deadlocks about 2-3 times per day in the production server. > > To reproduce the problem easily we've written a simple Java class with > > multiple threads calling to the stored procedures running the above > queries > > inside a loop. This way we can easily recreate a scenario that happens in > > the production. > > Don't overcomplicate your solution. Adjust your code to detect the deadlock > and replay the transaction when it happens. At 2-3 deadlocks per day, it's > difficult to justify any other solution (as any other solution would be > more time-consuming to implement, AND would interfere with performance). > When you say replay the transaction, I believe that is to catch the exception inside the stored procedure? We've considered that option at one state but, the problem with that is we don't have enough context information at the stored procedure where this deadlock occurs. > > I've worked with a number of write-heavy applications that experienced > deadlocks, some of them on the order of hundreds of deadlocks per day. > In some cases, you can adjust the queries to reduce the incidence of > deadlocks, or eliminate the possibility of deadlocks completely. The > situation that you describe is not one of those cases, as the planner > can choose to lock rows in whatever order it thinks it most efficient > and you don't have direct control over that. > > The performance hit you'll take 2-3 times a day when a statement has to > be replayed due to deadlock will hardly be noticed (although a statement > that takes 50 seconds will cause raised eyebrows if it runs 2x) but that > will only happen 2-3 times a day, and the solution I'm proposing won't > have any performance impact on the other 1300 queries per day that > don't deadlock. > > 2-3 deadlocks per day is normal operation for a heavily contented table, > in my experience. > Given that we have no control over how Postgres performs delete and update operations, the only other possibility seems to be to partition this table by id_A (so that the individual tables will never be deadlocked). But that seems to be a too extreme end option at this stage. > > -- > Bill Moran > I need your help to succeed: > http://gamesbybill.com > -- Sanjaya
