Re: psql \r changed behavior in pg10

"David G. Johnston" writes: > On Wednesday, July 22, 2020, Emanuel Araújo wrote: >> \r >> \e >> -> Open temp file with the same last command "select 1;" >> is it right? > Documentation since v10: > Or, if the current query buffer is empty, the most recently executed query > is copied to a tempor

Re: psql \r changed behavior in pg10

On Wednesday, July 22, 2020, David G. Johnston wrote: > On Wednesday, July 22, 2020, Emanuel Araújo wrote: > >> >> \r >> \e >> -> Open temp file with the same last command "select 1;" >> is it right? >> >> > Documentation since v10: > > Or, if the current query buffer is empty, the most recently

Re: psql \r changed behavior in pg10

On Wednesday, July 22, 2020, Emanuel Araújo wrote: > > \r > \e > -> Open temp file with the same last command "select 1;" > is it right? > > Documentation since v10: Or, if the current query buffer is empty, the most recently executed query is copied to a temporary file and edited in the same fa

psql \r changed behavior in pg10

Hi, When used PostgreSQL 9.6.* it was common use in psql \r\e to clear a buffer query and open the empty text editor. Works fine for me and it was very useful in my tasks. Since PostgreSQL 10, this behavior changed and not more clear the buffer query. psql helps show that \r reset (clear) the q

Re: Slow or Cannot Connect to PostgreSQL Instance Service on Windows 10

On 23-07-2020 06:12, Rob Sargent wrote: On Jul 22, 2020, at 4:58 PM, Surya Widyanto > wrote: On 23-07-2020 05:26, Adrian Klaver wrote: On 7/22/20 10:24 AM, Surya Widyanto wrote: Hi, I have PostgreSQL v12.3 (x64) installed on my Laptop running Windows 10 Home Sing

Re: Slow or Cannot Connect to PostgreSQL Instance Service on Windows 10

> On Jul 22, 2020, at 4:58 PM, Surya Widyanto wrote: > > On 23-07-2020 05:26, Adrian Klaver wrote: >> On 7/22/20 10:24 AM, Surya Widyanto wrote: >>> Hi, >>> >>> I have PostgreSQL v12.3 (x64) installed on my Laptop running Windows 10 >>> Home Single Language (64-bit). >>> In two or three ti

Re: Slow or Cannot Connect to PostgreSQL Instance Service on Windows 10

On 23-07-2020 05:26, Adrian Klaver wrote: On 7/22/20 10:24 AM, Surya Widyanto wrote: Hi, I have PostgreSQL v12.3 (x64) installed on my Laptop running Windows 10 Home Single Language (64-bit). In two or three times since I fresh install the PostgreSQL and windows on 17-05-2020, 06:04:56 (GMT).

Re: How to create function returning numeric from string containing percent character

On Wed, Jul 22, 2020 at 3:50 AM Andrus wrote: > val function should return numeric value from string up to first non-digit > character, considering first decimal point also: > > val('1,2TEST') should return 1.2 > val('1,2,3') should return 1.2 > val('-1,2,3') should return -1.2 > >

Re: CASCADE/fkey order

On Wed, Jul 22, 2020 at 9:03 AM Samuel Nelson wrote: > seems to fix it to work as we were expecting. Is that particularly > costly? Should I only set the constraint to be deferred when we really > need it? Would it be more efficient to perform the deletes explicitly > within a transaction rath

Re: Slow or Cannot Connect to PostgreSQL Instance Service on Windows 10

On 7/22/20 10:24 AM, Surya Widyanto wrote: Hi, I have PostgreSQL v12.3 (x64) installed on my Laptop running Windows 10 Home Single Language (64-bit). In two or three times since I fresh install the PostgreSQL and windows on 17-05-2020, 06:04:56 (GMT). Meant to ask earlier, where did you inst

Re: Slow or Cannot Connect to PostgreSQL Instance Service on Windows 10

On 7/22/20 2:43 PM, Surya Widyanto wrote: On 23-07-2020 02:07, Adrian Klaver wrote: On 7/22/20 10:24 AM, Surya Widyanto wrote: Hi, Is the database server you are connecting to on the laptop or some other machine? The Database is on the same machine/laptop, I try to connect using *localho

Re: Slow or Cannot Connect to PostgreSQL Instance Service on Windows 10

On 23-07-2020 02:07, Adrian Klaver wrote: On 7/22/20 10:24 AM, Surya Widyanto wrote: Hi, I have PostgreSQL v12.3 (x64) installed on my Laptop running Windows 10 Home Single Language (64-bit). In two or three times since I fresh install the PostgreSQL and windows on 17-05-2020, 06:04:56 (GMT).

Re: Switching Primary Keys to BigInt

On Wed, Jul 22, 2020 at 4:45 PM Ireneusz Pluta/wp.pl wrote: > W dniu 2020-07-21 o 17:30, Mohamed Wael Khobalatte pisze: > > we are planning to move some primary keys from int to bigint because we > are approaching the type limit > If that does not break your business logic, you might arrange to u

Re: Switching Primary Keys to BigInt

W dniu 2020-07-21 o 17:30, Mohamed Wael Khobalatte pisze: we are planning to move some primary keys from int to bigint because we are approaching the type limit If that does not break your business logic, you might arrange to use the negative half of the ::int value range. Ugly, but this might

Re: Switching Primary Keys to BigInt

> Believe it or not I pitched a similar idea internally. I'll explore it in a test run. By similar idea, I am referencing your suggestion of dropping the primary key constraint. >

Re: Switching Primary Keys to BigInt

> > One presumes you may be planning to use pglogical or another similar > solution to upgrade to a new Postgres version soon, and would have a > convenient time then to change schema. I am curious, why not just stick > with the single column unique index and forgo for the primary key > constraint

Re: Switching Primary Keys to BigInt

On Wed, Jul 22, 2020 at 12:23 PM Mohamed Wael Khobalatte < mkhobala...@grubhub.com> wrote: > No worries. I suppose the answer to the original question, which is how to > avoid a table scan when adding a primary key constraint to a newly > backfilled column is "there is no way"? Downtime might be a

Re: Slow or Cannot Connect to PostgreSQL Instance Service on Windows 10

On 7/22/20 10:24 AM, Surya Widyanto wrote: Hi, I have PostgreSQL v12.3 (x64) installed on my Laptop running Windows 10 Home Single Language (64-bit). In two or three times since I fresh install the PostgreSQL and windows on 17-05-2020, 06:04:56 (GMT). By two or three times I mean, if this slow

Re: Doubt in mvcc

On Mon, Jul 13, 2020 at 10:41:28AM +0200, Francisco Olarte wrote: > Rama: > > On Mon, Jul 13, 2020 at 9:52 AM Rama Krishnan wrote: > > I m preparing for interview one of the recruiter asked me mvcc drawbacks as > > i told due to mvcc it use more space and need to perform maintenance > > activit

Re: Switching Primary Keys to BigInt

On Wed, Jul 22, 2020 at 11:13 AM Daniel Verite wrote: > Mohamed Wael Khobalatte wrote: > > > We lock the table as a precaution, with the understanding that we are > > undergoing a "small" downtime to finish replacing the int id by the new > > bigint > > Ah, sorry I overlooked that most ro

Re: Transaction control in SECURITY DEFINER procedures

On Tue, Jul 7, 2020 at 12:13:42PM +0100, Chris Sterritt wrote: > The documentation for CREATE PROCEDURE informs us "A SECURITY DEFINER  > procedure > cannot execute transaction control statements (for example, COMMIT and  > ROLLBACK > , depending on the language)." > > Can anyone let me know why

Slow or Cannot Connect to PostgreSQL Instance Service on Windows 10

Hi, I have PostgreSQL v12.3 (x64) installed on my Laptop running Windows 10 Home Single Language (64-bit). In two or three times since I fresh install the PostgreSQL and windows on 17-05-2020, 06:04:56 (GMT). By two or three times I mean, if this slow connection problem happen, than it wil occ

Re: CASCADE/fkey order

I checked, and changing the `bazinga_foo_bar` constraint to: alter table bazinga add constraint bazinga_foo_bar foreign key (foo_id, bar_id) references foo_bar (foo_id, bar_id) deferrable initially deferred; seems to fix it to work as we were expecting. Is that particularly costly? Should I onl

Re: Problem with pg_service.conf

On 7/21/20 2:35 PM, Michał Lis wrote: Hello, I can't connect to a database via service defined in pg_service.conf file from remote machine. Connection from local machine using this service definition works fine. I tested it on PostgreSQL 9.4 and 9.6. The other problem is with the localizati

Re: Problem with pg_service.conf

On 7/21/20 4:35 PM, Michał Lis wrote: Hello, I can't connect to a database via service defined in pg_service.conf file from remote machine. Connection from local machine using this service definition works fine. I tested it on PostgreSQL 9.4 and 9.6. The other problem is with the localizati

Re: CASCADE/fkey order

On Wed, Jul 22, 2020 at 8:24 AM Samuel Nelson wrote: > Is there a way to force the delete to cascade to tables in a specific > order? > No really, but you can defer constraint checking. https://www.postgresql.org/docs/12/sql-set-constraints.html David J.

Re: Logical replication from 11.x to 12.x and "unique key violations"

Adrian Klaver schrieb am 22.07.2020 um 16:42: Or is this a copy/paste issue? That.

CASCADE/fkey order

Hi all, We've got an interesting case where we want deletes to cascade if one table was hit directly, but not another. We can show that the delete _would_ cascade from one foreign key relationship, but the delete is actually blocked by the foreign key constraint from the other relationship. A so

HA setup with pg pool in docker

Hi Team, We are planning to build a HA setup with pgpool in docker, Could you please let us know prerequisites for it. 1. How many servers are needed for this?, can we plan with 2 servers like below config. Primary instance and pgpool in two different docker containers in server 1 and standby

Problem with pg_service.conf

Hello, I can't connect to a database via service defined in pg_service.conf file from remote machine. Connection from local machine using this service definition works fine. I tested it on PostgreSQL 9.4 and 9.6. The other problem is with the localization of the pg_service.conf file. Readi

Re: Switching Primary Keys to BigInt

Mohamed Wael Khobalatte wrote: > We lock the table as a precaution, with the understanding that we are > undergoing a "small" downtime to finish replacing the int id by the new > bigint Ah, sorry I overlooked that most row updates are done pre-transaction in a preliminary step: /* in ba

Re: Logical replication from 11.x to 12.x and "unique key violations"

On 7/21/20 10:36 PM, Thomas Kellerer wrote: Adrian Klaver schrieb am 21.07.2020 um 17:07: No, as mentioned, those are varchar(20) columns. The values are generated by the application (no default value defined for the column) Aah I see my mistake I was going off your follow up question not the

Re: Switching Primary Keys to BigInt

On Wed, Jul 22, 2020 at 9:27 AM Daniel Verite wrote: > Mohamed Wael Khobalatte wrote: > > > > alter table change_seq alter COLUMN id set data > > > type bigint; > > > This is significant downtime, since it locks exclusively, no? We want to > > avoid that. > > Well, in the steps you mentio

Re: Switching Primary Keys to BigInt

Mohamed Wael Khobalatte wrote: > > alter table change_seq alter COLUMN id set data > > type bigint; > This is significant downtime, since it locks exclusively, no? We want to > avoid that. Well, in the steps you mentioned upthread, the transaction starts by doing LOCK TABLE some_table, s

Re: Logical replication from 11.x to 12.x and "unique key violations"

Thomas Kellerer schrieb am 13.07.2020 um 11:52: > If I create the publication with all needed tables (about 50) at > once, I get "duplicate key value violates unique constraint xxx_pkey" > errors during the initial replication (when creating the > subscription). Turns out the problem was - once ag

How to create function returning numeric from string containing percent character

val function should return numeric value from string up to first non-digit character, considering first decimal point also: val('1,2TEST') should return 1.2 val('1,2,3') should return 1.2 val('-1,2,3') should return -1.2 I tried CREATE OR REPLACE FUNCTION public.VAL(value text)