Re: postgres server process crashes when using odbc_fdw

[Laurenz Albe]

Ravi Krishna wrote:
> On a related note is fdw for Oracle and SQLServer supported by the community ?

The PostgreSQL Global Development Group, specifically these mailing lists,
will provide support for "core PostgreSQL", that is the source code you can
download from www.postgresql.org and binaries built from that source.

The support here does not provide any guarantees, but it also has no
well-defined limits.  So if you ask well, people will also try to help you
with versions that are "out of support" or third-party products like the
foreign data wrappers you mention above.

Likewise, "the community" has no well-defined limits: in a way, people who
provide third-party open source extensions also are a part of the community,
or at least the communities overlap.

That said, it is a good idea to contact the maintainers of those third-party
extensions first: usually, they know more about their product than the general
public, and it also keeps mails off the main list that are of little interest
to most people.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

convert real to numeric.

[Alessandro Aste]

Hi,



Postresql version: 10.5



I need to convert an SQL field from real to numeric, but I’m getting a
strange behavior.



See the following query in preprod:



select amount, amount::numeric, amount::numeric(16,4),
amount::varchar::numeric from mytable where id = 32560545;



Result:

17637.75, 17637.8, 17637.8000, 17637.75



As you can see, the conversion to ::numeric is truncating the number to
just 1 decimal digit.



Also we tried to  change the schema definition of this table, from real to
numeric, and the value was truncated.



Is the ::varchar::numeric workaround a good option in your opinion? Any
other ideas to fix this issue?



Thank you,

pg_dump backup utility is taking more time around 24hrs to take the backup of 28GB

[Raghavendra Rao J S V]

Hi All,

We are using *pg_dump *backup utility in order to take the backup of the
database. Unfortunately,it is taking around 24hrs of time  to take the
backup of  28GB database. Please guide me how to reduce the time and is
there any parameter need to be modified which will help us to reduce the
backup time. We are using Postgres 9.2 version

*Note:-*Kindly suggest me options using pg_dump only.

-- 
Regards,
Raghavendra Rao

Re: pg_dump backup utility is taking more time around 24hrs to take the backup of 28GB

[Ozz Nixon]

There are many possible problems, could you share your command line?

For example. Dump on SAME drive as the DB files, can produce disk
contention. Dump across the network, can have packet collision of network
latency. I dump 20gb test server in a matter of a couple minutes here. But,
I run multiple disk controllers, data drive one 1 iSCSI and archives on
another iSCSI - both run SSD ... so obviously, in this design I am simply
moving RAM from 1 gig channel to another 1 gig channel, both are private
circuits - no network conflict, and I average no less than 1GBps A to B
stack performance.

On Thu, Oct 18, 2018 at 8:25 AM Raghavendra Rao J S V <
raghavendra...@gmail.com> wrote:

> Hi All,
>
> We are using *pg_dump *backup utility in order to take the backup of the
> database. Unfortunately,it is taking around 24hrs of time  to take the
> backup of  28GB database. Please guide me how to reduce the time and is
> there any parameter need to be modified which will help us to reduce the
> backup time. We are using Postgres 9.2 version
>
> *Note:-*Kindly suggest me options using pg_dump only.
>
> --
> Regards,
> Raghavendra Rao
>
>

Re: convert real to numeric.

[Adrian Klaver]

On 10/18/18 3:28 AM, Alessandro Aste wrote:

Hi,



Postresql version: 10.5

I need to convert an SQL field from real to numeric, but I’m getting a 
strange behavior.


See the following query in preprod:

select amount, amount::numeric, amount::numeric(16,4), 
amount::varchar::numeric from mytable where id = 32560545;


Result:

17637.75, 17637.8, 17637.8000, 17637.75

As you can see, the conversion to ::numeric is truncating the number to 
just 1 decimal digit.


Also we tried to  change the schema definition of this table, from real 
to numeric, and the value was truncated.


Is the ::varchar::numeric workaround a good option in your opinion? Any 
other ideas to fix this issue?


select 17637.75::real::numeric;
numeric
-
 17637.8

select 17637.75::float::numeric;
numeric
--
 17637.75




Thank you,




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: BUG: Incorrect working with POSIX locale if database in UTF-8 encoding

[Jehan-Guillaume (ioguix) de Rorthais]

On Wed, 17 Oct 2018 12:29:52 +0300
Олег Самойлов  wrote:

> There is not problem with systemctl, if system locale setted by localectl is
> the same as database locale. But, as I said, there is problem with pacemaker
> pgsqlms module. And I think this is incorrect behavior. Database may write in
> log messages in database locale, if this messages arises inside connection to
> this database. But common messages, such as messages on start cluster or
> errors on start cluster must written according to locale settings, and if
> locale empty (POSIX) such messages must be in english. And if messages not in
> english they must be visible as is, not as ‘???’. :) It works inside
> connection, why this does not work on start/stop?

It seems like POSIX support ASCII and some additional characters. So maybe the
character encoding used in the database is just incompatible with POSIX, ie.
POSIX does not support wide chars. As you can see, characters from ASCII table
are printed correctly.

Help with list partitioning on expression

[Dinko Papak]

I have created table partitioned by list on expression using timestamp column, 
so for each timestamp value function returns int and each partition table is 
for single value (range of timestamp for which function result is the same). 
This helps me to simplify querying as I do not need to always write date 
ranges. Querying (partition pruning) works nice, however when I attach new 
partition it seems to always scan whole table, although I do have necessary 
check constraint on partitioned table. I have tried to make timestamp column 
both null and not null. Also, it takes longer to attach partition then to add 
constraint itself although per my understanding those 2 operations should do 
the same scan.

Thank you,
Dinko

Sent from Mail for Windows 10

Re: Pgbouncer discard all

[Fabio Pardi]

@Nicola, while I'm glad you got further help.. I was asking myself it you maybe 
missed my answer to your question? (mail from 10/10/2018 at 15:31)

https://www.postgresql.org/message-id/a22f8385-2a49-30a7-b1d2-fc743c2f3245%40portavita.eu

regards,

fabio pardi



On 16/10/2018 14:12, Nicola Contu wrote:
> Hello,
> is this normal? can anyone help?
>
> Thanks a lot for your help in advance.
>
> Nicola
>
> Il giorno mer 10 ott 2018 alle ore 17:03 Nicola Contu  > ha scritto:
>
> Hello,
> we are running pgbouncer 1.9.1 connected to postgres 10.5
>
> Sometimes we are seeing a lot of waiting connections with this query :
>
> DISCARD ALL
>
> This is our pgbouncer config :
>
> [databases]
> dev = host=10.10.10.1 port=5432 dbname=dev auth_user=pgbouncer 
> pool_size=120
>
> [pgbouncer]
> listen_port = 6543
> listen_addr = *
> auth_type = md5
> auth_file = /etc/pgbouncer/users.txt
> auth_query = select uname,phash from user_lookup($1)
> logfile = /var/log/pgbouncer.log
> pidfile = /home/postgres/pgbouncer.pid
> admin_users = admin
> user = postgres
> max_db_connections = 220
> log_connections = 0
> log_disconnections = 0
>
>
> Do you think this can depend on the server_idle_timeout default config 
> value?
>
> Thanks a lot,
> Nicola
>

Re: convert real to numeric.

[Tom Lane]

Alessandro Aste  writes:
> I need to convert an SQL field from real to numeric, but I’m getting a
> strange behavior.
> select amount, amount::numeric, amount::numeric(16,4),
> amount::varchar::numeric from mytable where id = 32560545;
> Result:
> 17637.75, 17637.8, 17637.8000, 17637.75

You realize of course that "real" (a/k/a float4) can only be trusted
to six decimal digits in the first place.  When I try this, I get

regression=# select '17637.75'::real, '17637.75'::real::numeric;
 float4  | numeric 
-+-
 17637.8 | 17637.8
(1 row)

because the float4 output function rounds it off at the last trustworthy
digit.  Your results suggest that you must be running with
extra_float_digits set to 1, which affects the behavior of the float4
output function ... but not that of float4->numeric conversion.

You could ju-jitsu the system into duplicating that behavior by casting
to text (which invokes float4out) and then to numeric:

regression=# set extra_float_digits to 1;
SET
regression=# select '17637.75'::real, '17637.75'::real::numeric;
  float4  | numeric 
--+-
 17637.75 | 17637.8
(1 row)

regression=# select '17637.75'::real, '17637.75'::real::text::numeric;
  float4  | numeric  
--+--
 17637.75 | 17637.75
(1 row)

I think, however, that you ought to spend some time contemplating
the fact that that extra digit is partially garbage.  I'm not
really convinced that doing it like this rather than doing the
standard conversion is a good idea.  You can't manufacture precision
where there is none --- so it might be better to do the standard
conversion and then go back and fix any values you can demonstrate
are wrong.

regards, tom lane

Re: BUG: Incorrect working with POSIX locale if database in UTF-8 encoding

[Tom Lane]

=?utf-8?B?0J7Qu9C10LMg0KHQsNC80L7QudC70L7Qsg==?=  writes:
> [ postmaster's localized messages are printed as garbage if LANG is C or 
> unset ]

I'm not quite convinced that this is a bug.  The reason it's misbehaving
is that in the postmaster process (and, probably, non-backend children)
LC_MESSAGES gets set to whatever you said in postgresql.conf, but LC_CTYPE
is never changed away from what it was in the postmaster's environment.
So if the prevailing environment setting is C/POSIX, gettext() throws up
its hands and substitutes "?" for non-ASCII characters, because it has
no idea which encoding to render them in.

This is sort of intentional, in that the environment LC_CTYPE ought to
reflect the "console encoding" that you're operating in; if you run your
terminal in say KOI8R, then you set LC_CTYPE=ru_RU.koi8r and messages
should get printed in the encoding the terminal is expecting.

We could maybe make a case for forcing gettext to use the encoding
implied by LC_MESSAGES if LC_CTYPE is C/POSIX, but I'm not really
convinced that there's anything principled about that.

On the other hand, the current behavior in this situation surely
isn't useful to anybody.  Arguably, gettext() is being pretty
unhelpful here, but I doubt we could get them to change.

Peter, any thoughts?

regards, tom lane

query replication status when WAL-E is used

[Valery Sizov]

Hi all,

I need to have a way to query a streaming replication status from the
client (Web app), on a secondary node.  It can be easily done now by
performing a query "select pid, status from pg_stat_wal_receiver". The
problem is that sometimes we need to use WAL-E replication and in this
case, it's impossible to get the status using SQL command. As WAL-E
essentially uses archive_command/restore_command, I would like to be able
to see the content of the restore_command in recovery.conf file using SQL
command. Keep in mind that I CAN see the archive_command value by using
whether "SELECT ALL" or "SELECT * FROM pg_settings"What do you think?

Re: convert real to numeric.

[Peter J. Holzer]

On 2018-10-18 10:15:40 -0400, Tom Lane wrote:
> Alessandro Aste  writes:
> > I need to convert an SQL field from real to numeric, but I’m getting a
> > strange behavior.
> > select amount, amount::numeric, amount::numeric(16,4),
> > amount::varchar::numeric from mytable where id = 32560545;
> > Result:
> > 17637.75, 17637.8, 17637.8000, 17637.75
> 
> You realize of course that "real" (a/k/a float4) can only be trusted
> to six decimal digits in the first place.  When I try this, I get

24 bits, actually. Using decimal digits when talking about binary
numbers is misleading.

[...]
> You could ju-jitsu the system into duplicating that behavior by casting
> to text (which invokes float4out) and then to numeric:
> 
> regression=# set extra_float_digits to 1;
> SET
> regression=# select '17637.75'::real, '17637.75'::real::numeric;
>   float4  | numeric 
> --+-
>  17637.75 | 17637.8
> (1 row)
> 
> regression=# select '17637.75'::real, '17637.75'::real::text::numeric;
>   float4  | numeric  
> --+--
>  17637.75 | 17637.75
> (1 row)

I suggest casting first to float8 and then to numeric. The conversion
from float4 to float8 is exact, and any rounding error introduced by the
float8->numeric conversion is certainly much smaller than the
uncertainty of the original float4 value.


> I think, however, that you ought to spend some time contemplating
> the fact that that extra digit is partially garbage.

If we assume that 17637.75 was the result of rounding a more precise
value to a float4, then the real value was somewhere between
17637.7490234375 and 17637.7509765625. Rounding to 17637.8 introduces an
error almost 50 times larger.


> I'm not really convinced that doing it like this rather than doing the
> standard conversion is a good idea.  You can't manufacture precision
> where there is none

It may be that the real value of that number is only known to +/- 0.1.
Or maybe only to +/- 100. But postgresql can't know that, and
gratuitously adding additional rounding errors doesn't help.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

PGDG status and policy

[Steve Crawford]

While looking to install version 11 I discovered that the PGDG Yum
repository page at https://www.postgresql.org/download/linux/redhat/ includes
repository RPMs for RHEL7  (x86_64 and ppc64Ie), and RHEL6 (x86_64 and
i386).

However there are no RPMs for version 6 of any derivatives (CentOS,
Scientific, Oracle) and the RPMs for all the RHEL derivatives are for
ppc64Ie only.

To what extent are the missing repos simply due to packagers being busy and
which are by policy? I looked for a packaging policy and failed to find
one. Does such a thing exist?

Cheers,
Steve

Re: PGDG status and policy

[Adrian Klaver]

On 10/18/18 3:02 PM, Steve Crawford wrote:
While looking to install version 11 I discovered that the PGDG Yum 
repository page at 
https://www.postgresql.org/download/linux/redhat/ includes repository 
RPMs for RHEL7  (x86_64 and ppc64Ie), and RHEL6 (x86_64 and i386).


However there are no RPMs for version 6 of any derivatives (CentOS, 
Scientific, Oracle) and the RPMs for all the RHEL derivatives are for 
ppc64Ie only.


My guess is the code supplying the dropdowns has not been refreshed. 
Looks like the packages do exist:


https://yum.postgresql.org/repopackages.php



To what extent are the missing repos simply due to packagers being busy 
and which are by policy? I looked for a packaging policy and failed to 
find one. Does such a thing exist?


Cheers,
Steve




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: PGDG status and policy

[Steve Crawford]

No, the *link* exists. The package doesn't (404 errors on, for example,
Centos 7 x86_64 and several others).

Cheers,
Steve

On Thu, Oct 18, 2018 at 3:22 PM Adrian Klaver 
wrote:

> On 10/18/18 3:02 PM, Steve Crawford wrote:
> > While looking to install version 11 I discovered that the PGDG Yum
> > repository page at
> > https://www.postgresql.org/download/linux/redhat/ includes repository
> > RPMs for RHEL7  (x86_64 and ppc64Ie), and RHEL6 (x86_64 and i386).
> >
> > However there are no RPMs for version 6 of any derivatives (CentOS,
> > Scientific, Oracle) and the RPMs for all the RHEL derivatives are for
> > ppc64Ie only.
>
> My guess is the code supplying the dropdowns has not been refreshed.
> Looks like the packages do exist:
>
> https://yum.postgresql.org/repopackages.php
>
> >
> > To what extent are the missing repos simply due to packagers being busy
> > and which are by policy? I looked for a packaging policy and failed to
> > find one. Does such a thing exist?
> >
> > Cheers,
> > Steve
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: convert real to numeric.

[Tom Lane]

"Peter J. Holzer"  writes:
> On 2018-10-18 10:15:40 -0400, Tom Lane wrote:
>> You could ju-jitsu the system into duplicating that behavior by casting
>> to text (which invokes float4out) and then to numeric:

> I suggest casting first to float8 and then to numeric. The conversion
> from float4 to float8 is exact, and any rounding error introduced by the
> float8->numeric conversion is certainly much smaller than the
> uncertainty of the original float4 value.

I do not think that the OP will care for the results of that.  The problem
is that now the output function will think that the result is worth
printing to 16 digits, and the last ten or so of those will be garbage.
As an example, even though the cited value happens to work nicely:

regression=# select '17637.75'::float4::float8;
  float8  
--
 17637.75
(1 row)

nearby ones don't:

regression=# select '17637.74'::float4::float8;
 float8  
-
 17637.740234375
(1 row)

Yeah, in some sense that's a valid representation of the stored float4,
but it likely has little to do with the originally presented value.

regards, tom lane