Re: Wall shiping replica failed to recover database with error: invalid contrecord length 1956 at FED/38FFE208

[Kyotaro Horiguchi]

Thank you, and sorry for overlooking your comment.

At Thu, 14 Nov 2019 12:28:13 -0500, Tom Lane  wrote in 
> Kyotaro Horiguchi  writes:
> > At Wed, 2 Oct 2019 19:24:02 -0400, Stephen Frost  wrote 
> > in 
> >> * Tom Lane (t...@sss.pgh.pa.us) wrote:
> >>> Yeah, those messages are all pretty ancient, from when WAL was new and not
> >>> to be trusted much.  Perhaps the thing to do is move the existing info
> >>> into DETAIL and make the primary message be something like "reached
> >>> apparent end of WAL stream".
> 
> >> Yes, +1 on that.
> 
> > What do you think about this?
> 
> It seems overcomplicated.  Why do you need to reach into
> emode_for_corrupt_record's private state? 
> emode_for_corrupt_record's private state?  I think we could just
> change the message text without that, and leave the emode
> machinations as-is.

I tried to avoid messages at the same LSN. But it is done by
emode_for_corrupt_record by reducing error level. I reverted that
part.

> I don't understand the restriction to "if (RecPtr == InvalidXLogRecPtr)"
> either?  Maybe that's fine but the comment fails to explain it.

"When we end up here while reading successive recored" meant that, but
it is not explicit. If RecPtr was not invalid, it means that the
caller is requesting for a specific record that should exist.  It is
not end-of-wal at all. I rewrote the comment.

> Another point is that, as the comment for emode_for_corrupt_record
> notes, we don't really want to consider that we've hit end-of-WAL
> when reading any source except XLOG_FROM_PG_WAL.  So I think the
> change of message ought to include a test of the source, but this
> doesn't.

Maybe no. The function just mutes messages for repeated error on
XLOG_FROM_WAL. We consider end-of-WAL while XLOG_FROM_ARCHIVE.  In
this version the "reached end of WAL" is not emitted when
emode_for_corrupt_record decided not to show the message.

> Also, the business with an "operation" string violates the message
> translatability guideline about "don't assemble a message out of
> phrases".  If we want to have that extra detail, it's better just
> to make three separate ereport() calls with separately translatable
> messages.

Though I thought that the operation is just a noun and it is safely
processed separately, I followed your comment. And this version takes
the more verbose one of the previous two.

> Also, it seems wrong that the page TLI check, just below, is where
> it is and isn't part of the main set of page header sanity checks.
> That's sort of unrelated to this patch, except not really, because
> shouldn't a failure of that test also be treated as an "end of WAL"
> condition?

It seems checking if xlogreader did something wrong, since it is
pluggable.  I'm not sure there is any concrete reason for that,
though.  As for recovery, it is actually redundant because
XLogFileReadAnyTLI already checked that for the record, but it doesn't
matter.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center
>From 94ee9c982afc5b39bb062c59a95ac2323a4109c8 Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi 
Date: Fri, 17 Jan 2020 17:15:58 +0900
Subject: [PATCH] Change end-of-WAL message less scary

At the end of WAL during recovery, users read just "invalid record"
message in logs, which is too scary. This patch change the message for
the case to "reached end of WAL" to let users know that they are not
in a trouble.
---
 src/backend/access/transam/xlog.c | 48 +--
 1 file changed, 40 insertions(+), 8 deletions(-)

diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 7f4f784c0e..a7757ea4bf 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -4279,6 +4279,10 @@ ReadRecord(XLogReaderState *xlogreader, XLogRecPtr RecPtr, int emode,
 		EndRecPtr = xlogreader->EndRecPtr;
 		if (record == NULL)
 		{
+			int actual_emode =
+emode_for_corrupt_record(emode,
+		 RecPtr ? RecPtr : EndRecPtr);
+
 			if (readFile >= 0)
 			{
 close(readFile);
@@ -4286,15 +4290,43 @@ ReadRecord(XLogReaderState *xlogreader, XLogRecPtr RecPtr, int emode,
 			}
 
 			/*
-			 * We only end up here without a message when XLogPageRead()
-			 * failed - in that case we already logged something. In
-			 * StandbyMode that only happens if we have been triggered, so we
-			 * shouldn't loop anymore in that case.
+			 * Invalid RecPtr here means we are reading successive records
+			 * during recovery. If we get here during recovery, we assume that
+			 * we reached the end of WAL.  Otherwise something's really wrong
+			 * and we report just only the errormsg if any. If we don't receive
+			 * errormsg here, we already logged something.  We are going to
+			 * emit duplicate message at the same LSN if
+			 * emode_for_currupt_record decided to mute it.  We don't repeat
+			 * "reached end of WAL" in the muted messages.
+			 *
+			 * Note: errormsg is alreay translated.
 			 */
-			if (errormsg)
-ereport(emo

postgresql commands(psql,createdb,dropdb) are not working from shell script

[ramesh penumalli]

Dear Team,

I am using postgresql 9.2.12 version and I am facing  an issue while
running the executing the commands createdb,dropdb,psql commands in the
shell script which is running from Jenkins to restore prod data into stage
environment.

Jenkins job is failing with the below output.I have verified the script and
the scrit is fine and it seems to be an issue with the configuration of
postgresql  and not sure where to make the changes.

line 48: dropdb: command not found

line 50: createdb: command not found

line 52: psql: command not found


Kindly guide me to rectify the issue.


Thanks and Regards,

Ramesh

Regarding automatic table partitioning in PostgreSQL 12 version is possible or not

[Durgamahesh Manne]

Hi
 To the respected PostgreSQL international team

Please let me know that automatic table partitioning is possible in pgsql
12 or not without using trigger function



Regards
Durgamahesh Manne

Re: postgresql commands(psql,createdb,dropdb) are not working from shell script

[Ron]

Does the Jenkins' $PATH variable have the Postgres binaries' location?

On 1/17/20 2:47 AM, ramesh penumalli wrote:

Dear Team,

I am using postgresql 9.2.12 version and I am facing  an issue while 
running the executing the commands createdb,dropdb,psql commands in the 
shell script which is running from Jenkins to restore prod data into stage 
environment.


Jenkins job is failing with the below output.I have verified the script 
and the scrit is fine and it seems to be an issue with the configuration 
of postgresql  and not sure where to make the changes.


line 48: dropdb: command not found
line 50: createdb: command not found
line 52: psql: command not found
Kindly guide me to rectify the issue.
Thanks and Regards,
Ramesh


--
Angular momentum makes the world go 'round.

Re: postgresql commands(psql,createdb,dropdb) are not working from shell script

[Ramesh Penuballi]

Dear Ron,

Thanks for looking into this query.

Could you please let me know how can I check the same.

We are able take our production backup  from the  Jenkins server, and the
issue is with our uat database server only.

Could you please let me know how to check the path on Jenkins server as I
am new to Jenkins.

Thanks and Regards
Ramesh Penuballi

On Fri, 17 Jan, 2020, 17:53 Ron,  wrote:

> Does the Jenkins' $PATH variable have the Postgres binaries' location?
>
> On 1/17/20 2:47 AM, ramesh penumalli wrote:
>
> Dear Team,
>
> I am using postgresql 9.2.12 version and I am facing  an issue while
> running the executing the commands createdb,dropdb,psql commands in the
> shell script which is running from Jenkins to restore prod data into stage
> environment.
>
> Jenkins job is failing with the below output.I have verified the script
> and the scrit is fine and it seems to be an issue with the configuration of
> postgresql  and not sure where to make the changes.
>
> line 48: dropdb: command not found
>
> line 50: createdb: command not found
>
> line 52: psql: command not found
>
> Kindly guide me to rectify the issue.
>
> Thanks and Regards,
>
> Ramesh
>
>
> --
> Angular momentum makes the world go 'round.
>

Re: postgresql commands(psql,createdb,dropdb) are not working from shell script

[Ron]

The Jenkins server needs to be able to find the Unix executables dropdb, 
createdb and psql.


I don't know *anything* about Jenkins, or even whether or not Jenkins runs 
on the same box as Postgres, but there's *some* shell script *somewhere* 
that points $PATH to the proper Postgres binaries.


On 1/17/20 6:56 AM, Ramesh Penuballi wrote:

Dear Ron,

Thanks for looking into this query.

Could you please let me know how can I check the same.

We are able take our production backup  from the  Jenkins server, and the 
issue is with our uat database server only.


Could you please let me know how to check the path on Jenkins server as I 
am new to Jenkins.


Thanks and Regards
Ramesh Penuballi

On Fri, 17 Jan, 2020, 17:53 Ron, > wrote:


Does the Jenkins' $PATH variable have the Postgres binaries' location?

On 1/17/20 2:47 AM, ramesh penumalli wrote:

Dear Team,

I am using postgresql 9.2.12 version and I am facing  an issue while
running the executing the commands createdb,dropdb,psql commands in
the shell script which is running from Jenkins to restore prod data
into stage environment.

Jenkins job is failing with the below output.I have verified the
script and the scrit is fine and it seems to be an issue with the
configuration of postgresql  and not sure where to make the changes.

line 48: dropdb: command not found
line 50: createdb: command not found
line 52: psql: command not found
Kindly guide me to rectify the issue.
Thanks and Regards,
Ramesh


-- 
Angular momentum makes the world go 'round.




--
Angular momentum makes the world go 'round.

Re: postgresql commands(psql,createdb,dropdb) are not working from shell script

[Ramesh Penuballi]

Thanks once again for sharing the info.
Let me check and get back to you.

Thanks and Regards
Ramesh Penuballi

On Fri, 17 Jan, 2020, 18:43 Ron,  wrote:

> The Jenkins server needs to be able to find the Unix executables dropdb,
> createdb and psql.
>
> I don't know *anything* about Jenkins, or even whether or not Jenkins
> runs on the same box as Postgres, but there's *some* shell script
> *somewhere* that points $PATH to the proper Postgres binaries.
>
> On 1/17/20 6:56 AM, Ramesh Penuballi wrote:
>
> Dear Ron,
>
> Thanks for looking into this query.
>
> Could you please let me know how can I check the same.
>
> We are able take our production backup  from the  Jenkins server, and the
> issue is with our uat database server only.
>
> Could you please let me know how to check the path on Jenkins server as I
> am new to Jenkins.
>
> Thanks and Regards
> Ramesh Penuballi
>
> On Fri, 17 Jan, 2020, 17:53 Ron,  wrote:
>
>> Does the Jenkins' $PATH variable have the Postgres binaries' location?
>>
>> On 1/17/20 2:47 AM, ramesh penumalli wrote:
>>
>> Dear Team,
>>
>> I am using postgresql 9.2.12 version and I am facing  an issue while
>> running the executing the commands createdb,dropdb,psql commands in the
>> shell script which is running from Jenkins to restore prod data into stage
>> environment.
>>
>> Jenkins job is failing with the below output.I have verified the script
>> and the scrit is fine and it seems to be an issue with the configuration of
>> postgresql  and not sure where to make the changes.
>>
>> line 48: dropdb: command not found
>>
>> line 50: createdb: command not found
>>
>> line 52: psql: command not found
>>
>> Kindly guide me to rectify the issue.
>>
>> Thanks and Regards,
>>
>> Ramesh
>>
>>
>> --
>> Angular momentum makes the world go 'round.
>>
>
> --
> Angular momentum makes the world go 'round.
>

Regarding automatic table partitioning without using trigger function in pgsql 12 is possible or not

[Durgamahesh Manne]

Hi
 To the respected PostgreSQL international team

Please let me know that automatic table partitioning without using trigger
function is possible in pgsql 12 or not ?



Regards

Durgamahesh Manne

Re: Regarding automatic table partitioning in PostgreSQL 12 version is possible or not

[Stephen Frost]

Greetings,

* Durgamahesh Manne (maheshpostgr...@gmail.com) wrote:
> Please let me know that automatic table partitioning is possible in pgsql
> 12 or not without using trigger function

The approach I'd recommend for automatically getting new table partitions
created, at least today, is to use pg_partman, which is a very handy
extension for working with partitions in PG.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Regarding automatic table partitioning in PostgreSQL 12 version is possible or not

[Durgamahesh Manne]

On Fri, Jan 17, 2020 at 7:43 PM Stephen Frost  wrote:

> Greetings,
>
> * Durgamahesh Manne (maheshpostgr...@gmail.com) wrote:
> > Please let me know that automatic table partitioning is possible in pgsql
> > 12 or not without using trigger function
>
> The approach I'd recommend for automatically getting new table partitions
> created, at least today, is to use pg_partman, which is a very handy
> extension for working with partitions in PG.
>
> Thanks,
>
> Stephen
>

Hi sir


Thank you for this valuable information

Re: minimal wal_level on subscriber

[Laurenz Albe]

On Fri, 2020-01-17 at 08:43 +0100, Arnaud L. wrote:
> > > is it OK to set wal_level to minimal on the subscriber side of the 
> > > logical replication ?
> > 
> > Yes, if you don't want physical backups.
> 
> You mean online physical backups ?
> I could still shutdown the subscriber and take an offline physical 
> backup, right ?

Yes, that is what I mean.

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

RPC via WebSockets.

[Dmitry Igrishin]

Hello!

Who needs to talk to Postgres right from a WEB-browser via WebSockets? For
example, conveniently call storable functions/procedures?

Yes, i know about PostgREST. But I want to focus on RPC via WebSockets
without all these REST or "give me contents of that table by this URL"
stuff...

I'm thinking about writing an application which is simple to
install/deploy, simple to configure, with a connection pool built-in.
Ideally, one binary and one config in JSON format.

I need some feedback before start. So please don't hesitate to share any of
your thoughts/ideas/comments/scepticism here :-)

Thanks.

Re: jsonb_set() strictness considered harmful to data

[Tomas Vondra]

On Wed, Jan 08, 2020 at 05:24:05PM +1030, Andrew Dunstan wrote:

On Wed, Jan 8, 2020 at 7:08 AM Pavel Stehule  wrote:


Hi

po 6. 1. 2020 v 22:34 odesílatel Andrew Dunstan 
 napsal:



Updated version including docco and better error message.

cheers

andrew



I think so my objections are solved. I have small objection

+ errdetail("exception raised due to \"null_value_treatment := 
'raise_exception'\""),
+ errhint("to avoid, either change the null_value_treatment argument or ensure that 
an SQL NULL is not used")));

"null_value_treatment := 'raise_exception'\""

it use proprietary PostgreSQL syntax for named parameters. Better to use 
ANSI/SQL syntax

"null_value_treatment => 'raise_exception'\""

It is fixed in attached patch

source compilation without warnings,
compilation docs without warnings
check-world passed without any problems

I'll mark this patch as ready for commiter

Thank you for your work




Thanks for the review. I propose to commit this shortly.



Now that this was committed, I've updated the patch status accordingly.

Thanks!

--
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services 

Re: jsonb_set() strictness considered harmful to data

[Ariadne Conill]

Hello,

January 17, 2020 5:21 PM, "Tomas Vondra"  wrote:

> On Wed, Jan 08, 2020 at 05:24:05PM +1030, Andrew Dunstan wrote:
> 
>> On Wed, Jan 8, 2020 at 7:08 AM Pavel Stehule  wrote:
>>> Hi
>>> 
>>> po 6. 1. 2020 v 22:34 odesílatel Andrew Dunstan 
>>>  napsal:
>> 
>> Updated version including docco and better error message.
>> 
>> cheers
>> 
>> andrew
>>> I think so my objections are solved. I have small objection
>>> 
>>> + errdetail("exception raised due to \"null_value_treatment := 
>>> 'raise_exception'\""),
>>> + errhint("to avoid, either change the null_value_treatment argument or 
>>> ensure that an SQL NULL is
>>> not used")));
>>> 
>>> "null_value_treatment := 'raise_exception'\""
>>> 
>>> it use proprietary PostgreSQL syntax for named parameters. Better to use 
>>> ANSI/SQL syntax
>>> 
>>> "null_value_treatment => 'raise_exception'\""
>>> 
>>> It is fixed in attached patch
>>> 
>>> source compilation without warnings,
>>> compilation docs without warnings
>>> check-world passed without any problems
>>> 
>>> I'll mark this patch as ready for commiter
>>> 
>>> Thank you for your work
>> 
>> Thanks for the review. I propose to commit this shortly.
> 
> Now that this was committed, I've updated the patch status accordingly.

Thank you very much for coming together and finding a solution to this bug!

Ariadne

Re: jsonb_set() strictness considered harmful to data

[Rob Sargent]

> On Jan 17, 2020, at 4:28 PM, Ariadne Conill  wrote:
> 
> Hello,
> 
> January 17, 2020 5:21 PM, "Tomas Vondra"  > wrote:
> 
> Thank you very much for coming together and finding a solution to this bug!
> 
> Ariadne
Let’s leave it at “issue” :)

Re: RPC via WebSockets.

[Nikolay Samokhvalov]

This is a very common task, definitely.

As for WebSockets, there is an addition to PostgREST, postgrest-websockets
https://github.com/diogob/postgres-websockets#readme

PostgREST is not only for "give me contents", you can write functions (in
any PL) and call them using POST /rpc/functionname.

There is also an attempt to build similar tool in Go (although I'm not sure
how popular and powerful it is): pREST https://github.com/prest/prest

Finally, there is a whole line of trendy and quite popular tools for having
GraphQL on top of Postgres, including but not limited to:
- PostGraphile https://github.com/graphile/postgraphile
- Hasura https://github.com/hasura/graphql-engine
- Prisma https://github.com/prisma/prisma

On Fri, Jan 17, 2020 at 13:18 Dmitry Igrishin  wrote:

> Hello!
>
> Who needs to talk to Postgres right from a WEB-browser via WebSockets? For
> example, conveniently call storable functions/procedures?
>
> Yes, i know about PostgREST. But I want to focus on RPC via WebSockets
> without all these REST or "give me contents of that table by this URL"
> stuff...
>
> I'm thinking about writing an application which is simple to
> install/deploy, simple to configure, with a connection pool built-in.
> Ideally, one binary and one config in JSON format.
>
> I need some feedback before start. So please don't hesitate to share any
> of your thoughts/ideas/comments/scepticism here :-)
>
> Thanks.
>
>

Re: RPC via WebSockets.

[Dmitry Igrishin]

On Sat, 18 Jan 2020, 05:54 Nikolay Samokhvalov, 
wrote:

> This is a very common task, definitely.
>
> As for WebSockets, there is an addition to PostgREST, postgrest-websockets
> https://github.com/diogob/postgres-websockets#readme
>
Yep, but as it's addition, it's not first class citizen and requires extra
steps to install it. While this is possible it's not user friendly. I would
like to fix this :-)


> PostgREST is not only for "give me contents", you can write functions (in
> any PL) and call them using POST /rpc/functionname.
>
This is what I want to emphasize on at first: calling functions, written in
any PL via JSON-RPC. There's also plans to execute any named queries
conveniently stored in text files, for example, foo.sql might contains:

-- @id my-query1
select :name::text

-- @id my-query2
select :age::integer

And then the caller can call these queries by ID, providing the parameter
values as simple as regular RPC call.


> There is also an attempt to build similar tool in Go (although I'm not
> sure how popular and powerful it is): pREST https://github.com/prest/prest
>
> Finally, there is a whole line of trendy and quite popular tools for
> having GraphQL on top of Postgres, including but not limited to:
> - PostGraphile https://github.com/graphile/postgraphile
> - Hasura https://github.com/hasura/graphql-engine
> - Prisma https://github.com/prisma/prisma
>
Thanks for the links!


> On Fri, Jan 17, 2020 at 13:18 Dmitry Igrishin  wrote:
>
>> Hello!
>>
>> Who needs to talk to Postgres right from a WEB-browser via WebSockets?
>> For example, conveniently call storable functions/procedures?
>>
>> Yes, i know about PostgREST. But I want to focus on RPC via WebSockets
>> without all these REST or "give me contents of that table by this URL"
>> stuff...
>>
>> I'm thinking about writing an application which is simple to
>> install/deploy, simple to configure, with a connection pool built-in.
>> Ideally, one binary and one config in JSON format.
>>
>> I need some feedback before start. So please don't hesitate to share any
>> of your thoughts/ideas/comments/scepticism here :-)
>>
>> Thanks.
>>
>>