Re: Resync second slave to new master

[Michael Paquier]

On Tue, Mar 06, 2018 at 06:00:40AM +, Dylan Luong wrote:
> So everytime after promoting Slave to become master (either manually
> or automatic), just stop Slave2 and run pg_rewind on slave2 against
> the new maser (old slave1). And when old master server is available
> again, use pg_rewind on that serve as well against new master to
> return to original configuration.

Yes.  That's exactly the idea.  Running pg_rewind on the old master will
be necessary anyway because you need to stop it cleanly once, which will
cause it to generate WAL records at least for the shutdown checkpoint,
while doing it on slave 2 may be optional, still safer to do.
--
Michael


signature.asc
Description: PGP signature

Re: Best options for new PG instance

[Benjamin Scherrey]

First - NEVER USE NFS TO STORE DATA YOU DON'T WANT TO LOSE. That said, what
you want to host on depends a lot on whether your system is typically CPU
bound or I/O bound. A VM for the computational side is generally quite
fine. If you're seriously CPU bound then you're likely to want to cluster
the thing and/or use PG10 if you can take advantage of parallel requests.
Once you get I/O bound things get trickier. AWS has horrible I/O
characteristics compared to any "bare metal" solution out there for
example. Yes, you can buy I/Oops but now you have incredibly expensive slow
I/O characteristics. If you're I/O bound your best solution is to host
elsewhere if possible. We have clients who cannot and they're paying a lot
more as a result sadly.

A great way to host PG is inside docker containers and there's some
excellent kubernetes solutions coming around. It is best if you can mount
your data on a host file system rather than a data volume container. The
reasons for that may be less strong than before (that was one area where
early Docker had defects) but we still see better I/O performance when
pushed. That said, I am aware of people happy with their deployments using
volume containers although I don't know their I/O profiles so much. Anyway
- Docker can be run within VMs or directly on bare metal quite easily and
is a great way to compare the impact of the two.

Oh - and lots of memory is always good no matter what as others have said.

  Good luck,

  -- Ben

On Mon, Mar 5, 2018 at 11:53 PM, David Gauthier 
wrote:

> Hi:
>
> I'm going to be requesting a PG DB instance (v9.6.7) from an IT dept in a
> large corp setting.  I was wondering if anyone could comment on the
> pros/cons of getting this put on a virtual machine vs hard metal ?  Locally
> mounted disk vs nfs ?
>
> Thanks !
>
>
>
>
>
>

RE: Resync second slave to new master

[Dylan Luong]

Thanks Michael,
So everytime after promoting Slave to become master (either manually or 
automatic), just stop Slave2 and run pg_rewind on slave2 against the new maser 
(old slave1). And when old master server is available again, use pg_rewind on 
that serve as well against new master to return to original configuration.

-Original Message-
From: Michael Paquier [mailto:mich...@paquier.xyz] 
Sent: Tuesday, 6 March 2018 3:54 PM
To: Dylan Luong 
Cc: pgsql-generallists.postgresql.org 
Subject: Re: Resync second slave to new master

On Tue, Mar 06, 2018 at 04:45:10AM +, Dylan Luong wrote:
> After a failover (promote) to the Slave1,  is it easily resync the
> Slave2 to the new master (old slave1)? Do we need to do  full rebuild 
> of the Slave2 from new master everytime we failover to Slave1 from 
> Master? Can we use pg_rewind on Slave2 to resyn it with new master 
> (old slave1)?

After promoting slave 1, it could be possible that some records have slipped to 
slave 2 from the primary.  In this case, a rewind would be recommended.  You 
should be careful that your slave 2 has not received WAL to a position newer 
than where WAL has forked because of the promotion.  If that happened, then a 
rewind would be necessary before replugging slave 2 to the newly-promoted 
server.  Be very careful with your failover flow here.  pg_rewind also would 
not run if it finds that the target server does not need a rewind, so you could 
stop the slave 2, and run pg_rewind unconditionally to keep things simple.
--
Michael

Re: Resync second slave to new master

[Michael Paquier]

On Tue, Mar 06, 2018 at 04:45:10AM +, Dylan Luong wrote:
> After a failover (promote) to the Slave1,  is it easily resync the
> Slave2 to the new master (old slave1)? Do we need to do  full rebuild
> of the Slave2 from new master everytime we failover to Slave1 from
> Master? Can we use pg_rewind on Slave2 to resyn it with new master
> (old slave1)? 

After promoting slave 1, it could be possible that some records have
slipped to slave 2 from the primary.  In this case, a rewind would be
recommended.  You should be careful that your slave 2 has not received
WAL to a position newer than where WAL has forked because of the
promotion.  If that happened, then a rewind would be necessary before
replugging slave 2 to the newly-promoted server.  Be very careful with
your failover flow here.  pg_rewind also would not run if it finds that
the target server does not need a rewind, so you could stop the slave 2,
and run pg_rewind unconditionally to keep things simple.
--
Michael


signature.asc
Description: PGP signature

Re: org.postgresql.util.PSQLException: Error could not open file "base/": No such file or directory

[Adrian Klaver]

On 03/05/2018 05:54 PM, Raghavendra Rao J S V wrote:

Hi,

Few of the tables and indexes are bloated though auto vacuum has enables.

Two reclaim the space vacuum, I have ran vacuum full on the larger 
tables and also performed reindex on the indexes which are in larger 
size. Now the size of the database is in control.


After perfoming the vacuum full and reindexing on larger tables, I am 
facing below error.


*org.postgresql.util.PSQLException: Error could not open file "base/": 
No such file or directory*


Assuming the database is up and running then base/ is actually there:

https://www.postgresql.org/docs/10/static/storage-file-layout.html


When I know a file or directory exists and I see an error like:

'Error could not open file "base/": No such file or directory '

then I generally check permissions on said file/directory. In particular 
does the app throwing the error having the permissions to 'see' the 
file/directory?




Please guide me how to resolve the above error and let me know does this 
has any relation with vacumm full or reindexing operation which are 
performed by me.


--
Regards,
Raghavendra Rao J S V




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

Resync second slave to new master

[Dylan Luong]

Hi

We are currently on PostgreSQL 9.6 and our HA is configured as  master/slave 
with wal streaming replication.
We would like to setup a second slave which replicates from the same master.

Eg:
Current setup:

Slave <-- Master
Slave is setup for automatic failover and we use pg_rewind after failover to 
rewind the old master as new slave.

New setup:

Slave1   <-- Master --> Slave2

Slave 2 (new) is for reporting and nightly pg_dumps, as we have some very big 
databases.
Slave 1 is for HA and is setup automatic failover.

After a failover (promote) to the Slave1,  is it easily resync the Slave2 to 
the new master (old slave1)? Do we need to do  full rebuild of the Slave2 from 
new master everytime we failover to Slave1 from Master? Can we use pg_rewind on 
Slave2 to resyn it with new master (old slave1)?

Thanks
Dylan

RE: org.postgresql.util.PSQLException: Error could not open file "base/": No such file or directory

[Alvaro Aguayo Garcia-Rada]

What do you have on your postgresql logs, on the server?

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 
954183248
Website: www.ocs.pe

Sent from my Sony Xperia™ smartphone

 Raghavendra Rao J S V wrote 

Hi,

Few of the tables and indexes are bloated though auto vacuum has enables.

Two reclaim the space vacuum, I have ran vacuum full on the larger tables
and also performed reindex on the indexes which are in larger size. Now the
size of the database is in control.

After perfoming the vacuum full and reindexing on larger tables, I am
facing below error.

*org.postgresql.util.PSQLException: Error could not open file "base/": No
such file or directory*

Please guide me how to resolve the above error and let me know does this
has any relation with vacumm full or reindexing operation which are
performed by me.

-- 
Regards,
Raghavendra Rao J S V

org.postgresql.util.PSQLException: Error could not open file "base/": No such file or directory

[Raghavendra Rao J S V]

Hi,

Few of the tables and indexes are bloated though auto vacuum has enables.

Two reclaim the space vacuum, I have ran vacuum full on the larger tables
and also performed reindex on the indexes which are in larger size. Now the
size of the database is in control.

After perfoming the vacuum full and reindexing on larger tables, I am
facing below error.

*org.postgresql.util.PSQLException: Error could not open file "base/": No
such file or directory*

Please guide me how to resolve the above error and let me know does this
has any relation with vacumm full or reindexing operation which are
performed by me.

-- 
Regards,
Raghavendra Rao J S V

Re: Best options for new PG instance

[Michael Paquier]

On Mon, Mar 05, 2018 at 09:51:53AM -0800, Steve Atkins wrote:
> I've been running postgresql instances on ESXi VMs for years with no
> issues. I've not benchmarked them, but performance has been good
> enough despite their running on fairly wimpy hardware. Performance
> relative to bare metal is probably going to be dominated by disk IO,
> and depending on how you're hosting VMs that can be anywhere between
> pretty good and terrible - in a large corporation I'd expect it to be
> pretty good. Just don't skimp on RAM - having your hot data in the
> filesystem cache is always good and can make high latency storage
> tolerable.

One thing to be very careful about is the backup strategy of your
PostgreSQL instances.  I would recommend primarily using PostgreSQL
in-core tools like pg_basebackup to do the work and make sure that
things are consistent.  Users tend to rely a lot on VM snapshots,
particularly quiesced snapshots without memory footprint, but those
could be the cause of data corruption if not using appropriate
pre-freeze and post-thaw scripts in charge of freezing the partitions
while the snapshot is taken (use different partitions for the data
folder, pg_wal and logs as well!), so this would require extra work from
your side.  I am talking about VMware technology here, still you can
find a lot of so-told-useful VM-level backup technologies.  Be careful
with those as well when it comes to database backups.  You can think
that your backups taken are safe, until you see a corruption which has
been hidden for weeks.
--
Michael


signature.asc
Description: PGP signature

Re: JDBC4 and setting statement_timeout: responds "is not yet implemented"

[rob stone]

Hi Gunnar,

On Mon, 2018-03-05 at 12:27 +0100, Gunnar Halvorsen wrote:
> Dear Sirs,
> As quite new in this discussiongroup I have red a lot about
> statement_timeout, but I can't find any answer on the following
> problem:
> 
>  My JavaEE-code responds the fpllowing Error message:
> 
>  Method
> org.postgresql.jdbc4.Jdbc4PreparedStatement.setQueryTimeout(int) is
> not yet implemented
> 
> Javacode:
> PreparedStatement AA = null;
> ..
> ..
> AA.setQueryTimeout( 5000 );  // This sentence generates the fault-
> message
> 
> 
> PostgreSQL version: 9.1.24
> ( Only READ-access )
> 
> JDBC4 version:   postgresql-9.0-801.jdbc4.jar
> 
> 
> My application reads contiously a SELECT-sentence every 10 seconds,
> and needs to be aborted if it not returns in during of 5 seconds.
> 
> Is the functiion setQueryTimeout(INT) not yet implemented in JDBC4,
> or is it the PostgreSQK-server which is not yet implemented?
> 
> How shall I implement statement_timeout in my JavaEE code, when I
> only has Read-access to PostgreSQL, to avoid this ErrorMessage?
> 
> Regards
> -Gunnar
> 
>  
> 
> 
> 


There was a fix to this in Version 9.4-1205 of the driver.
Also, your version of PostgreSql is ancient.

I think an upgrade would be worthwhile.

HTH,
Robert

Re: Best options for new PG instance

[Tim Cross]

David Gauthier  writes:

> Hi:
>
> I'm going to be requesting a PG DB instance (v9.6.7) from an IT dept in a
> large corp setting.  I was wondering if anyone could comment on the
> pros/cons of getting this put on a virtual machine vs hard metal ?  Locally
> mounted disk vs nfs ?
>

This is a hard question to answer as there are too many
variables. However, I would say that my experience has been that most
large organisations are pretty much using VMs for everything, so asking
for something on a 'bare metal' basis is likely to result in your
request needing special attention and justification. On the other hand,
if you make a standard request, it will likely be fulfilled more
rapidly. Sys admins are likely to be resistant to a bare metal setup if
their infrastructure is based around VMs due tot he additional work and
maintenance overheads.

All our databases are running on VMs. Some of them are storing fairly
large amounts of data (i.e. one application stores large amounts of
weather data - adding 650 million records a day with a consolidation
after every 100 days. Currently, the DB is using about 6Tb, so not huge,
but not insignificant). Disk storage is via SAN.

Getting the right performance will require tweaking of memory, cpus
etc. The good news is that adding additional memory and CPUs is
relatively trivial.

For our situation, VMs have been fine and there has been some advantages
with SAN storage infrastructure, such as fast snapshots for backups
etc. In general, I usually find it best to work with the system admins
and follow their recommendations. Provide them with details of your
performance requirements and where you feel resource demands may peak
and let them propose what they feel would be best suited given whatever
infrastructure they have. 

Tim

--
Tim Cross

Re: PQConsumeinput stuck on recv

[Andres Freund]

Hi,

On 2018-03-05 17:57:51 -0300, Andre Oliveira Freitas wrote:
> Ok, on one hand glad to hear that is an artifact, on the other hand
> that means the issue is still out there...
> 
> I'm not a maintainer of Freeswitch, I am an user of it. However I am
> trying to supply the maintainers with any relevant information
> regarding this issue.
> 
> As you mentioned, I see the socket being modified and passed around in
> the source code. Do you think the implementation is OK? I do not know
> if that's standard practice when consuming libpq.

It's ok to pass the socket around, it'd not be ok to change whether the
socket blocks or not.  I don't have the time to review the freeswitch
code to see whether it does so.

- Andres

Re: Q: text palloc() size vs. SET_VARSIZE()

[Albrecht Dreß]

Am 04.03.18 20:52 schrieb(en) Tom Lane:

>  From the docs, for me it is not clear whether the value assigned using 
SET_VARSIZE() must be the *exact* size of the newly allocated return value, or just 
the length of the text plus the header size.  IOW would the code above create a 
memory leak if out_len < VARSIZE_ANY_EXHDR(t)?

No memory leak.  Your returned value would have some wasted memory at the end 
of its palloc chunk, but function result values don't normally live long enough 
that that's worth worrying about.


Thanks a lot for the clarification!  I.e. palloc()/pfree() basically behave 
like malloc()/free() in this regard…

In my application, the wasted space will actually be just a few bytes, if any.  
So this is definitely the best solution.


You could repalloc the result down to minimum size if you felt like it, but I 
think it'd largely be a waste of cycles.


Avoiding exactly this overhead is my intention!

Thanks again,
Albrecht.

pgpeH5Sn7t7J1.pgp
Description: PGP signature

Re: Best options for new PG instance

[Steve Atkins]

> On Mar 5, 2018, at 8:53 AM, David Gauthier  wrote:
> 
> Hi:  
> 
> I'm going to be requesting a PG DB instance (v9.6.7) from an IT dept in a 
> large corp setting.  I was wondering if anyone could comment on the pros/cons 
> of getting this put on a virtual machine vs hard metal ?  Locally mounted 
> disk vs nfs ?

I've been running postgresql instances on ESXi VMs for years with no issues. 
I've not benchmarked them, but performance has been good enough despite their 
running on fairly wimpy hardware. Performance relative to bare metal is 
probably going to be dominated by disk IO, and depending on how you're hosting 
VMs that can be anywhere between pretty good and terrible - in a large 
corporation I'd expect it to be pretty good. Just don't skimp on RAM - having 
your hot data in the filesystem cache is always good and can make high latency 
storage tolerable.

If performance isn't critical then a VM is great. If it is, you'll want to plan 
and maybe benchmark a bit to decide whether bare metal is going to be 
significantly better for what you're doing.

I wouldn't let NFS anywhere near it. I'd ideally want something that looks to 
the VM like a locally mounted disk, whether that be really local or served from 
a SAN or iSCSI or ...

https://www.slideshare.net/jkshah/best-practices-of-running-postgresql-in-virtual-environments
 has some hints on VM-specific things to consider.

Cheers,
  Steve

Best options for new PG instance

[David Gauthier]

Hi:

I'm going to be requesting a PG DB instance (v9.6.7) from an IT dept in a
large corp setting.  I was wondering if anyone could comment on the
pros/cons of getting this put on a virtual machine vs hard metal ?  Locally
mounted disk vs nfs ?

Thanks !

Re: What is wrong with my pgadmin?

[Łukasz Jarych]

I am using:



I am using:




thank you for help,
Jacek

2018-03-05 15:22 GMT+01:00 Adrian Klaver :

> On 03/05/2018 06:05 AM, Łukasz Jarych wrote:
>
>> Hi Guys,
>>
>> what is wrong with my pgAdmin?
>>
>> I am creating wrong query:
>>
>> select 1/0 but still this is executed!
>>
>
> Well it will be executed, but it should throw an error. Can you confirm
> that running select 1/0; in psql does indeed throw an error:
>
> test=# select 1/0;
> ERROR:  division by zero
>
> Also:
>
> 1) What version of pgAdmin4 are you running?
>
> 2) What version of Postgres is it running against?
>
>
>>
>>
>> why?
>>
>> Best wishes,
>> Jacek
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: What is wrong with my pgadmin?

[Łukasz Jarych]

Hi geoff,

i am expecting that there will be error thrown by pg_admin.

Jacek

2018-03-05 15:09 GMT+01:00 geoff hoffman :

> What result were you expecting? A divide by zero error?
>
>
> > On Mar 5, 2018, at 07:05, Łukasz Jarych  wrote:
> >
> > Hi Guys,
> >
> > what is wrong with my pgAdmin?
> >
> > I am creating wrong query:
> >
> > select 1/0 but still this is executed!
> >
> > 
> >
> > why?
> >
> > Best wishes,
> > Jacek
> >
> >
>

Re: What is wrong with my pgadmin?

[Łukasz Jarych]

Hi,

yes, using psql there is an error.

Jacek

2018-03-05 15:22 GMT+01:00 Adrian Klaver :

> On 03/05/2018 06:05 AM, Łukasz Jarych wrote:
>
>> Hi Guys,
>>
>> what is wrong with my pgAdmin?
>>
>> I am creating wrong query:
>>
>> select 1/0 but still this is executed!
>>
>
> Well it will be executed, but it should throw an error. Can you confirm
> that running select 1/0; in psql does indeed throw an error:
>
> test=# select 1/0;
> ERROR:  division by zero
>
> Also:
>
> 1) What version of pgAdmin4 are you running?
>
> 2) What version of Postgres is it running against?
>
>
>>
>>
>> why?
>>
>> Best wishes,
>> Jacek
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: psql variables in the DO command

[Pavel Stehule]

2018-03-05 16:19 GMT+01:00 Pavel Luzanov :

> On 05.03.2018 18:01, Pavel Stehule wrote:
>
>
> It is most correct when you thinking about it.
>
> 1. :xx is out of SQL syntax, so can by safely used. There is not risk of
> unwanted usage.
>
> But there is absence of wanted usage too.
>

How much strong and often? The parser of SQL in psql is not nice - and I
understand so nobody would to complicate syntax. Current design is SAFE and
good enough. The problem is not is a evaluation, but in DO implementation.

>
> 2. but string literal can contain :xxx symbols and not necessary it means
> so it should be usage of psql variable - so additional syntax for disabling
> evaluation should be necessary
>
> Yes and Oracle sqlplus (I khow than you know this tool) has special
> command to control this: set define ...
>
>
> Unfortunately DO command is half baked - and doesn't support parameters. I
> am working on schema variables and I hope it will be a solution of this
> issue:
>
> CREATE VARIABLE var as integer;
>
> LET var = :psqlintvar;
>
> DO $$
> BEGIN
>   RAISE NOTICE '%', var;
> END;
> $$;
>
>
> It will be great. I already commented it in your blog.
>

I am slowly working on prototype. The work is simple, when variables are
just scalars. But it is much harder, when we allow composite variables.
When prototype will be done, I invite any cooperation - there are lot of
question - and one very hard - where and how the variable should be stored
(values) - now I have workaround, but it is pretty ugly code.

CREATE VARIABLE x INT;
LET x = 10;
BEGIN;
DROP VARIABLE x;
ROLLBACK;
SELECT x; -- should be 10 .. for this situation, the PostgreSQL internal
caches are not prepared

Regards

Pavel


>
> -
> Pavel Luzanov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>

回复： pg_upgrade fails to restore function

[Roger Sunny]

HI Tom

Thank you very much for your help. This issue has been solved.

Regards,
Henry



-- 原始邮件 --
发件人: "Tom Lane";
发送时间: 2018年3月3日(星期六) 晚上11:30
收件人: "Roger Sunny"<947035...@qq.com>;
抄送: "pgsql-general"; 
主题: Re: pg_upgrade fails to restore function



"=?gb18030?B?Um9nZXIgU3Vubnk=?=" <947035...@qq.com> writes:
> During the pg_upgrade from PostgreSQL 9.4 to PostgreSQL 10.0, the 
> error¡±[archiver (db)] Error while PROCESSING TOC:¡± happened, The upgrade 
> failed. OS is RHEL 6.5 , The error is as below:
> pg_restore: connecting to database for restorepg_restore: 
> creating pg_largeobject "pg_largeobject"pg_restore: creating 
> pg_largeobject_metadata "pg_largeobject_metadata"pg_restore: creating SCHEMA 
> "bo_user0300"pg_restore: creating SCHEMA "d0007359_bex2018"pg_restore: 
> creating SCHEMA "d0007359_dan2014"   omit -- omit -- omit 
> -- 
> pg_restore: creating PROCEDURE "zchuo_ult18.kp_delkakeihyo(character)"
 
> pg_restore: creating FOREIGN DATA WRAPPER "dblink_fdw"
 
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>  pg_restore: [archiver (db)] Error from TOC entry 470023; 2328 777028 FOREIGN 
> DATA WRAPPER dblink_fdw postgrespg_restore: [archiver (db)] could not execute 
> query: ERROR:  function dblink_fdw_validator(text[], oid) does not exist 
>Command was: CREATE FOREIGN DATA WRAPPER "dblink_fdw" VALIDATOR 
> "dblink_fdw_validator";


Is that really the first error, or did you "omit" some?  What it
looks like is that dblink_fdw_validator had previously failed to
be created, probably because you don't have dblink.so installed
in the destination database.  Or maybe it's not the right version
of dblink.so.

regards, tom lane

psqlODBC and PostgreSQL

[muthamma.appaiah]

Hi team,

Could you tell me the components of psqlODBC, what other applications come 
along with this download package?

Could you let me know if PostgreSQL (Client version 10.x) is a stand alone 
product or an embedded product? If it is packaged separately by package 
maintenance?

JDBC4 and setting statement_timeout: responds "is not yet implemented"

[Gunnar Halvorsen]

Dear Sirs,
As quite new in this discussiongroup I have red a lot about
statement_timeout, but I can't find any answer on the following problem:

 My JavaEE-code responds the fpllowing Error message:

 *Method org.postgresql.jdbc4.Jdbc4PreparedStatement.setQueryTimeout(int)
is not yet implemented*

Javacode:
PreparedStatement AA = null;
*..*
*..*
AA.setQueryTimeout( 5000 );  // This sentence generates the fault-message


PostgreSQL version: 9.1.24
( Only READ-access )

JDBC4 version:   postgresql-9.0-801.jdbc4.jar


My application reads contiously a SELECT-sentence every 10 seconds, and
needs to be aborted if it not returns in during of 5 seconds.

Is the functiion *setQueryTimeout(INT) *not yet implemented in JDBC4, or is
it the PostgreSQK-server which is not yet implemented?

How shall I implement statement_timeout in my JavaEE code, when I only has
Read-access to PostgreSQL, to avoid this ErrorMessage?

Regards
-Gunnar

Re: psql variables in the DO command

[Pavel Luzanov]

On 05.03.2018 18:01, Pavel Stehule wrote:


It is most correct when you thinking about it.

1. :xx is out of SQL syntax, so can by safely used. There is not risk 
of unwanted usage.

But there is absence of wanted usage too.


2. but string literal can contain :xxx symbols and not necessary it 
means so it should be usage of psql variable - so additional syntax 
for disabling evaluation should be necessary
Yes and Oracle sqlplus (I khow than you know this tool) has special 
command to control this: set define ...




Unfortunately DO command is half baked - and doesn't support 
parameters. I am working on schema variables and I hope it will be a 
solution of this issue:


CREATE VARIABLE var as integer;

LET var = :psqlintvar;

DO $$
BEGIN
  RAISE NOTICE '%', var;
END;
$$;



It will be great. I already commented it in your blog.

-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: What is wrong with my pgadmin?

[Murtuza Zabuawala]

Hello,

This is a bug with pgAdmin4 (Bug: RM#3090
).

Work around for this problem is to set
lc_messages = 'en_US.UTF-8'
in "postgresql.conf" file.


--
Regards,
Murtuza Zabuawala
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


On Mon, Mar 5, 2018 at 7:57 PM, Martin Moore 
wrote:

> Using pgAdmin 4 2.0 on pg 9.6 and 10 I get div/0 error.
>
>
>
> On 05/03/2018, 14:22, "Adrian Klaver"  wrote:
>
> On 03/05/2018 06:05 AM, Łukasz Jarych wrote:
> > Hi Guys,
> >
> > what is wrong with my pgAdmin?
> >
> > I am creating wrong query:
> >
> > select 1/0 but still this is executed!
>
> Well it will be executed, but it should throw an error. Can you confirm
> that running select 1/0; in psql does indeed throw an error:
>
> test=# select 1/0;
> ERROR:  division by zero
>
> Also:
>
> 1) What version of pgAdmin4 are you running?
>
> 2) What version of Postgres is it running against?
>
> >
> >
> >
> > why?
> >
> > Best wishes,
> > Jacek
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>
>
>
>

Re: psql variables in the DO command

[Pavel Stehule]

2018-03-05 15:02 GMT+01:00 Pavel Luzanov :

> On 05.03.2018 16:56, Pavel Stehule wrote:
>
>
>> I can't use psql variable in the DO command. Is it intentional behavior?
>>>
>>
>> yes. psql variables living on client side, and are not accessible from
>> server side . DO command is executed on server side.
>>
>>
>> But SELECT command also executed on a server side ))
>> I thought that the command is sent to the server after variable's
>> replacement.
>>
>
> The psql variables are injected into SQL string before execution (before
> SQL string is sent to server). But this injection is disabled inside
> strings - and body of DO command is passed as string.
>
> Yes, now I understand this. But at first glance this is not an obvious
> behavior.
>

It is most correct when you thinking about it.

1. :xx is out of SQL syntax, so can by safely used. There is not risk of
unwanted usage.

2. but string literal can contain :xxx symbols and not necessary it means
so it should be usage of psql variable - so additional syntax for disabling
evaluation should be necessary

3. I understand to request to use psql variables in DO command. But you
should remember - body of DO command is string. body of any function is
string too. Some unwanted psql variable evaluation in CREATE FUNCTION can
be tragic.

Unfortunately DO command is half baked - and doesn't support parameters. I
am working on schema variables and I hope it will be a solution of this
issue:

CREATE VARIABLE var as integer;

LET var = :psqlintvar;

DO $$
BEGIN
  RAISE NOTICE '%', var;
END;
$$;



> -
> Pavel Luzanov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>

Re: Requiring pass and database psql shell command

[David G. Johnston]

On Mon, Mar 5, 2018 at 5:55 AM, Łukasz Jarych  wrote:

> Hi Guys,
>
> do you have also problem that every time you have to log into your
> database using shell and psql?
>
> I have to write over and over localhost, username, password...how to force
> shell to remember this?
>
>
​Use a service file.​

psql "service=mydb" -c "SELECT 1;"

​https://www.postgresql.org/docs/10/static/libpq-pgservice.html

I also, for scripts, simply define a function at the top of the script
psql_* and invoke that function instead of psql directly.

I use .pgpass for passwords

David J.

Re: What is wrong with my pgadmin?

[Martin Moore]

Using pgAdmin 4 2.0 on pg 9.6 and 10 I get div/0 error.



On 05/03/2018, 14:22, "Adrian Klaver"  wrote:

On 03/05/2018 06:05 AM, Łukasz Jarych wrote:
> Hi Guys,
> 
> what is wrong with my pgAdmin?
> 
> I am creating wrong query:
> 
> select 1/0 but still this is executed!

Well it will be executed, but it should throw an error. Can you confirm 
that running select 1/0; in psql does indeed throw an error:

test=# select 1/0;
ERROR:  division by zero

Also:

1) What version of pgAdmin4 are you running?

2) What version of Postgres is it running against?

> 
> 
> 
> why?
> 
> Best wishes,
> Jacek
> 
> 


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

Re: Requiring pass and database psql shell command

[Ron Johnson]

The password file is also a solution.

https://www.postgresql.org/docs/current/static/libpq-pgpass.html

On 03/05/2018 07:33 AM, James Keener wrote:
Well, it's not a problem, it's the way it's designed and it's a sensible 
design. Check https://www.postgresql.org/docs/9.3/static/libpq-envars.html 
for more info on doing what you want.


On March 5, 2018 7:55:46 AM EST, "Łukasz Jarych"  wrote:

Hi Guys,

do you have also problem that every time you have to log into your
database using shell and psql?

I have to write over and over localhost, username, password...how to
force shell to remember this?

Best,
Jacek




--
Angular momentum makes the world go 'round.

Re: What is wrong with my pgadmin?

[Adrian Klaver]

On 03/05/2018 06:05 AM, Łukasz Jarych wrote:

Hi Guys,

what is wrong with my pgAdmin?

I am creating wrong query:

select 1/0 but still this is executed!


Well it will be executed, but it should throw an error. Can you confirm 
that running select 1/0; in psql does indeed throw an error:


test=# select 1/0;
ERROR:  division by zero

Also:

1) What version of pgAdmin4 are you running?

2) What version of Postgres is it running against?





why?

Best wishes,
Jacek





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

Re: psql variables in the DO command

[Pavel Luzanov]

Another possible, but inconvenient workaround - constructing the right 
string before execution:


postgres=# \set var 'Hello, World!'
postgres=# \set cmd '$$begin raise notice ''%'', ' :'var' '; end;$$;'
postgres=# do :cmd;
NOTICE:  Hello, World!
DO

-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On 05.03.2018 17:02, Pavel Luzanov wrote:

On 05.03.2018 16:56, Pavel Stehule wrote:




I can't use psql variable in the DO command. Is it
intentional behavior?


yes. psql variables living on client side, and are not
accessible from server side . DO command is executed on server side.


But SELECT command also executed on a server side ))
I thought that the command is sent to the server after variable's
replacement.


The psql variables are injected into SQL string before execution 
(before SQL string is sent to server). But this injection is disabled 
inside strings - and body of DO command is passed as string.
Yes, now I understand this. But at first glance this is not an obvious 
behavior.


-
Pavel Luzanov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company

Re: Requiring pass and database psql shell command

[Łukasz Jarych]

thank you!!

Jacek

2018-03-05 14:33 GMT+01:00 James Keener :

> Well, it's not a problem, it's the way it's designed and it's a sensible
> design. Check https://www.postgresql.org/docs/9.3/static/libpq-envars.html
> for more info on doing what you want.
>
>
> On March 5, 2018 7:55:46 AM EST, "Łukasz Jarych" 
> wrote:
>>
>> Hi Guys,
>>
>> do you have also problem that every time you have to log into your
>> database using shell and psql?
>>
>> I have to write over and over localhost, username, password...how to
>> force shell to remember this?
>>
>> Best,
>> Jacek
>>
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
>

Re: psql variables in the DO command

[Pavel Luzanov]

On 05.03.2018 16:56, Pavel Stehule wrote:




I can't use psql variable in the DO command. Is it
intentional behavior?


yes. psql variables living on client side, and are not accessible
from server side . DO command is executed on server side.


But SELECT command also executed on a server side ))
I thought that the command is sent to the server after variable's
replacement.


The psql variables are injected into SQL string before execution 
(before SQL string is sent to server). But this injection is disabled 
inside strings - and body of DO command is passed as string.
Yes, now I understand this. But at first glance this is not an obvious 
behavior.


-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: psql variables in the DO command

[Pavel Stehule]

2018-03-05 14:52 GMT+01:00 Pavel Luzanov :

> On 05.03.2018 16:42, Pavel Stehule wrote:
>
>
> I can't use psql variable in the DO command. Is it intentional behavior?
>>
>
> yes. psql variables living on client side, and are not accessible from
> server side . DO command is executed on server side.
>
>
> But SELECT command also executed on a server side ))
> I thought that the command is sent to the server after variable's
> replacement.
>

The psql variables are injected into SQL string before execution (before
SQL string is sent to server). But this injection is disabled inside
strings - and body of DO command is passed as string.

postgres=# \set xxx ahoj
postgres=# select ':xxx';
+--+
| ?column? |
+--+
| :xxx |
+--+
(1 row)


Regards

Pavel


>
>
> you can copy psql variables to GUC variables by set_config function, and
> then on server side use current_setting function for getting the content.
>
> Yes, I know about workarounds.
>
>
> -
> Pavel Luzanov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>

Re: psql variables in the DO command

[Pavel Luzanov]

On 05.03.2018 16:42, Pavel Stehule wrote:


I can't use psql variable in the DO command. Is it intentional
behavior?


yes. psql variables living on client side, and are not accessible from 
server side . DO command is executed on server side.


But SELECT command also executed on a server side ))
I thought that the command is sent to the server after variable's 
replacement.




you can copy psql variables to GUC variables by set_config function, 
and then on server side use current_setting function for getting the 
content.

Yes, I know about workarounds.


-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: psql variables in the DO command

[Pavel Stehule]

Hi

2018-03-05 14:13 GMT+01:00 Pavel Luzanov :

> Hello,
>
> I can't use psql variable in the DO command. Is it intentional behavior?
>

yes. psql variables living on client side, and are not accessible from
server side . DO command is executed on server side.

you can copy psql variables to GUC variables by set_config function, and
then on server side use current_setting function for getting the content.

Regards

Pavel



>
> postgres=# \set var 'Hello, World!'
>
> postgres=# do $$begin raise notice '%', :'var'; end;$$;
>
> ERROR:  syntax error at or near ":"
>
> LINE 1: do $$begin raise notice '%', :'var'; end;$$;
>
>  ^
>
>
> --
>
> -
> Pavel Luzanov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>
>

Re: Requiring pass and database psql shell command

[James Keener]

Well, it's not a problem, it's the way it's designed and it's a sensible 
design. Check https://www.postgresql.org/docs/9.3/static/libpq-envars.html for 
more info on doing what you want.

On March 5, 2018 7:55:46 AM EST, "Łukasz Jarych"  wrote:
>Hi Guys,
>
>do you have also problem that every time you have to log into your
>database
>using shell and psql?
>
>I have to write over and over localhost, username, password...how to
>force
>shell to remember this?
>
>Best,
>Jacek

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

psql variables in the DO command

[Pavel Luzanov]

Hello,

I can't use psql variable in the DO command. Is it intentional behavior?

postgres=# \set var 'Hello, World!'

postgres=# do $$begin raise notice '%', :'var'; end;$$;

ERROR:  syntax error at or near ":"

LINE 1: do $$begin raise notice '%', :'var'; end;$$;

 ^


--

-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Requiring pass and database psql shell command

[Łukasz Jarych]

Hi Guys,

do you have also problem that every time you have to log into your database
using shell and psql?

I have to write over and over localhost, username, password...how to force
shell to remember this?

Best,
Jacek

PostgreSQL 10.3 and Ubuntu

[Olleg Samoylov]

There is PostgreSQL 10.3 already. I have Ubuntu 17.10 (artful) and there 
is not a pgdg repository for it. There is PostgreSQL 10.3 in 16.04 LTS 
(xenial) pgdg repository, but such packages can't be installed on 17.10 
(artful) Ubuntu. And there is zesty-pgdg (for 17.04 Ubuntu), works fine 
for me (for 17.10 Ubuntu), but there are only old PostgreSQL 
10+189.pgdg17.04+1. Can you rebuid packages for the zesty or create for 
the artful with modern 10.3 PostgreSQL?