Re: POLL: Adding transaction status to default psql prompt

[Guillaume Lelarge]

Le jeu. 6 févr. 2020 à 03:55, Vik Fearing  a
écrit :

> Hello,
>
> I proposed a patch to add %x to PROMPT1 and PROMPT2 by default in psql.
>
> The effect of this is:
>
> - nothing at all when not in a transaction,
>

Too bad it doesn't add a space, so that we still have the same space used
byt the prompt whatever the status of the transaction. That's probably the
only thing that bugs me with %x.

- adding a '*' when in a transaction or a '!' when in an aborted
>   transaction.
>
> Before making a change to a long-time default, a poll in this group was
> requested.
>
> Please answer +1 if you want or don't mind seeing transaction status by
> default in psql or -1 if you would prefer to keep the current default.
>
> Thanks!
>
> +1 from me.
>

+1 from me as well. Actually, thanks to you, I added it to my .psqlrc file
in the meantime. But it would be very interesting when I work on a
customer's server.


-- 
Guillaume.

Re: POLL: Adding transaction status to default psql prompt

[Laurenz Albe]

On Thu, 2020-02-06 at 03:54 +0100, Vik Fearing wrote:
> I proposed a patch to add %x to PROMPT1 and PROMPT2 by default in psql.

+1

Yours,
Laurenz Albe

Re: POLL: Adding transaction status to default psql prompt

[Daevor The Devoted]

+1

(+10 billion actually, but sadly, I'm only allowed a +1)

On Thu, Feb 6, 2020 at 4:55 AM Vik Fearing  wrote:

> Hello,
>
> I proposed a patch to add %x to PROMPT1 and PROMPT2 by default in psql.
>
> The effect of this is:
>
> - nothing at all when not in a transaction,
> - adding a '*' when in a transaction or a '!' when in an aborted
>   transaction.
>
> Before making a change to a long-time default, a poll in this group was
> requested.
>
> Please answer +1 if you want or don't mind seeing transaction status by
> default in psql or -1 if you would prefer to keep the current default.
>
> Thanks!
>
> +1 from me.
> --
> Vik Fearing
>
>
>

Re: POLL: Adding transaction status to default psql prompt

[David G. Johnston]

On Wed, Feb 5, 2020 at 7:55 PM Vik Fearing  wrote:

> Please answer +1 if you want or don't mind seeing transaction status by
> default in psql or -1 if you would prefer to keep the current default.
>

+1

Re: POLL: Adding transaction status to default psql prompt

[Adrian Klaver]

On 2/5/20 8:05 PM, Vik Fearing wrote:

On 06/02/2020 04:55, Steve Baldwin wrote:

Hi Vik,

I'm not sure why this should be the default when it is easy to override the
default via a psqrc file. If you know enough to do it, you can.


Because it isn't always easy to modify the .psqlrc file.  This is
especially true if you frequently connect to other people's systems.


Otherwise I
don't think it adds any value as a default since a novice user isn't going
to know what */!/? means. Maybe I'm missing something.


A novice user won't understand any of psql's meta commands, either.


Except they can be found by doing \?

-1




-1 from me.


Thank you for voting.




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

Re: POLL: Adding transaction status to default psql prompt

[Michael Paquier]

On Thu, Feb 06, 2020 at 03:54:48AM +0100, Vik Fearing wrote:
> I proposed a patch to add %x to PROMPT1 and PROMPT2 by default in psql.
> 
> The effect of this is:
> 
> - nothing at all when not in a transaction,
> - adding a '*' when in a transaction or a '!' when in an aborted
>   transaction.
> 
> Before making a change to a long-time default, a poll in this group was
> requested.

Thanks Vik for starting a new thread.  For reference here is the
thread where the patch is being discussed:
https://www.postgresql.org/message-id/09502c40-cfe1-bb29-10f9-4b3fa7b2b...@2ndquadrant.com
--
Michael


signature.asc
Description: PGP signature

Re: POLL: Adding transaction status to default psql prompt

[Christophe Pettus]

> On Feb 5, 2020, at 18:54, Vik Fearing  wrote:
> Please answer +1 if you want or don't mind seeing transaction status by
> default in psql or -1 if you would prefer to keep the current default.

+1.

Re: POLL: Adding transaction status to default psql prompt

[Hari Kiran]

On 06/02/20 8:24 am, Vik Fearing wrote:

Hello,

I proposed a patch to add %x to PROMPT1 and PROMPT2 by default in psql.

The effect of this is:

- nothing at all when not in a transaction,
- adding a '*' when in a transaction or a '!' when in an aborted
   transaction.

Before making a change to a long-time default, a poll in this group was
requested.

Please answer +1 if you want or don't mind seeing transaction status by
default in psql or -1 if you would prefer to keep the current default.

Thanks!

+1 from me.

double +1 from me

Re: POLL: Adding transaction status to default psql prompt

[Vik Fearing]

On 06/02/2020 04:55, Steve Baldwin wrote:
> Hi Vik,
> 
> I'm not sure why this should be the default when it is easy to override the
> default via a psqrc file. If you know enough to do it, you can.

Because it isn't always easy to modify the .psqlrc file.  This is
especially true if you frequently connect to other people's systems.

> Otherwise I
> don't think it adds any value as a default since a novice user isn't going
> to know what */!/? means. Maybe I'm missing something.

A novice user won't understand any of psql's meta commands, either.

> -1 from me.

Thank you for voting.
-- 
Vik Fearing

Re: POLL: Adding transaction status to default psql prompt

[Steve Baldwin]

Hi Vik,

I'm not sure why this should be the default when it is easy to override the
default via a psqrc file. If you know enough to do it, you can. Otherwise I
don't think it adds any value as a default since a novice user isn't going
to know what */!/? means. Maybe I'm missing something.

-1 from me.

On Thu, Feb 6, 2020 at 1:55 PM Vik Fearing  wrote:

> Hello,
>
> I proposed a patch to add %x to PROMPT1 and PROMPT2 by default in psql.
>
> The effect of this is:
>
> - nothing at all when not in a transaction,
> - adding a '*' when in a transaction or a '!' when in an aborted
>   transaction.
>
> Before making a change to a long-time default, a poll in this group was
> requested.
>
> Please answer +1 if you want or don't mind seeing transaction status by
> default in psql or -1 if you would prefer to keep the current default.
>
> Thanks!
>
> +1 from me.
> --
> Vik Fearing
>
>
>

Re: POLL: Adding transaction status to default psql prompt

[Ian Barwick]

On Thu, 6 Feb 2020 at 11:55, Vik Fearing  wrote:

> Hello,
>
> I proposed a patch to add %x to PROMPT1 and PROMPT2 by default in psql.
>
> The effect of this is:
>
> - nothing at all when not in a transaction,
> - adding a '*' when in a transaction or a '!' when in an aborted
>   transaction.
>
> Before making a change to a long-time default, a poll in this group was
> requested.
>
> Please answer +1 if you want or don't mind seeing transaction status by
> default in psql or -1 if you would prefer to keep the current default.


+1 from me.

I've been configuring this in every single .psqlrc I "own" since it was
first added in 7.4,
and sorely miss it not being just available in environments I don't "own".


Regards

Ian Barwick

Re: POLL: Adding transaction status to default psql prompt

[Matt Zagrabelny]

+1

On Wed, Feb 5, 2020 at 9:15 PM Ahmed, Nawaz (Fuji Xerox Australia)
 wrote:
>
> +1
>
> -Original Message-
> From: Vik Fearing 
> Sent: Thursday, 6 February 2020 1:55 PM
> To: pgsql-general@lists.postgresql.org
> Subject: POLL: Adding transaction status to default psql prompt
>
> Hello,
>
> I proposed a patch to add %x to PROMPT1 and PROMPT2 by default in psql.
>
> The effect of this is:
>
> - nothing at all when not in a transaction,
> - adding a '*' when in a transaction or a '!' when in an aborted
>   transaction.
>
> Before making a change to a long-time default, a poll in this group was 
> requested.
>
> Please answer +1 if you want or don't mind seeing transaction status by 
> default in psql or -1 if you would prefer to keep the current default.
>
> Thanks!
>
> +1 from me.
> --
> Vik Fearing
>
>
>
> IMPORTANT NOTE: Fuji Xerox email transmission, including any attachments, is 
> private and confidential and may contain legally privileged information. It 
> is for the addressee's attention only. If you are not the intended recipient 
> and have received this transmission, you must not use, edit, print, copy or 
> disclose its contents to any person or disseminate the information contained 
> herein or hereto attached, and you must notify sender immediately by return 
> email and delete this transmission from your system. Any confidentiality, 
> privilege or copyright is not waived or lost because this e-mail has been 
> sent to you in error. We have used reasonable efforts to protect this 
> transmission from computer viruses and other malicious software, but no 
> warranty is made and the sender takes no responsibility for any loss or 
> damage incurred from using this email or the information contained in this 
> email.

RE: POLL: Adding transaction status to default psql prompt

[Ahmed, Nawaz (Fuji Xerox Australia)]

+1

-Original Message-
From: Vik Fearing 
Sent: Thursday, 6 February 2020 1:55 PM
To: pgsql-general@lists.postgresql.org
Subject: POLL: Adding transaction status to default psql prompt

Hello,

I proposed a patch to add %x to PROMPT1 and PROMPT2 by default in psql.

The effect of this is:

- nothing at all when not in a transaction,
- adding a '*' when in a transaction or a '!' when in an aborted
  transaction.

Before making a change to a long-time default, a poll in this group was 
requested.

Please answer +1 if you want or don't mind seeing transaction status by default 
in psql or -1 if you would prefer to keep the current default.

Thanks!

+1 from me.
--
Vik Fearing



IMPORTANT NOTE: Fuji Xerox email transmission, including any attachments, is 
private and confidential and may contain legally privileged information. It is 
for the addressee's attention only. If you are not the intended recipient and 
have received this transmission, you must not use, edit, print, copy or 
disclose its contents to any person or disseminate the information contained 
herein or hereto attached, and you must notify sender immediately by return 
email and delete this transmission from your system. Any confidentiality, 
privilege or copyright is not waived or lost because this e-mail has been sent 
to you in error. We have used reasonable efforts to protect this transmission 
from computer viruses and other malicious software, but no warranty is made and 
the sender takes no responsibility for any loss or damage incurred from using 
this email or the information contained in this email.

Re: POLL: Adding transaction status to default psql prompt

[Nikolay Samokhvalov]

+1 of course

On Wed, Feb 5, 2020 at 6:55 PM Vik Fearing  wrote:

> Hello,
>
> I proposed a patch to add %x to PROMPT1 and PROMPT2 by default in psql.
>
> The effect of this is:
>
> - nothing at all when not in a transaction,
> - adding a '*' when in a transaction or a '!' when in an aborted
>   transaction.
>
> Before making a change to a long-time default, a poll in this group was
> requested.
>
> Please answer +1 if you want or don't mind seeing transaction status by
> default in psql or -1 if you would prefer to keep the current default.
>
> Thanks!
>
> +1 from me.
> --
> Vik Fearing
>
>
>

POLL: Adding transaction status to default psql prompt

[Vik Fearing]

Hello,

I proposed a patch to add %x to PROMPT1 and PROMPT2 by default in psql.

The effect of this is:

- nothing at all when not in a transaction,
- adding a '*' when in a transaction or a '!' when in an aborted
  transaction.

Before making a change to a long-time default, a poll in this group was
requested.

Please answer +1 if you want or don't mind seeing transaction status by
default in psql or -1 if you would prefer to keep the current default.

Thanks!

+1 from me.
-- 
Vik Fearing

Re: calculating the MD5 hash of role passwords in C

[Stephen Frost]

Greetings,

* Matthias Apitz (g...@unixarea.de) wrote:
> If I look into the database I see:
> 
> sisis71=# select rolname, rolpassword from pg_authid where rolname = 'sisis';
>  rolname | rolpassword
> -+-
>  sisis   | md52f128a1fbbecc4b16462e8fc8dda5cd5
> 
> I know the clear text password of the role, it is simple 'sisis123', how
> could I calculate the above MD5 hash from the clear text password, for
> example in C? Which salt is used for the crypt(3) function?

Didn't see it mentioned here, but it probably should be- newer PG
installs really should be using SCRAM and not md5 and the way the
validator is built/stored is rather different from the simple md5 that
you've probably seen in the past.

Thanks,

Stephen


signature.asc
Description: PGP signature

Weird behavior with update cascade on partitioned tables when moving data between partitions

[Márcio Antônio Sepp]

Hi all,


Update cascade apparently doesn't work as well on partiotioned tables (when
moving data between different partitions). 
Please, look at example below:


-- Create master partitioned table;
CREATE TABLE users (
id serial NOT NULL,
username   text NOT NULL,
password   text,
primary key (id)
)
PARTITION BY RANGE ( id );

CREATE TABLE users_p0
partition OF users 
FOR VALUES FROM (MINVALUE) TO (10);
CREATE TABLE users_p1
partition OF users
FOR VALUES FROM (10) TO (20);


-- Create detail table;
create table logs ( id serial not null,
user_id integer not null,
primary key (id),
foreign key (user_id) references users (id) on update cascade 
); 


test=# insert into users (id, username, password) values (1, 'user',
'pass');
INSERT 0 1

test=# insert into logs (id, user_id) values (1, 1);
INSERT 0 1

-- update without changing partition
test=# update users set id = 8 where id = 1;
UPDATE 1

-- Lets see the log table
test=# select * from logs;
 id | user_id
+-
  1 |   8
(1 row)

-- update changing partition;
test=# update users set id = 12 where id = 8;
ERROR:  update or delete on table "users_p0" violates foreign key constraint
"logs_user_id_fkey1" on table "logs"
DETAIL:  Key (id)=(8) is still referenced from table "logs".


IF create the foreign key with option "on update cascade on delete cascade"
after running this command: update users set id = 12 where id = 8; the
Record on logs table Will be deleted. 

Is this the expectec behavior? 


--
Att
Márcio A. Sepp

Re: Should I reinstall over current installation?

[Chris Charley]

Moreno, thank you for all your help.

Following your instructions, I was able to recover my databases. All is
good.

Chris


On Wed, Feb 5, 2020 at 6:45 AM Moreno Andreo 
wrote:

> Il 04/02/2020 21:18, Chris Charley ha scritto:
> > Hello Moreno
> >
> > Thanks for the reply!
> >
> > I ran Services and it reported postsql as Disabled.
>
> A disabled service will never run nor leave error messages anywhere
>
> Right click on the Postgresql service, select properties.
> In the next window, choose startup type (the combo under the executable
> path) and select Automatic, then click on the Start button below
> Click OK and close the window.
>
> Now something should happen. If everything goes well, You'll see
> "Started" and "Automatic" in the two columns next to the service
> description, and you can start your application
> If there is an error message, then it should log something. See my last
> mail to extract log informations and post them here
>
> > I am considering reinstall because I couldn't find any helpful info
> > from these services.
>
> You got no helpful info because service does not start, andd so does not
> log :-)
>
> > Thanks again for your help.
> >  I suspect when Windows was reinstalled, the firewall was reconfigured
> > and PostgreSQL wasn't allowed to access the port?
>
> ATM there's a service start issue to be resolved, if there's also
> something about the firewall, we'll think about it later.
>
> Moreno.-
>
>
>
>

Re: Restore is failing

[Adrian Klaver]

On 2/5/20 12:47 AM, Sonam Sharma wrote:

Version is 11.2 .. took schema dump using pg_dump -d -n (schma) / gunzip ..


So the above was a plain text dump that you then piped through 
gunzip(?)? That does not make sense.


The error I am getting while running restore.


If it is a plain text dump you would need to use psql not pg_restore.



On Wed, Feb 5, 2020, 2:03 PM Rob Sargent > wrote:




On 2/5/20 1:24 AM, Sonam Sharma wrote:

I am copying schema data to qa box from prod.its failing and
giving below error :

WARNING: terminating connection because of crash of another server
process

DETAIL: The postmaster has commanded this server process to roll
back the current transaction and exit, because another server
process exited abnormally and possibly corrupted shared memory.

HINT:  In a moment you should be able to reconnect to the database
and repeat your command.

server closed the connection unexpectedly.


Can some one please help. Work_mem is set to 8gb.


Thanks , Sonam


What version of postgres?
Method of copy?
Identify which server process is failing, what's the error there.




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

Re: Error handling: Resume work after error

[Raul Kaubi]

Oh yeah, I must add new *begin ... exception .. end;* block between.

So like this:

BEGIN
>


BEGIN
> EXCEPTION when . then ..
> END;
>
> END;


At first I did like this:

BEGIN

.

EXCEPTION when . then ..;
> END;


Raul

Kontakt hubert depesz lubaczewski () kirjutas kuupäeval
K, 5. veebruar 2020 kell 17:03:

> On Wed, Feb 05, 2020 at 04:17:09PM +0200, Raul Kaubi wrote:
> > Let's say I want my procedure/function to resume work after specific
> error
> > ( duplicate_table ).
> > Is it possible to resume work after error..?
> > EXCEPTION
> > > WHEN duplicate_table then ..;
>
> Sure you can:
>
>
> https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
>
> Best regards,
>
> depesz
>
>

Re: initialize and use variable in query

[Олег Самойлов]

Just refactor DO block to function that returns row set and put SELECT inside.

> 29 дек. 2018 г., в 18:40, Glenn Schultz  написал(а):
> 
> All,
> 
> I need to initialize a variable and then use it in query.  Ultimately this 
> will part of a recursive CTE but for now I just need to work this out.  I 
> followed the docs and thought I needed something like this.  But does not 
> work-maybe I have misunderstood.  Is this possible?
> 
>   SET max_parallel_workers_per_gather = 8;
>   SET random_page_cost = 1;
>   SET enable_partitionwise_aggregate = on;
>   
>   Do $$
>   Declare startdate date;
>   BEGIN
>   startdate := (select max(fctrdt) from fnmloan);
>   END $$;
>   
>   select 
>   fnmloan.loanseqnum
>   ,fnmloan.currrpb
>   from
>   fnmloan
>   
>   join
>   fnmloan_data
>   on
>   fnmloan_data.loanseqnum = fnmloan.loanseqnum
>   
>   where
>   fnmloan.fctrdt = (select * from startdate)
> 
>   limit 10

Re: Error handling: Resume work after error

[hubert depesz lubaczewski]

On Wed, Feb 05, 2020 at 04:17:09PM +0200, Raul Kaubi wrote:
> Let's say I want my procedure/function to resume work after specific error
> ( duplicate_table ).
> Is it possible to resume work after error..?
> EXCEPTION
> > WHEN duplicate_table then ..;

Sure you can:

https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

Best regards,

depesz

Error handling: Resume work after error

[Raul Kaubi]

Hi

PostgreSQL 12.1

Let's say I want my procedure/function to resume work after specific error
( duplicate_table ).
Is it possible to resume work after error..?


EXCEPTION
> WHEN duplicate_table then ..;


Or is there different approach to achieve that..?


Thanks
Raul

Re: Declare variable from other variable

[Raul Kaubi]

Makes sense yeah.

Thanks for both of your help.

Raul

Kontakt hubert depesz lubaczewski () kirjutas kuupäeval
K, 5. veebruar 2020 kell 14:50:

> On Wed, Feb 05, 2020 at 02:42:42PM +0200, Raul Kaubi wrote:
> > Thanks, it worked!
> >
> > By the way, what does this "**j"* mean there..? (this does not mean
> > multiply there?)
>
> it's normal multiplication.
>
> Your "j" variable is integer.
>
> So, '1 month'::interval * j is some number of months.
> > And what if, I would like to declare v_to_date also, so that v_to_date is
> > always + 1 month compared to v_date_from..?
>
> v_to_date := v_from_date + '1 month'::interval; ?
>
> Best regards,
>
> depesz
>
>

Re: Declare variable from other variable

[hubert depesz lubaczewski]

On Wed, Feb 05, 2020 at 02:42:42PM +0200, Raul Kaubi wrote:
> Thanks, it worked!
> 
> By the way, what does this "**j"* mean there..? (this does not mean
> multiply there?)

it's normal multiplication.

Your "j" variable is integer.

So, '1 month'::interval * j is some number of months.
> And what if, I would like to declare v_to_date also, so that v_to_date is
> always + 1 month compared to v_date_from..?

v_to_date := v_from_date + '1 month'::interval; ?

Best regards,

depesz

Re: Declare variable from other variable

[Raul Kaubi]

Thanks, it worked!

By the way, what does this "**j"* mean there..? (this does not mean
multiply there?)

And what if, I would like to declare v_to_date also, so that v_to_date is
always + 1 month compared to v_date_from..?

-- This one will work, but can this be done simpler..?
v_to_date := (date_trunc('month',current_date)::date + interval '1 month' +
interval '1 month'*j)::date;

Raul

Kontakt Yasin Sari () kirjutas kuupäeval K, 5.
veebruar 2020 kell 14:28:

> On Wed, Feb 5, 2020 at 2:22 PM Raul Kaubi  wrote:
>
>>
>> DO $$
>>> DECLARE
>>> v_var integer := 1;
>>> v_from_date date;
>>> BEGIN
>>> for j in 0..v_var LOOP
>>> v_from_date := (date_trunc('month',current_date) + interval 'j
>>> month')::date;
>>> RAISE NOTICE '%', v_from_date;
>>> END LOOP;
>>> END;
>>> $$ LANGUAGE plpgsql;
>>
>>
>>> ERROR:  invalid input syntax for type interval: "j month"
>>> LINE 1: ...LECT (date_trunc('month',current_date) + interval 'j
>>> month')...
>>
>>
>>
>>
> If you replace red line with this one it will work:
>
> v_from_date := (date_trunc('month',current_date) + interval '1
> month'*j)::date;
>

Re: Declare variable from other variable

[Thomas Kellerer]

Raul Kaubi schrieb am 05.02.2020 um 12:21:
> How can I declare another variable from another variable.
> Basically from oracle, I can just: 
>
> var1 := 'asda'||var2;
>
> In postgres, I have the following example, I would like to use variable j to 
> add number of months there.
>
> " interval 'j month')::date; "
>
>
> DO $$
> DECLARE
> v_var integer := 1;
> v_from_date date;
> BEGIN
> for j in 0..v_var LOOP
> v_from_date := (date_trunc('month',current_date) + interval 'j 
> month')::date;
> RAISE NOTICE '%', v_from_date;
> END LOOP;
> END;
> $$ LANGUAGE plpgsql;

The easiest way is to use make_interval()

v_from_date := (date_trunc('month',current_date) + make_interval(months => 
j))::date;


But it sounds as if generate_series() is what you are really looking for.

Re: Declare variable from other variable

[Yasin Sari]

On Wed, Feb 5, 2020 at 2:22 PM Raul Kaubi  wrote:

>
> DO $$
>> DECLARE
>> v_var integer := 1;
>> v_from_date date;
>> BEGIN
>> for j in 0..v_var LOOP
>> v_from_date := (date_trunc('month',current_date) + interval 'j
>> month')::date;
>> RAISE NOTICE '%', v_from_date;
>> END LOOP;
>> END;
>> $$ LANGUAGE plpgsql;
>
>
>> ERROR:  invalid input syntax for type interval: "j month"
>> LINE 1: ...LECT (date_trunc('month',current_date) + interval 'j
>> month')...
>
>
>
>
If you replace red line with this one it will work:

v_from_date := (date_trunc('month',current_date) + interval '1
month'*j)::date;

Re: Unable to startup postgres: Could not read from file "pg_clog/00EC"

[Alvaro Herrera]

On 2020-Feb-05, Nick Renders wrote:

> Is there anything specific I should check in our postgres installation /
> database to make sure it is running ok now? Anyway to see what the
> consequences were of purging that one pg_clog file?

Losing pg_clog files is pretty bad, and should not happen; then again,
this might have been something else (ie. the file was maybe not lost).
That said, wrongly overwriting files is even worse.

By zeroing an existing pg_clog file, you marked a bunch of transactions
as aborted.  Your data is now probably inconsistent, if not downright
corrupt.  I would be looking for my most recent backup ...

If you're very lucky, your database might be pg_dumpable.  I would try
that, followed by restoring it in a separate clean instance and seeing
what happens.

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

Re: Should I reinstall over current installation?

[Moreno Andreo]

Il 04/02/2020 21:18, Chris Charley ha scritto:

Hello Moreno

Thanks for the reply!

I ran Services and it reported postsql as Disabled.


A disabled service will never run nor leave error messages anywhere

Right click on the Postgresql service, select properties.
In the next window, choose startup type (the combo under the executable 
path) and select Automatic, then click on the Start button below

Click OK and close the window.

Now something should happen. If everything goes well, You'll see 
"Started" and "Automatic" in the two columns next to the service 
description, and you can start your application
If there is an error message, then it should log something. See my last 
mail to extract log informations and post them here


I am considering reinstall because I couldn't find any helpful info 
from these services.


You got no helpful info because service does not start, andd so does not 
log :-)



Thanks again for your help.
 I suspect when Windows was reinstalled, the firewall was reconfigured 
and PostgreSQL wasn't allowed to access the port?


ATM there's a service start issue to be resolved, if there's also 
something about the firewall, we'll think about it later.


Moreno.-

Re: Get rid of brackets around variable

[Raul Kaubi]

Awesome, thanks!

Kontakt Geoff Winkless () kirjutas kuupäeval K, 5.
veebruar 2020 kell 13:11:

>
>
> On Wed, 5 Feb 2020 at 10:48, Raul Kaubi  wrote:
>
>>
>> DO $$
>>> DECLARE
>>> cur cursor for
>>> select * from (values('logi_web'), ('logi_taustaprotsess')) as q (col1);
>>> BEGIN
>>> for i in cur LOOP
>>> RAISE NOTICE 'create table %_y2020m01 PARTITION OF % FOR VALUES FROM
>>> (''2019-12-01'') TO (''2020-01-01'')', i, i;
>>> END LOOP;
>>> END;
>>> $$ LANGUAGE plpgsql;
>>>
>>
>> If I execute, this is the output:
>>
>> NOTICE:  create table (logi_web)_y2020m01 PARTITION OF (logi_web) FOR
>>> VALUES FROM ('2019-12-01') TO ('2020-01-01')
>>> NOTICE:  create table (logi_taustaprotsess)_y2020m01 PARTITION OF
>>> (logi_taustaprotsess) FOR VALUES FROM ('2019-12-01') TO ('2020-01-01')
>>> DO
>>
>>
> You're returning rows from the cursor.
>
> You need to use i.col1 instead of i.
>
> Geoff
>

Declare variable from other variable

[Raul Kaubi]

Hi

PostgreSQL 12.1

How can I declare another variable from another variable.
Basically from oracle, I can just:

> var1 := 'asda'||var2;


In postgres, I have the following example, I would like to use variable j
to add number of months there.

" interval 'j month')::date; "


DO $$
> DECLARE
> v_var integer := 1;
> v_from_date date;
> BEGIN
> for j in 0..v_var LOOP
> v_from_date := (date_trunc('month',current_date) + interval 'j
> month')::date;
> RAISE NOTICE '%', v_from_date;
> END LOOP;
> END;
> $$ LANGUAGE plpgsql;


> ERROR:  invalid input syntax for type interval: "j month"
> LINE 1: ...LECT (date_trunc('month',current_date) + interval 'j month')...



Raul

Unable to startup postgres: Could not read from file "pg_clog/00EC"

[Nick Renders]

Hello,

Yesterday, we experienced some issues with our Postgres installation 
(v9.6 running on macOS 10.12).
It seems that the machine was automatically rebooted for a yet unknown 
reason, and afterwards we were unable to start the Postgres service.


The postgres log shows the following:

2020-02-04 15:20:41 CET LOG:  database system was interrupted; last 
known up at 2020-02-04 15:18:34 CET
2020-02-04 15:20:43 CET LOG:  database system was not properly shut 
down; automatic recovery in progress
2020-02-04 15:20:44 CET LOG:  invalid record length at 14A/9E426DF8: 
wanted 24, got 0

2020-02-04 15:20:44 CET LOG:  redo is not required
2020-02-04 15:20:44 CET FATAL:  could not access status of transaction 
247890764
2020-02-04 15:20:44 CET DETAIL:  Could not read from file "pg_clog/00EC" 
at offset 106496: Undefined error: 0.
2020-02-04 15:20:44 CET LOG:  startup process (PID 403) exited with exit 
code 1
2020-02-04 15:20:44 CET LOG:  aborting startup due to startup process 
failure

2020-02-04 15:20:44 CET LOG:  database system is shut down


After some searching, I found someone who had had a similar issue and 
was able to resolve it by overwriting the file in pg_clog.

So I tried the following command:

dd if=/dev/zero of=[dbpath]/pg_clog/00EC bs=256k count=1

and now the service is running again.


But I am worried that there might still be some issues that we haven't 
noticed yet. I also have no idea what caused this error in the first 
place. It might have been the reboot, but maybe the reboot was a result 
of a Postgres issue.


Is there anything specific I should check in our postgres installation / 
database to make sure it is running ok now? Anyway to see what the 
consequences were of purging that one pg_clog file?


Best regards,

Nick Renders

Re: Get rid of brackets around variable

[Geoff Winkless]

On Wed, 5 Feb 2020 at 10:48, Raul Kaubi  wrote:

>
> DO $$
>> DECLARE
>> cur cursor for
>> select * from (values('logi_web'), ('logi_taustaprotsess')) as q (col1);
>> BEGIN
>> for i in cur LOOP
>> RAISE NOTICE 'create table %_y2020m01 PARTITION OF % FOR VALUES FROM
>> (''2019-12-01'') TO (''2020-01-01'')', i, i;
>> END LOOP;
>> END;
>> $$ LANGUAGE plpgsql;
>>
>
> If I execute, this is the output:
>
> NOTICE:  create table (logi_web)_y2020m01 PARTITION OF (logi_web) FOR
>> VALUES FROM ('2019-12-01') TO ('2020-01-01')
>> NOTICE:  create table (logi_taustaprotsess)_y2020m01 PARTITION OF
>> (logi_taustaprotsess) FOR VALUES FROM ('2019-12-01') TO ('2020-01-01')
>> DO
>
>
You're returning rows from the cursor.

You need to use i.col1 instead of i.

Geoff

Get rid of brackets around variable

[Raul Kaubi]

Hi

PostgreSQL 12.1

I am trying to figure out, how can I get rid of brackets for variable.

Example as follows:

DO $$
> DECLARE
> cur cursor for
> select * from (values('logi_web'), ('logi_taustaprotsess')) as q (col1);
> BEGIN
> for i in cur LOOP
> RAISE NOTICE 'create table %_y2020m01 PARTITION OF % FOR VALUES FROM
> (''2019-12-01'') TO (''2020-01-01'')', i, i;
> END LOOP;
> END;
> $$ LANGUAGE plpgsql;
>

If I execute, this is the output:

NOTICE:  create table (logi_web)_y2020m01 PARTITION OF (logi_web) FOR
> VALUES FROM ('2019-12-01') TO ('2020-01-01')
> NOTICE:  create table (logi_taustaprotsess)_y2020m01 PARTITION OF
> (logi_taustaprotsess) FOR VALUES FROM ('2019-12-01') TO ('2020-01-01')
> DO


Now I have tried, even if I execute this statement:

EXECUTE 'create table '||i||'_y2020m01 PARTITION OF '||i||' FOR VALUES FROM
> (''2019-12-01'') TO (''2020-01-01'')';



ERROR:  syntax error at or near "("
> LINE 1: create table (logi_web)_y2020m01 PARTITION OF (logi_web) FOR...
>  ^
> QUERY:  create table (logi_web)_y2020m01 PARTITION OF (logi_web) FOR
> VALUES FROM ('2019-12-01') TO ('2020-01-01')
> CONTEXT:  PL/pgSQL function inline_code_block line 8 at EXECUTE
>

Then you can see, that it still puts these brackets around variable.

Raul

Re: Restore is failing

[Peter J. Holzer]

On 2020-02-05 13:54:56 +0530, Sonam Sharma wrote:
> I am copying schema data to qa box from prod.its failing and giving below 
> error
> :
> 
> 
> WARNING:  terminating connection because of crash of another server process
> 
> DETAIL:  The postmaster has commanded this server process to roll back the
> current transaction and exit, because another server process exited abnormally

> and possibly corrupted shared memory.

As the message states, the problem is with another server process. You
need to identify that process and why it crashed. Check the postgres
server log. It should contain an error message. You might also want to
check the syslog (on Linux or other Unixes) or equivalent for system
errors (e.g. out of memory, disk errors, etc.)

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Read: The best way to solve a problem

[Satheesh-Gsuite]

Your message

To:  pgsql-general@lists.postgresql.org
Subject:  Re: The best way to solve a problem
Sent:  05-02-2020 12:48

was read on 05-02-2020 14:51.


binADpolde_6K.bin
Description: message/disposition-notification

Re: Force Commit

[Andrei Zhidenkov]

You can workaround by simulation autonomous transaction using plpython or 
dblink. Or just performing a commit outside the stored procedure.

> On 5. Feb 2020, at 09:06, İlyas Derse  wrote:
> 
> I'm writing to you about  Commit. I want to do force commit query even if  I 
> have exception.
> It's like :
> 
> CREATE OR REPLACE PROCEDURE public."test"()
> LANGUAGE 'plpgsql'
> AS $BODY$
> DECLARE "a" integer  ;
> DECLARE "b" integer  ;
> BEGIN
> "a" = 1;
> "b" = 0;
>BEGIN  
>raise notice 'hata';
>update public."crud" set lastname = 'Tekindor' where autoid = 20;
>"a"="a"/"b";
>ROLLBACK;
> 
>   EXCEPTION
>   WHEN OTHERS THEN
> 
>COMMIT;
>   END ;
> END ;
> $BODY$;
> 
> How can I do force commit  ? 
> Thanks..

Re: Restore is failing

[Sonam Sharma]

Version is 11.2 .. took schema dump using pg_dump -d -n (schma) / gunzip
..

The error I am getting while running restore.

On Wed, Feb 5, 2020, 2:03 PM Rob Sargent  wrote:

>
>
> On 2/5/20 1:24 AM, Sonam Sharma wrote:
>
> I am copying schema data to qa box from prod.its failing and giving below
> error :
>
> WARNING:  terminating connection because of crash of another server process
>
> DETAIL:  The postmaster has commanded this server process to roll back the
> current transaction and exit, because another server process exited
> abnormally and possibly corrupted shared memory.
>
> HINT:  In a moment you should be able to reconnect to the database and
> repeat your command.
>
> server closed the connection unexpectedly.
>
>
> Can some one please help. Work_mem is set to 8gb.
>
>
> Thanks , Sonam
>
> What version of postgres?
> Method of copy?
> Identify which server process is failing, what's the error there.
>
>

Re: Restore is failing

[Rob Sargent]

On 2/5/20 1:24 AM, Sonam Sharma wrote:
I am copying schema data to qa box from prod.its failing and giving 
below error :


WARNING: terminating connection because of crash of another server process

DETAIL: The postmaster has commanded this server process to roll back 
the current transaction and exit, because another server process 
exited abnormally and possibly corrupted shared memory.


HINT:  In a moment you should be able to reconnect to the database and 
repeat your command.


server closed the connection unexpectedly.


Can some one please help. Work_mem is set to 8gb.


Thanks , Sonam


What version of postgres?
Method of copy?
Identify which server process is failing, what's the error there.

Restore is failing

[Sonam Sharma]

I am copying schema data to qa box from prod.its failing and giving below
error :

WARNING:  terminating connection because of crash of another server process

DETAIL:  The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.

HINT:  In a moment you should be able to reconnect to the database and
repeat your command.

server closed the connection unexpectedly.


Can some one please help. Work_mem is set to 8gb.


Thanks , Sonam

Re: Force Commit

[Pavel Stehule]

st 5. 2. 2020 v 9:10 odesílatel İlyas Derse  napsal:

> I'm writing to you about  Commit. I want to do force commit query even
> if  I have exception.
> It's like :
>
> CREATE OR REPLACE PROCEDURE public."test"()
> LANGUAGE 'plpgsql'
> AS $BODY$
> DECLARE "a" integer  ;
> DECLARE "b" integer  ;
> BEGIN
> "a" = 1;
> "b" = 0;
>BEGIN
>raise notice 'hata';
>update public."crud" set lastname = 'Tekindor' where autoid = 20;
>"a"="a"/"b";
>ROLLBACK;
>
>   EXCEPTION
>   WHEN OTHERS THEN
>
>COMMIT;
>   END ;
> END ;
> $BODY$;
>
> How can I do force commit  ?
> Thanks..
>

just you cannot to do it.

There is not possibility how to do it now.

Regards

Pavel

Force Commit

[İlyas Derse]

I'm writing to you about  Commit. I want to do force commit query even if
I have exception.
It's like :

CREATE OR REPLACE PROCEDURE public."test"()
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE "a" integer  ;
DECLARE "b" integer  ;
BEGIN
"a" = 1;
"b" = 0;
   BEGIN
   raise notice 'hata';
   update public."crud" set lastname = 'Tekindor' where autoid = 20;
   "a"="a"/"b";
   ROLLBACK;

  EXCEPTION
  WHEN OTHERS THEN

   COMMIT;
  END ;
END ;
$BODY$;

How can I do force commit  ?
Thanks..