Re: [GENERAL] looking for old rpm

[Devrim GÜNDÜZ]

Hi,

On Mon, 2015-09-21 at 09:31 +0200, Etienne Champetier wrote:
> 
> I'm looking for old postgres rpm, like
> postgresql93-server-9.3.6-1PGDG.rhel6.x86_64.rpm
> 
> (one of our software is "certified" with this minor version ...)

We don't keep those RPMS, but you can build the RPMs by yourself. 

ftp://ftp.postgresql.org/pub/source/v9.3.6/

are the sources, and 

http://people.planetpostgresql.org/devrim/index.php?/archives/44-How-To
-Build-Your-Own-PostgreSQL-and-related-software-RPMs-on
-CentOSRHELFedora.html

is the guide.

Regards,
-- 
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] looking for old rpm

[Etienne Champetier]

Hi,

I'm looking for old postgres rpm, like
postgresql93-server-9.3.6-1PGDG.rhel6.x86_64.rpm

(one of our software is "certified" with this minor version ...)

Thanks in advance
Etienne

Re: [GENERAL] Rounding Float Array

[Andreas Kretschmer]

hubert depesz lubaczewski  wrote:

> > Is there an easy way to round all values to 4 decimals.
> 
> Sure:
> 
> (depesz@[local]:5960) 12:15:46 [depesz] 
> $ select geoloc::numeric(8,4)[] from alex;
>   geoloc
> ---
>  {5.3443,100.2946}

Nice!


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


-- 
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] Rounding Float Array

[Chris Mair]

>> Sure:
>>
>> (depesz@[local]:5960) 12:15:46 [depesz] 
>> $ select geoloc::numeric(8,4)[] from alex;
>>   geoloc
>> ---
>>  {5.3443,100.2946}
> 
> Nice!

Indeed :)

Bye,
Chris.





-- 
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] Rounding Float Array

[hubert depesz lubaczewski]

On Mon, Sep 21, 2015 at 11:55:23AM +0200, Alex Magnum wrote:
> Hello,
> 
> I have a float array holding geo location information.
> 
>   geoloc
> ---
>  {5.3443133704554,100.29457569122}
>  {5.3885574294704,100.29601335526}
>  {3.1654978750403,101.60915851593}
>  {5.3766154817748,100.31472444534}
>  {3.1545014704258,101.70036971569}
> (5 rows)
> Is there an easy way to round all values to 4 decimals.

Sure:
$ select geoloc from alex;
  geoloc   
---
 {5.3443133704554,100.29457569122}
 {5.3885574294704,100.29601335526}
 {3.1654978750403,101.60915851593}
 {5.3766154817748,100.31472444534}
 {3.1545014704258,101.70036971569}
(5 rows)

(depesz@[local]:5960) 12:15:46 [depesz] 
$ select geoloc::numeric(8,4)[] from alex;
  geoloc
---
 {5.3443,100.2946}
 {5.3886,100.2960}
 {3.1655,101.6092}
 {5.3766,100.3147}
 {3.1545,101.7004}
(5 rows)

depesz


-- 
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] pgsql-95 repo in rsync

[Devrim GÜNDÜZ]

Hi,

On Mon, 2015-09-21 at 12:46 +, Kjetil Nygård wrote:

> 1. rsync
> Would it be possible to have PostgreSQL 9.5 in the rsync-repo?

Emailed PostgreSQL.org sysadmins, and I'll let you know when that is
done.

> 2. yum repo for Fedora 23
> Is it possible to have fedora 23-repo for PostgreSQL? Especially 9.4
> and 9.5.
>*** Is there anything I can do, to help with this?
> 

We don't provide repos for alpha/beta Fedora releases. Sorry about
that. You can check back when F23 is stable. Still, you can build your
own RPMs using the guide here:

http://people.planetpostgresql.org/devrim/index.php?/archives/44-How-To
-Build-Your-Own-PostgreSQL-and-related-software-RPMs-on
-CentOSRHELFedora.html

I believe you can use Fedora 22 stuff on Fedora 23, too.

Regards,
-- 
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR





-- 
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] pgsql-95 repo in rsync

[John R Pierce]

On 9/21/2015 5:46 AM, Kjetil Nygård wrote:

2. yum repo for Fedora 23
Is it possible to have fedora 23-repo for PostgreSQL? Especially 9.4 
and 9.5.


I thought F23 starts beta next week?

it appears 9.4.4 is included in the F23 base repository.

postgres 9.5 is itself still in beta.

--
john r pierce, recycling bits in santa cruz



--
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] pgsql-95 repo in rsync

[Adrian Klaver]

On 09/21/2015 05:46 AM, Kjetil Nygård wrote:

Hi,

I have two questions about PostgreSQL repositories:

1. rsync
Would it be possible to have PostgreSQL 9.5 in the rsync-repo?


I am not understanding what you mean above.

In other words not sure what Postgres has to do with rsync?



2. yum repo for Fedora 23
Is it possible to have fedora 23-repo for PostgreSQL? Especially 9.4 and
9.5.
*** Is there anything I can do, to help with this?


--
Med vennleg helsing

Kjetil Nygård
Technical Expert | Ambita AS

k...@ambita.com 
Mobile: +47 41 47 43 37
Call senter: +47 24 13 35 00
https://www.ambita.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] pgsql-95 repo in rsync

[Devrim GÜNDÜZ]

Hi,

On Mon, 2015-09-21 at 15:46 -0700, Adrian Klaver wrote:
> In other words not sure what Postgres has to do with rsync?

I think OP refers to the YUM repo.

Regards,
-- 
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Queuing query

[Steve Crawford]

While awaiting the awesomeness of the upcoming "skip locked" feature in 9.5
I need to handle a work queue.

Does anyone see any glaring issues or subtle nuances with the basic method
below which combines CTEs with queue-handling methods posted by depesz, on
the PG wiki and elsewhere.

Note that it appears that there is the slight potential for a
race-condition which would cause one worker to occasionally fail to get a
record but the application code handles that issue fine.

The work is sent to an externally hosted API which will ultimately reply to
a callback API at our end so obviously there's a lot of other stuff in the
system to update final results, recover from lost work, add to the queue,
etc. I'm just asking about the sanity of the queue processing query itself:

with next_up as (
   select
the_id
from
queuetest
   where
not sent_for_processing
and pg_try_advisory_xact_lock(12345, the_id)
   order by
the_priority
limit 1 for update)
update
queuetest
set
sent_for_processing = true
where
the_id = (select the_id from next_up)
returning
the_work_to_do;

Cheers,
Steve

Re: [GENERAL] Broken primary key after backup restore.

[Michael Chau]

Hi,

Just want to let everybody knows

So, I found out this morning what went wrong.

For some reason, there were some bad wal log files in pg_xlog. I believe
that they got generated during the backup last Monday, but I don't know
why. I speculate that may be the wal_keep_segments was not set high enough
as I have changed it recently.

Luckily, I have archived the wal log files. And by comparing between the
two directories, I did see those bad wal log files in pg_xlog directory
only.

Yes, we were looking at Barman before. But at that time Barman didn't
support our version of PostgreSQL. I think that I will pursue this.

Thanks again.

On Fri, Sep 18, 2015 at 2:40 PM, David Steele  wrote:

> On 9/18/15 3:44 PM, Michael Chau wrote:
>
>> Hi Jeff,
>>
>> Only if you are very lucky.  If your tar command tars up the pg_xlog
>>> directory as the last thing it does, then you are probably going to be OK.
>>> Otherwise, it is a crap shoot.
>>>
>>
>> May be that's it. I have another similar set up, but the pg_xlog is a
>> soft link to another directory, and I use 'tar -chvzf'. It tar up the
>> pg_xlog at the very last. And the restore is fine.
>>
>
> This is still not always safe.  It depends on your wal_keep_segments
> settings and some luck.  WAL segments can be recycled during the backup.
>
> For this one, DB1 and DB2, the pg_xlog is the directory itself, and I
>> use 'tar -cvzf'. And it tar up pg_xlog at the beginning. I always have
>> doubt about it. But I though pg_stop_backup() and pg_start_backup() like
>> freezing would prevent the inconsistency.
>>
>
> This is definitely not a good idea.
>
> Indeed, I will look inot pgbasebackup.
>>
>
> pg_basebackup is good for creating replicas but for real backup you might
> want to consider purpose-built backup software like pgBackRest or barman.
>
> --
> -David
> da...@pgmasters.net
>



-- 
*Michael Chau*
*Database Administrator*
*GAME GOLF*
77 Geary St, 5th floor
San Francisco, CA 94108
c) *510-366-3800*
e) *michael.c...@gameyourgame.com *
f) www.facebook.com/gamegolf 
t) @GAMEGOLF
w) www.gamegolf.c *om*

Re: [GENERAL] looking for old rpm

[John R Pierce]

On 9/21/2015 12:31 AM, Etienne Champetier wrote:

I'm looking for old postgres rpm, like
postgresql93-server-9.3.6-1PGDG.rhel6.x86_64.rpm

(one of our software is "certified" with this minor version ...)


I cringe when I see requirements like this. did they also 'certify' 
this applicaiton to only run on whatever was the day's snapshot build of 
the OS, such that any newer security updates and bug fixes can't be 
applied to RHEL 6 ?


specific to 9.3.6, you'd be missing out on the fixes ...
http://www.postgresql.org/docs/current/static/release-9-3-7.html
http://www.postgresql.org/docs/current/static/release-9-3-8.html
http://www.postgresql.org/docs/current/static/release-9-3-9.html


--
john r pierce, recycling bits in santa cruz



--
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] looking for old rpm

[John McKown]

On Mon, Sep 21, 2015 at 10:46 AM, John R Pierce  wrote:

> On 9/21/2015 12:31 AM, Etienne Champetier wrote:
>
>> I'm looking for old postgres rpm, like
>> postgresql93-server-9.3.6-1PGDG.rhel6.x86_64.rpm
>>
>> (one of our software is "certified" with this minor version ...)
>>
>
> I cringe when I see requirements like this. did they also 'certify'
> this applicaiton to only run on whatever was the day's snapshot build of
> the OS, such that any newer security updates and bug fixes can't be applied
> to RHEL 6 ?
>

​We have a few products like that here. One of our Windows servers is
"stabilized" in that it cannot be patched or upgraded because a single
piece of critical software is running on it which is not only certified
only on that particular release / patch, but actually fails on more up to
date servers. In addition, it is "abandon-ware"​ in that that original
company is defunct. Oh, and it cannot be replaced because that part of the
business is being eliminated, so this is only for "historical" clients, who
will be gone by the end of this year. At least, in that, we do have a hard
time limit and then the software / machine will be junked.

The above may be why, at least historically, businesses have like Windows.
With Windows there is "one true way". _Their_ way. I would say more, but
that would become too political.



>
> specific to 9.3.6, you'd be missing out on the fixes ...
> http://www.postgresql.org/docs/current/static/release-9-3-7.html
> http://www.postgresql.org/docs/current/static/release-9-3-8.html
> http://www.postgresql.org/docs/current/static/release-9-3-9.html
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[GENERAL] postgres 9.3

[Ramesh T]

I have one database and two schemas in that public,preview and role preview
automatically i want to connect preview schema with preview role.

set search_path to preview.
show search_path;
..
preview.


when disconnect and connect database it's showing $user$:public schema not
showing preview.

what is the problem..?how to resolve the issue...?


when session closed showing postgres user..even set preview.

[GENERAL] pgsql-95 repo in rsync

[Kjetil Nygård]

Hi,

I have two questions about PostgreSQL repositories:

1. rsync
Would it be possible to have PostgreSQL 9.5 in the rsync-repo?

2. yum repo for Fedora 23
Is it possible to have fedora 23-repo for PostgreSQL? Especially 9.4 and 9.5.
   *** Is there anything I can do, to help with this?


--
Med vennleg helsing

Kjetil Nygård
Technical Expert | Ambita AS

k...@ambita.com
Mobile: +47 41 47 43 37
Call senter: +47 24 13 35 00
https://www.ambita.com

Re: [GENERAL] @ operator

[Igor Neyman]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of ? 
Sent: Thursday, September 17, 2015 9:11 AM
To: pgsql-general@postgresql.org
Cc: Антон Бушмелев 
Subject: [GENERAL] @ operator

Hello, google nothing  for @ operator =(,  what does this mean  field1 <@ ( 
subquery ) ?


ps: sorry for my English


Could be absolute-value operator for numeric data types,
or array (or range) inclusion operator.

See PG docs (and these docs should be your first option, before googling):
http://www.postgresql.org/docs/9.4/static/typeconv-oper.html

Regards,
Igor Neyman

Re: [GENERAL] looking for old rpm

[Etienne Champetier]

Hi,

2015-09-21 17:46 GMT+02:00 John R Pierce :

> On 9/21/2015 12:31 AM, Etienne Champetier wrote:
>
>> I'm looking for old postgres rpm, like
>> postgresql93-server-9.3.6-1PGDG.rhel6.x86_64.rpm
>>
>> (one of our software is "certified" with this minor version ...)
>>
>
> I cringe when I see requirements like this. did they also 'certify'
> this applicaiton to only run on whatever was the day's snapshot build of
> the OS, such that any newer security updates and bug fixes can't be applied
> to RHEL 6 ?
>

Yes they do :), they certify centos 6.5 stock, without updates \o/
We are going to update, just when we call the support we will have to prove
that it's there app that is buggy, not all the (minor) updates ...

Having old rpm can also be useful to build new dev env in sync with
production env (when you update slowly)


>
> specific to 9.3.6, you'd be missing out on the fixes ...
> http://www.postgresql.org/docs/current/static/release-9-3-7.html
> http://www.postgresql.org/docs/current/static/release-9-3-8.html
> http://www.postgresql.org/docs/current/static/release-9-3-9.html
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> 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] @ operator

[Bushmelev Anton]

Thanks for reply, it is clear now =)

On 09/17/2015 08:34 PM, David G. Johnston wrote:
On Thursday, September 17, 2015, David G. Johnston 
> wrote:


On Thursday, September 17, 2015, Антон Бушмелев
> wrote:

Hello, google nothing  for @ operator =(,  what does this mean
 field1 <@ ( subquery ) ?


It would depend on what field1 is.

Typically <@ is a contains/contained-by operator.  Is this context
the subquery is checked to see if it contains the value of field1.

David J.


To clarify I think that it means that for each row returned by the 
subquery, the value of filed1 is checked to see if the field in the 
subquery contains the given value.  Use use ANY/ALL to indicate 
whether to care if on,y so,e rows or all rows need match in order for 
the entire expression to pass.


David J.

[GENERAL] Memory for BYTEA returned by C function is not released until connection is dropped

[John Leiseboer]

I have written a number of functions in C that return BYTEA type. I have 
compiled and run on both Windows and Linux, 32-bit and 64-bit, PostgreSQL 
versions 9.3 and 9.4.

My functions return BYTEA data to the caller. The problem is that memory usage 
grows until there is no memory left on the host, at which point an error is 
returned. If I drop the connection (e.g. by quitting from pqsql), the memory is 
returned.

I wrote the following minimal function to test palloc() and BYTEA return 
behaviour, and found that this minimal program also exhibits the unbounded 
memory growth problem.


C source code:

PG_FUNCTION_INFO_V1(test_palloc);
Datum test_palloc()
{
bytea *test_ret;
int test_len = 1024;

test_ret = (bytea *)palloc(test_len + VARHDRSZ);
SET_VARSIZE(test_ret, test_len + VARHDRSZ);
PG_RETURN_BYTEA_P(test_ret);
}

Function definition:

CREATE OR REPLACE FUNCTION test_palloc() RETURNS BYTEA
AS E'', test_palloc' LANGUAGE C IMMUTABLE STRICT;

psql commands to reproduce the problem:

\o out.txt
SELECT ids.*, test_palloc() FROM GENERATE_SERIES(1, 100) ids;

At the completion of the above command, host memory will have been consumed but 
not released back to the system. After quitting psql (\q), memory is released.

Is this expected behaviour or a bug? Am I doing something wrong? How can I 
return a BYTEA type from a C library function without having to drop the 
connection in order to recover the allocated memory that is returned to the 
caller?

Regards,
John


-- 
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] Memory for BYTEA returned by C function is not released until connection is dropped

[Pavel Stehule]

2015-09-21 4:31 GMT+02:00 John Leiseboer :

> I have written a number of functions in C that return BYTEA type. I have
> compiled and run on both Windows and Linux, 32-bit and 64-bit, PostgreSQL
> versions 9.3 and 9.4.
>
> My functions return BYTEA data to the caller. The problem is that memory
> usage grows until there is no memory left on the host, at which point an
> error is returned. If I drop the connection (e.g. by quitting from pqsql),
> the memory is returned.
>
> I wrote the following minimal function to test palloc() and BYTEA return
> behaviour, and found that this minimal program also exhibits the unbounded
> memory growth problem.
>
>
> C source code:
>
> PG_FUNCTION_INFO_V1(test_palloc);
> Datum test_palloc()
> {
> bytea *test_ret;
> int test_len = 1024;
>
> test_ret = (bytea *)palloc(test_len + VARHDRSZ);
> SET_VARSIZE(test_ret, test_len + VARHDRSZ);
> PG_RETURN_BYTEA_P(test_ret);
> }
>
> Function definition:
>
> CREATE OR REPLACE FUNCTION test_palloc() RETURNS BYTEA
> AS E'', test_palloc' LANGUAGE C IMMUTABLE STRICT;
>
> psql commands to reproduce the problem:
>
> \o out.txt
> SELECT ids.*, test_palloc() FROM GENERATE_SERIES(1, 100) ids;
>
> At the completion of the above command, host memory will have been
> consumed but not released back to the system. After quitting psql (\q),
> memory is released.
>
> Is this expected behaviour or a bug? Am I doing something wrong? How can I
> return a BYTEA type from a C library function without having to drop the
> connection in order to recover the allocated memory that is returned to the
> caller?
>
>
This memory is release, when memory context is dropped.

http://www.neilconway.org/talks/hacking/hack_slides.pdf look on slide 15

Regards

Pavel


> Regards,
> John
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[GENERAL] Rounding Float Array

[Alex Magnum]

Hello,

I have a float array holding geo location information.

  geoloc
---
 {5.3443133704554,100.29457569122}
 {5.3885574294704,100.29601335526}
 {3.1654978750403,101.60915851593}
 {5.3766154817748,100.31472444534}
 {3.1545014704258,101.70036971569}
(5 rows)

Is there an easy way to round all values to 4 decimals.

I can round the individual values and return them seperately but I need to
return them as an array.

  lat|   long
-+---
 5.34431 | 100.29458
 5.38856 | 100.29601
 3.16550 | 101.60916
 5.37662 | 100.31472
 3.15450 | 101.70037
(5 rows)

Any suggestion is highly appreciated.

Thanks
Alex

Re: [GENERAL] Rounding Float Array

[Chris Mair]

On 21/09/15 11:55, Alex Magnum wrote:
> Hello,
> 
> I have a float array holding geo location information.
> 
>  geoloc
> ---
>  {5.3443133704554,100.29457569122}
>  {5.3885574294704,100.29601335526}
>  {3.1654978750403,101.60915851593}
>  {5.3766154817748,100.31472444534}
>  {3.1545014704258,101.70036971569}
> (5 rows)
> 
> Is there an easy way to round all values to 4 decimals.
> 
> I can round the individual values and return them seperately but I need to 
> return them as an array. 
> 
>  lat|   long
> -+---
>  5.34431 | 100.29458
>  5.38856 | 100.29601
>  3.16550 | 101.60916
>  5.37662 | 100.31472
>  3.15450 | 101.70037
> (5 rows)
> 
> Any suggestion is highly appreciated.


This might work for you.

Bye,
Chris.

chris=# select * from geoloc;
  geoloc   
---
 {5.3443133704554,100.29457569122}
 {5.3885574294704,100.29601335526}
 {3.1654978750403,101.60915851593}
 {5.3766154817748,100.31472444534}
 {3.1545014704258,101.70036971569}
(5 rows)

chris=# select (select array_agg(to_char(x, '999.')::float) from 
unnest(geoloc) as x) from geoloc;;
 array_agg 
---
 {5.3443,100.2946}
 {5.3886,100.296}
 {3.1655,101.6092}
 {5.3766,100.3147}
 {3.1545,101.7004}
(5 rows)






-- 
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] Rounding Float Array

[Andreas Kretschmer]

Alex Magnum  wrote:

> Hello,
> 
> I have a float array holding geo location information.
> 
>               geoloc
> ---
>  {5.3443133704554,100.29457569122}
>  {5.3885574294704,100.29601335526}
>  {3.1654978750403,101.60915851593}
>  {5.3766154817748,100.31472444534}
>  {3.1545014704258,101.70036971569}
> (5 rows)
> 
> Is there an easy way to round all values to 4 decimals.

sure:

test=*# select * from alex ;
  geoloc
---
 {5.3443133704554,100.29457569122}
 {5.3885574294704,100.29601335526}
 {3.1654978750403,101.60915851593}
 {5.3766154817748,100.31472444534}
(4 rows)

Time: 0,245 ms
test=*# update alex set geoloc = array[round(geoloc[1]::numeric,4),
round(geoloc[2]::numeric,4)];
UPDATE 4
Time: 0,454 ms
test=*# select * from alex ;
  geoloc
---
 {5.3443,100.2946}
 {5.3886,100.296}
 {3.1655,101.6092}
 {5.3766,100.3147}
(4 rows)




Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] md5(large_object_id)

[Karsten Hilbert]

I have searched the mailing list archives as well as the
internet at large but haven't been able to find a _streaming_
version of md5 hashing for large objects. The closest thing I
found is this thread


http://www.postgresql.org/message-id/5d0f60990704081003p615530acx86257a70bff33...@mail.gmail.com

which eventually lead to this code

http://llg.cubic.org/pg-mdagg/

which, however, doesn't stream in the large object data
either. Essentially, for hashing _very_ large objects (to the
order of several GB per object, I am dealing with radiology
studies aka DICOM data) one would want an md5 function which
streams in parts of a large object piece by piece using
md5_update and m5_finalize or some such.

It didn't look like pgcrypto offers a streaming version either.

Anything I am overlooking ?

Thanks,
Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
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] postgres 9.3

[Melvin Davidson]

You are correct John, but in this case, he stated only one (1) database. So
changing the search path in .postgresql.conf  simplifies things for all
users.
However, should he create additional databases later on, then yes, your
solution would be better.

On Mon, Sep 21, 2015 at 3:08 PM, Alvaro Herrera 
wrote:

> John R Pierce wrote:
>
> > better would be to...
> >
> > ALTER ROLE username SET SEARCH_PATH='preview,"$user", public';
> > or
> > ALTER DATABASE dbname SET...;
> >
> > and then this change just applies to that named role or database...
>
> (or
>ALTER ROLE username IN DATABASE dbname SET ..
> which applies to the specified role in the specified database)
>
> --
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] postgres 9.3

[John R Pierce]

On 9/21/2015 11:52 AM, Melvin Davidson wrote:

You cannot directly "connect" one schema with another,
but you can set the default to have both in the search_path.
Uncomment the #search_path parameter in Postgresql.conf
and change it to
search_path = 'preview, role,public,"$user",public' 


the above would change it globally for all databases and users on the 
system, which is, IMHO, probably not what you want to do.


better would be to...

ALTER ROLE username SET SEARCH_PATH='preview,"$user", public';
or
ALTER DATABASE dbname SET...;

and then this change just applies to that named role or database...

to the OP,  a simple SET    only applies to the current session, its 
not remembered.   ALTER ROLE (or ALTER DATABASE) will remember the 
setting for the specified user or database.


but if as you said, its the "preview" role that you want to access the 
"preview" schema, then the default search path would suffice, as $USER 
== preview.




--
john r pierce, recycling bits in santa cruz



--
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] postgres 9.3

[Melvin Davidson]

You cannot directly "connect" one schema with another,
but you can set the default to have both in the search_path.
Uncomment the #search_path parameter in Postgresql.conf
and change it to
search_path = 'preview, role,public,"$user",public'

The make sure you do:
sudo su - postgres
pg_ctl reload [-D datadir]

On Sat, Sep 19, 2015 at 4:36 AM, Ramesh T 
wrote:

> I have one database and two schemas in that public,preview and role preview
> automatically i want to connect preview schema with preview role.
>
> set search_path to preview.
> show search_path;
> ..
> preview.
>
>
> when disconnect and connect database it's showing $user$:public schema not
> showing preview.
>
> what is the problem..?how to resolve the issue...?
>
>
> when session closed showing postgres user..even set preview.
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] postgres 9.3

[Alvaro Herrera]

John R Pierce wrote:

> better would be to...
> 
> ALTER ROLE username SET SEARCH_PATH='preview,"$user", public';
> or
> ALTER DATABASE dbname SET...;
> 
> and then this change just applies to that named role or database...

(or
   ALTER ROLE username IN DATABASE dbname SET ..
which applies to the specified role in the specified database)

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general