Re: CREATEROLE users vs. role properties

[tushar]

On Mon, Jan 23, 2023 at 10:28 PM Robert Haas  wrote:

>
> In previous releases, you needed to have CREATEROLE in order to be
> able to perform user management functions. In master, you still need
> CREATEROLE, and you also need ADMIN OPTION on the role. In this
> scenario, only t1 meets those requirements with respect to t3, so only
> t1 can manage t3. t2 can SET ROLE to t3 and grant membership in t3,
> but it can't set role properties on t3 or change t3's password or
> things like that, because the ability to make user management changes
> is controlled by CREATEROLE.
>
ok.

>
> The patch is only intended to change behavior in the case where you
> possess both CREATEROLE and also ADMIN OPTION on the target role (but
> not SUPERUSER). In that scenario, it intends to change whether you can
> give or remove the CREATEDB, REPLICATION, and BYPASSRLS properties
> from a user.
>

right, Neha/I have tested with different scenarios using
createdb/replication/bypassrls and other
privileges properties on the role. also checked
pg_dumpall/pg_basebackup and everything looks fine.

regards,

Re: CREATEROLE users vs. role properties

[tushar]

On Thu, Jan 19, 2023 at 8:34 PM Robert Haas  wrote:

> On Thu, Jan 19, 2023 at 6:15 AM tushar 
> wrote:
> > postgres=# create role fff with createrole;
> > CREATE ROLE
> > postgres=# create role xxx;
> > CREATE ROLE
> > postgres=# set role fff;
> > SET
> > postgres=> alter role xxx with createrole;
> > ERROR:  permission denied
> > postgres=>
>
> Here fff would need ADMIN OPTION on xxx to be able to make modifications
> to it.
>
> See
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=cf5eb37c5ee0cc54c80d95c1695d7fca1f7c68cb


Thanks, Robert, that was helpful.

Please refer to this scenario where I am able to give createrole privileges
but not replication  privilege to role

postgres=# create role t1 createrole;
CREATE ROLE
postgres=# create role t2 replication;
CREATE ROLE
postgres=# create role t3;
CREATE ROLE
postgres=# grant t3 to t1,t2 with admin option;
GRANT ROLE
postgres=# set session authorization t1;
SET

*postgres=> alter role t3 createrole ;ALTER ROLE*
postgres=> set session authorization t2;
SET
postgres=> alter role t3 replication;
ERROR:  permission denied

This same behavior was observed in v14 as well but why i am able to give
createrole grant but not replication?

regards,

Re: almost-super-user problems that we haven't fixed yet

[tushar]

On 1/19/23 6:28 PM, tushar wrote:


There is  one typo , for the doc changes, it is  mentioned 
"pg_use_reserved_backends" but i think it supposed to be 
"pg_use_reserved_connections"

under Table 22.1. Predefined Roles.


Thanks, this is fixed now with the latest patches.

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: almost-super-user problems that we haven't fixed yet

[tushar]

On Thu, Jan 19, 2023 at 6:50 PM tushar 
wrote:

> and in the error message too
>
> [edb@centos7tushar bin]$ ./psql postgres -U r2
>
> psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed:
> FATAL:  remaining connection slots are reserved for roles with privileges
> of pg_use_reserved_backends
> [edb@centos7tushar bin]$
>


I think there is also a need to improve the error message if non
super users are not able to connect due to slot unavailability.
--Connect to psql terminal, create a user
create user t1;

--set these GUC parameters in postgresql.conf and restart the server

max_connections = 3 # (change requires restart)

superuser_reserved_connections = 1  # (change requires restart)

reserved_connections = 1

psql terminal ( connect to superuser),  ./psql postgres
psql terminal (try to connect to user t1) ,  ./psql postgres -U t1
Error message is

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed:
FATAL:  remaining connection slots are reserved for roles with privileges
of pg_use_reserved_backends



that is not true because the superuser can still able to connect,

probably in this case message should be like this -

"remaining connection slots are reserved for roles with privileges of
pg_use_reserved_connections and for superusers" or something better.

regards,

Re: almost-super-user problems that we haven't fixed yet

[tushar]

On Thu, Jan 19, 2023 at 6:28 PM tushar 
wrote:

> On 1/19/23 2:44 AM, Nathan Bossart wrote:
> > On Wed, Jan 18, 2023 at 02:51:38PM -0500, Robert Haas wrote:
> >> Should (nfree < SuperuserReservedBackends) be using <=, or am I
> confused?
> > I believe < is correct.  At this point, the new backend will have already
> > claimed a proc struct, so if the number of remaining free slots equals
> the
> > number of reserved slots, it is okay.
> >
> >> What's the deal with removing "and no new replication connections will
> >> be accepted" from the documentation? Is the existing documentation
> >> just wrong? If so, should we fix that first? And maybe delete
> >> "non-replication" from the error message that says "remaining
> >> connection slots are reserved for non-replication superuser
> >> connections"? It seems like right now the comments say that
> >> replication connections are a completely separate pool of connections,
> >> but the documentation and the error message make it sound otherwise.
> >> If that's true, then one of them is wrong, and I think it's the
> >> docs/error message. Or am I just misreading it?
> > I think you are right.  This seems to have been missed in ea92368.  I
> moved
> > this part to a new patch that should probably be back-patched to v12.
> >
> > On that note, I wonder if it's worth changing the "sorry, too many
> clients
> > already" message to make it clear that max_connections has been reached.
> > IME some users are confused by this error, and I think it would be less
> > confusing if it pointed to the parameter that governs the number of
> > connection slots.  I'll create a new thread for this.
> >
> There is  one typo , for the doc changes, it is  mentioned
> "pg_use_reserved_backends" but i think it supposed to be
> "pg_use_reserved_connections"
> under Table 22.1. Predefined Roles.
>
> and in the error message too

[edb@centos7tushar bin]$ ./psql postgres -U r2

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed:
FATAL:  remaining connection slots are reserved for roles with privileges
of pg_use_reserved_backends
[edb@centos7tushar bin]$

regards,

Re: almost-super-user problems that we haven't fixed yet

[tushar]

On 1/19/23 2:44 AM, Nathan Bossart wrote:

On Wed, Jan 18, 2023 at 02:51:38PM -0500, Robert Haas wrote:

Should (nfree < SuperuserReservedBackends) be using <=, or am I confused?

I believe < is correct.  At this point, the new backend will have already
claimed a proc struct, so if the number of remaining free slots equals the
number of reserved slots, it is okay.


What's the deal with removing "and no new replication connections will
be accepted" from the documentation? Is the existing documentation
just wrong? If so, should we fix that first? And maybe delete
"non-replication" from the error message that says "remaining
connection slots are reserved for non-replication superuser
connections"? It seems like right now the comments say that
replication connections are a completely separate pool of connections,
but the documentation and the error message make it sound otherwise.
If that's true, then one of them is wrong, and I think it's the
docs/error message. Or am I just misreading it?

I think you are right.  This seems to have been missed in ea92368.  I moved
this part to a new patch that should probably be back-patched to v12.

On that note, I wonder if it's worth changing the "sorry, too many clients
already" message to make it clear that max_connections has been reached.
IME some users are confused by this error, and I think it would be less
confusing if it pointed to the parameter that governs the number of
connection slots.  I'll create a new thread for this.

There is  one typo , for the doc changes, it is  mentioned 
"pg_use_reserved_backends" but i think it supposed to be 
"pg_use_reserved_connections"

under Table 22.1. Predefined Roles.

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: CREATEROLE users vs. role properties

[tushar]

On 1/19/23 3:05 PM, tushar wrote:
which was working previously without patch. 

My bad, I was testing against PG v15 but this issue is not
reproducible on master (without patch).

As you mentioned- "This implements the standard idea that you can't give 
permissions
you don't have (but you can give the ones you do have)" but here the 
role is having
createrole  privilege that he cannot pass on to another user? Is this 
expected?


postgres=# create role fff with createrole;
CREATE ROLE
postgres=# create role xxx;
CREATE ROLE
postgres=# set role fff;
SET
postgres=> alter role xxx with createrole;
ERROR:  permission denied
postgres=>

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: CREATEROLE users vs. role properties

[tushar]

On 1/19/23 4:47 AM, Nathan Bossart wrote:

This seems like a clear improvement to me.  However, as the attribute
system becomes more sophisticated, I think we ought to improve the error
messages in user.c.  IMHO messages like "permission denied" could be
greatly improved with some added context.
I observed this behavior where the role is having creatrole but still 
it's unable to pass it to another user.


postgres=# create role abc1 login createrole;
CREATE ROLE
postgres=# create user test1;
CREATE ROLE
postgres=# \c - abc1
You are now connected to database "postgres" as user "abc1".
postgres=> alter role test1 with createrole ;
ERROR:  permission denied
postgres=>

which was working previously without patch.

Is this an expected behavior?

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: Getting an error if we provide --enable-tap-tests switch on SLES 12

[tushar]

On 1/5/23 2:40 AM, Andres Freund wrote:


Have you actually tested running the tests with the patch applied?

Yes but getting an errors like
t/006_edb_current_audit_logfile.pl .. Can't locate IPC/Run.pm in @INC 
(you may need to install the IPC::Run module) (@INC contains: 
/home/runner/edbas/src/bin/pg_ctl/../../../src/test/perl 
/home/runner/edbas/src/bin/pg_ctl



Do we have any better option to work without this workaround?

You could install the module via cpan :/.



Yes, will try to install.

Thanks Andres.

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Getting an error if we provide --enable-tap-tests switch on SLES 12

[tushar]

Hi,

We found that if we provide *--enable-tap-tests * switch at the time of PG
sources configuration,  getting this below error
"
checking for Perl modules required for TAP tests... Can't locate IPC/Run.pm
in @INC (you may need to install the IPC::Run module) (@INC contains:
/usr/lib/perl5/site_perl/5.18.2/x86_64-linux-thread-multi
/usr/lib/perl5/site_perl/5.18.2
/usr/lib/perl5/vendor_perl/5.18.2/x86_64-linux-thread-multi
/usr/lib/perl5/vendor_perl/5.18.2
/usr/lib/perl5/5.18.2/x86_64-linux-thread-multi /usr/lib/perl5/5.18.2
/usr/lib/perl5/site_perl .) at ./config/check_modules.pl line 11.

BEGIN failed--compilation aborted at ./config/check_modules.pl line 11.

configure: error: Additional Perl modules are required to run TAP tests
"

look like this is happening because the Perl-IPC-Run package is not
available on SLES 12 where Perl-IPC-Run3 is available.

Srinu (my teammate) found that  IPC::Run is hard coded in config/
check_modules.pl and if we replace Run to Run3 it works (patch is attached,
created by Srinu)

Do we have any better option to work without this workaround?

regards,


perl.patch
Description: Binary data

Re: replacing role-level NOINHERIT with a grant-level option

[tushar]

On 8/24/22 12:28 AM, Robert Haas wrote:

This patch needed to be rebased pretty extensively after commit
ce6b672e4455820a0348214be0da1a024c3f619f. Here is a new version.

Thanks, Robert, I have retested this patch with my previous scenarios
and things look good to me.

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: replacing role-level NOINHERIT with a grant-level option

[tushar]

On 7/28/22 8:03 PM, Robert Haas wrote:

No, it seems to me that's behaving as intended. REVOKE BLAH OPTION ...
is intended to be a way of switching an option off.
Ok, Thanks, Robert. I tested with a couple of more scenarios like 
pg_upgrade/pg_dumpall /grant/revoke .. with admin option/inherit

and things look good to me.

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: replacing role-level NOINHERIT with a grant-level option

[tushar]

On 7/19/22 12:56 AM, Robert Haas wrote:

Another good catch. Here is v5 with a fix for that problem.
Here is one scenario in which I have NOT granted (inherit false) 
explicitly but still revoke

command is changing the current state

postgres=# create group foo;
CREATE ROLE
postgres=# create user bar in group foo;
CREATE ROLE
postgres=# revoke inherit option for foo from bar;
REVOKE ROLE

[edb@centos7tushar bin]$ ./pg_dumpall > /tmp/a11

[edb@centos7tushar bin]$ cat /tmp/a11 |grep 'inherit false' -i
GRANT foo TO bar WITH INHERIT FALSE GRANTED BY edb;

I think this revoke command should be ignored and inherit option should 
remain 'TRUE'

as it was before?

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: replacing role-level NOINHERIT with a grant-level option

[tushar]

On 7/19/22 12:56 AM, Robert Haas wrote:

Another good catch. Here is v5 with a fix for that problem.

Thanks, the issue is fixed now.

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: replacing role-level NOINHERIT with a grant-level option

[tushar]

On 7/11/22 11:01 PM, Robert Haas wrote:

Oops. Here is a rebased version of v3 which aims to fix this bug.

I found one issue where pg_upgrade is failing

PG v14.4 , create these below objects

create user u1 with superuser;
create user u3;
create group g2 with user  u1;

now try to perform pg_upgrade from v16(w/patch), it is failing with 
these messages


[edb@centos7tushar bin]$ tail -10 
dc2/pg_upgrade_output.d/20220714T195919.494/log/pg_upgrade_utility.log

--
--
CREATE ROLE "u3";
CREATE ROLE
ALTER ROLE "u3" WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN 
NOREPLICATION NOBYPASSRLS;

ALTER ROLE
GRANT "g2" TO "u1" WITH  GRANTED BY "edb";
psql:dc2/pg_upgrade_output.d/20220714T195919.494/dump/pg_upgrade_dump_globals.sql:47: 
ERROR:  syntax error at or near "BY"

LINE 1: GRANT "g2" TO "u1" WITH  GRANTED BY "edb";
         ^
This issue is not reproducible on PG v16 (without patch).

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: replacing role-level NOINHERIT with a grant-level option

[tushar]

On 7/11/22 11:01 PM, Robert Haas wrote:

Oops. Here is a rebased version of v3 which aims to fix this bug.

Thanks, Issue seems to be fixed with this patch.

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: replacing role-level NOINHERIT with a grant-level option

[tushar]

On Sat, Jul 9, 2022 at 1:27 AM Robert Haas  wrote:

> On Tue, Jul 5, 2022 at 8:04 AM Robert Haas  wrote:
> > On Sun, Jul 3, 2022 at 1:17 PM Nathan Bossart 
> wrote:
> > > If by "bolder" you mean "mark [NO]INHERIT as
> deprecated-and-to-be-removed
> > > and begin emitting WARNINGs when it and WITH INHERIT DEFAULT are
> used," I
> > > think it's worth consideration.  I suspect it will be hard to sell
> removing
> > > [NO]INHERIT in v16 because it would introduce a compatibility break
> without
> > > giving users much time to migrate.  I could be wrong, though.
> >
> > It's a fair point. But, if our goal for v16 is to do something that
> > could lead to an eventual deprecation of [NO]INHERIT, I still think
> > removing WITH INHERIT DEFAULT from the patch set is probably a good
> > idea.
>
> So here is an updated patch with that change.
>
>
Thanks, Robert, I created a few objects with different privileges on v14.4
e.g


postgres=# \dp+ atest2

   Access privileges

 Schema |  Name  | Type  |   Access privileges   |
Column privileges | Policies

++---+---+---+--

 public | atest2 | table | regress_priv_user1=arwdDxt/regress_priv_user1+|
|

||   | regress_priv_user2=r/regress_priv_user1  +|
|

||   | regress_priv_user3=w/regress_priv_user1  +|
|

||   | regress_priv_user4=a/regress_priv_user1  +|
|

||   | regress_priv_user5=D/regress_priv_user1   |
|

(1 row)




and found that after pg_upgrade there is no change on privileges  on
v16(w/patch)


One scenario where the syntax is created in pg_dumpall is wrong


postgres=# create user u1;

CREATE ROLE

postgres=# create group g1 with user u1;

CREATE ROLE

postgres=# grant g1 to u1 with admin option, inherit false;

GRANT ROLE

postgres=#


Perform pg_dumpall


This is the syntax coming


"


-- Role memberships

--


GRANT g1 TO u1 WITH ADMIN OPTION WITH INHERIT FALSE GRANTED BY edb;


"


If we run this syntax on psql, there is an error.


postgres=# GRANT g1 TO u1 WITH ADMIN OPTION WITH INHERIT FALSE GRANTED BY
edb;

ERROR:  syntax error at or near "WITH"



regards,

[v15 beta] pg_upgrade failed if earlier executed with -c switch

[tushar]

Hi,

While performing pg_upgrade from v15Beta binaries/source,
I got this error below error

could not create directory "d2/pg_upgrade_output.d": File exists
Failure, exiting


*Steps to reproduce *
v15 Beta sources
initalize a cluster ( ./initdb -D d1)
initalize another cluster ( ./initdb -D d2)
run pg_upgrade with -c option  ( ./pg_upgrade -d d1 -D d2 -b . -B . -c -v)
run pg_upgrade without -c option ( ./pg_upgrade -d d1 -D d2 -b . -B .)
--
--
--
Error


This behavior was not there in earlier released versions, i guess.
Is it expected behavior now onwards?

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: refactoring basebackup.c

[tushar]

On 2/15/22 6:48 PM, Jeevan Ladhe wrote:

Please find the attached updated version of patch for ZSTD server side
Thanks, Jeevan, I again tested with the attached patch, and as mentioned 
the crash is fixed now.


also, I tested with different labels with gzip V/s zstd against data 
directory size which is 29GB and found these results



./pg_basebackup  -t server:/tmp/ 
--compress=server-zstd:  -Xnone -n -N --no-estimate-size -v


--compress=server-zstd:1 =  compress directory size is  1.3GB
--compress=server-zstd:4 = compress  directory size is  1.3GB
--compress=server-zstd:7 = compress  directory size is  1.2GB
--compress=server-zstd:12 = compress directory size is 1.2GB


===
./pg_basebackup  -t server:/tmp/ 
--compress=server-gzip:  -Xnone -n -N --no-estimate-size -v


--compress=server-gzip:1 =  compress directory size is  1.8GB
--compress=server-gzip:4 = compress  directory size is  1.6GB
--compress=server-gzip:9 = compress  directory size is  1.6GB
===

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: refactoring basebackup.c

[tushar]

On 1/27/22 11:12 PM, Robert Haas wrote:

Well what's weird here is that you are using both --gzip and also
--compress. Those both control the same behavior, so it's a surprising
idea to specify both. But I guess if someone does, we should make the
second one fully override the first one. Here's a patch to try to do
that.

right, the current behavior was  -

[edb@centos7tushar bin]$ ./pg_basebackup  -t server:/tmp/y101 --gzip -Z 
none  -Xnone

pg_basebackup: error: cannot use compression level with method none
Try "pg_basebackup --help" for more information.

and even this was not matching with PG v14 behavior too
e.g
 ./pg_basebackup -Ft -z -Z none  -D /tmp/test1  ( working in PG v14 but 
throwing above error on PG HEAD)


and somewhere we were breaking the backward compatibility.

now with your patch -this seems working fine

[edb@centos7tushar bin]$ ./pg_basebackup  -t server:/tmp/y101 --gzip*-Z 
none*  -Xnone
NOTICE:  WAL archiving is not enabled; you must ensure that all required 
WAL segments are copied through other means to complete the backup

[edb@centos7tushar bin]$ ls /tmp/y101
backup_manifest *base.tar*

OR

[edb@centos7tushar bin]$  ./pg_basebackup  -t server:/tmp/y0p -Z none  
-Xfetch *-z*

[edb@centos7tushar bin]$ ls /tmp/y0p
backup_manifest *base.tar.gz*

but what about server-gzip:0? should it allow compressing the directory?

[edb@centos7tushar bin]$  ./pg_basebackup  -t server:/tmp/1 
--compress=server-gzip:0  -Xfetch

[edb@centos7tushar bin]$ ls /tmp/1
backup_manifest  base.tar.gz

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: refactoring basebackup.c

[tushar]

On 1/27/22 10:17 PM, Robert Haas wrote:

Cool. I committed that patch.
Thanks , Please refer to this scenario  where the label is set to  0 for 
server-gzip but the directory is still  compressed


[edb@centos7tushar bin]$ ./pg_basebackup -t server:/tmp/11 --gzip 
--compress=0 -Xnone

NOTICE:  all required WAL segments have been archived
[edb@centos7tushar bin]$ ls /tmp/11
16384.tar  backup_manifest  base.tar


[edb@centos7tushar bin]$ ./pg_basebackup -t server:/tmp/10 --gzip 
--compress=server-gzip:0 -Xnone

NOTICE:  all required WAL segments have been archived
[edb@centos7tushar bin]$ ls /tmp/10
16384.tar.gz  backup_manifest  base.tar.gz

0 is for no compression so the directory should not be compressed if we 
mention server-gzip:0 and both these

above scenarios should match?

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: refactoring basebackup.c

[tushar]

On 1/27/22 2:15 AM, Robert Haas wrote:

The attached patch should fix this, too.

Thanks, the issues seem to be fixed now.

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: refactoring basebackup.c

[tushar]

On 1/22/22 12:03 AM, Robert Haas wrote:

I committed the base backup target patch yesterday, and today I
updated the remaining code in light of Michael Paquier's commit
5c649fe153367cdab278738ee4aebbfd158e0546. Here is the resulting patch.

Thanks Robert,  I tested against the latest PG Head and found a few issues -

A)Getting syntax error if -z is used along with -t

[edb@centos7tushar bin]$ ./pg_basebackup -t server:/tmp/data902 -z -Xfetch
pg_basebackup: error: could not initiate base backup: ERROR:  syntax error

OR

[edb@centos7tushar bin]$ ./pg_basebackup -t server:/tmp/t2 
--compress=server-gzip:9 -Xfetch -v -z

pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: error: could not initiate base backup: ERROR:  syntax error

B)No information of "client-gzip" or "server-gzip" added under 
"--compress" option/method of ./pg_basebackup --help.


C) -R option is silently ignoring

[edb@centos7tushar bin]$  ./pg_basebackup  -Z 4  -v  -t server:/tmp/pp 
-Xfetch -R

pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/3028 on timeline 1
pg_basebackup: write-ahead log end point: 0/3100
pg_basebackup: base backup completed
[edb@centos7tushar bin]$

go to /tmp/pp folder and extract it - there is no "standby.signal" file 
and if we start cluster against this data directory,it will not be in 
slave mode.


if this is not supported then I think we should throw some errors.

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: refactoring basebackup.c

[tushar]

] LOG:  checkpoint complete: wrote 0 
buffers (0.0%); 0 WAL file(s) added, 1 removed, 0 recycled; write=0.001 
s, sync=0.001 s, total=0.046 s; sync files=0, longest=0.000 s, 
average=0.000 s; distance=16383 kB, estimate=16383 kB

pg_basebackup: checkpoint completed

NOTICE:  WAL archiving is not enabled; you must ensure that all required 
WAL segments are copied through other means to complete the backup

pg_basebackup: base backup completed
[edb@centos7tushar bin]$

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: refactoring basebackup.c

[tushar]

On Tue, Dec 28, 2021 at 1:12 PM Jeevan Ladhe 
wrote:

> Hi Tushar,
>
> You need to apply Robert's v10 version patches 0002, 0003 and 0004, before
> applying the lz4 patch(v8 version).
> Please let me know if you still face any issues.
>

Thanks, Jeevan.
I tested —server-compression option using different other options of
pg_basebackup, also checked -t/—server-compression from pg_basebackup of
v15 will
throw an error if the server version is v14 or below. Things are looking
good to me.
Two open  issues -
1)lz4 value is missing for --server-compression in pg_basebackup --help
2)Error messages need to improve if using -t server with -z/-Z

regards,

Re: refactoring basebackup.c

[tushar]

On 1/4/22 8:07 PM, Robert Haas wrote:

Before sending an email like this, it would be a good idea to read the
documentation for the --server-compression option.

Sure, Thanks Robert.

One scenario where I feel error message is confusing and if it is not 
supported at all then error message need to be a little bit more clear


if we use -z  (or -Z ) with -t , we are getting this error
[edb@centos7tushar bin]$  ./pg_basebackup -t server:/tmp/test0 -Xfetch -z
pg_basebackup: error: only tar mode backups can be compressed
Try "pg_basebackup --help" for more information.

but after removing -z option  backup is in tar mode only

edb@centos7tushar bin]$  ./pg_basebackup -t server:/tmp/test0 -Xfetch
[edb@centos7tushar bin]$ ls /tmp/test0
backup_manifest  base.tar

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: refactoring basebackup.c

[tushar]

On 11/22/21 11:05 PM, Jeevan Ladhe wrote:

Please find the lz4 compression patch here that basically has:
Please refer to this  scenario , where --server-compression is only 
compressing

base backup into lz4 format but not pg_wal directory

[edb@centos7tushar bin]$ ./pg_basebackup -Ft --server-compression=lz4 
-Xstream -D foo


[edb@centos7tushar bin]$ ls foo
backup_manifest  base.tar.lz4  pg_wal.tar

this same is valid for gzip as well if server-compression is set to gzip

edb@centos7tushar bin]$ ./pg_basebackup -Ft --server-compression=gzip4 
-Xstream -D foo1


[edb@centos7tushar bin]$ ls foo1
backup_manifest  base.tar.gz  pg_wal.tar

if this scenario is valid then both the folders format should be in lz4 
format otherwise we should

get an error something like - not a valid option ?

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: refactoring basebackup.c

[tushar]

On 11/22/21 11:05 PM, Jeevan Ladhe wrote:

Please find the lz4 compression patch here that basically has:

One small issue, in the "pg_basebackup --help", we are not displaying
lz4 value under --server-compression option


[edb@tusharcentos7-v14 bin]$ ./pg_basebackup --help | grep 
server-compression

  --server-compression=none|gzip|gzip[1-9]

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: refactoring basebackup.c

[tushar]

On 12/28/21 1:11 PM, Jeevan Ladhe wrote:
You need to apply Robert's v10 version patches 0002, 0003 and 0004, 
before applying the lz4 patch(v8 version).

Thanks, able to apply now.

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: refactoring basebackup.c

[tushar]

On 11/22/21 11:05 PM, Jeevan Ladhe wrote:

Please find the lz4 compression patch here that basically has:

Thanks, Could you please rebase your patch, it is failing at my end -

[edb@centos7tushar pg15_lz]$ git apply /tmp/v8-0001-LZ4-compression.patch
error: patch failed: doc/src/sgml/ref/pg_basebackup.sgml:230
error: doc/src/sgml/ref/pg_basebackup.sgml: patch does not apply
error: patch failed: src/backend/replication/Makefile:19
error: src/backend/replication/Makefile: patch does not apply
error: patch failed: src/backend/replication/basebackup.c:64
error: src/backend/replication/basebackup.c: patch does not apply
error: patch failed: src/include/replication/basebackup_sink.h:285
error: src/include/replication/basebackup_sink.h: patch does not apply

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: preserving db/ts/relfilenode OIDs across pg_upgrade (was Re: storing an explicit nonce)

[tushar]

On 12/15/21 12:09 AM, tushar wrote:

I spent much of today reviewing 0001. Here's an updated version, so
far only lightly tested. Please check whether I've broken anything.

Thanks Robert, I tested from v96/12/13/v14 -> v15( with patch)
things are working fine i.e table /index relfilenode is preserved,
not changing after pg_upgrade. 
I covered tablespace OIDs testing scenarios and that is also preserved 
after pg_upgrade.


--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: preserving db/ts/relfilenode OIDs across pg_upgrade (was Re: storing an explicit nonce)

[tushar]

On 12/14/21 2:35 AM, Robert Haas wrote:

I spent much of today reviewing 0001. Here's an updated version, so
far only lightly tested. Please check whether I've broken anything.

Thanks Robert, I tested from v96/12/13/v14 -> v15( with patch)
things are working fine i.e table /index relfilenode is preserved,
not changing after pg_upgrade.

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

pg_upgrade operation failed if table created in --single user mode

[tushar]

Hi,

Please refer to this scenario where pg_upgrade operation is failing if 
the table is create in single-user mode.


PG v13
--connect to PG v13 using single user mode  ( ./postgres --single -D 
/tmp/data13 postgres )

--create table ( backend> create table r(n int); )
--exit  ( ctrl + D)

-- Perform pg_upgrade ( PG v13->PG v15)  )(./pg_upgrade -d data13 -D 
data15 -b /usr/psql-12/bin -B . )


it will fail with these messages

Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
  postgres
*failure*

Consult the last few lines of "pg_upgrade_dump_14174.log" for
the probable cause of the failure.
Failure, exiting

--cat pg_upgrade_dump_14174.log
--
--
--
--
pg_restore: creating TABLE "public.r"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 200; 1259 14180 TABLE r edb
pg_restore: error: could not execute query: ERROR:  pg_type array OID 
value not set when in binary upgrade mode

Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT 
pg_catalog.binary_upgrade_set_next_pg_type_oid('14181'::pg_catalog.oid);



-- For binary upgrade, must preserve pg_class oids and relfilenodes
SELECT 
pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('14180'::pg_catalog.oid);
SELECT 
pg_catalog.binary_upgrade_set_next_heap_relfilenode('14180'::pg_catalog.oid);


CREATE TABLE "public"."r" (
    "n" integer
);

-- For binary upgrade, set heap's relfrozenxid and relminmxid
UPDATE pg_catalog.pg_class
SET relfrozenxid = '492', relminmxid = '1'
WHERE oid = '"public"."r"'::pg_catalog.regclass;

Is it expected ?

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: extensible options syntax for replication parser?

[tushar]

On 10/5/21 10:26 PM, Robert Haas wrote:

Hearing no further comments, I've gone ahead and committed these
patches. I'm still slightly nervous that I may have missed some issue,
but I think at this point having the patches in the tree is more
likely to turn it up than any other course of action.
I have tested couple of scenarios of pg_basebackup / pg_receivewal 
/pg_recvlogical /  Publication(wal_level=logical) and
Subscription e.t.c against HEAD (with patches) and  cross-version 
testing. Things look good to me and no breakage was found.


--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: extensible options syntax for replication parser?

[tushar]

On 9/27/21 9:29 AM, Ajin Cherian wrote:

And in case you do see a problem, I request you create a seperate
thread for this. I didn't want to derail this patch.


It would be great if we throw an error rather than silently ignoring 
this parameter ,  I opened a separate email for this


https://www.postgresql.org/message-id/CAC6VRoY3SAFeO7kZ0EOVC6mX%3D1ZyTocaecTDTh209W20KCC_aQ%40mail.gmail.com

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: extensible options syntax for replication parser?

[tushar]

On 9/25/21 12:08 AM, Robert Haas wrote:

two_phase is new in v15, something you could also find out by checking
the documentation. Now if the patch changes the way two_phase
interacts with older versions, that's a bug in the patch and we should
fix it. But if the same issue exists without the patch then I'm not
sure why you are raising it here rather than on the thread where that
feature was developed.


Right, issue is reproducible on HEAD as well. I should have checked 
that, sorry about it.


--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

two_phase commit parameter used in subscription for a publication which is on < 15.

[tushar]

Hi,

Please refer to this scenario
CASE 1- HEAD (publication)-> HEAD (Subscription)
CASE 2 - PG 14 (Publication) ->HEAD (Subscription)

Test-case -
Publication  = create table t(n int); create publication p for table t;
Subscription = create table t(n int);
create subscription  s connection 'dbname=postgres host=localhost '
PUBLICATION p WITH (two_phase=1);

Result-
CASE 1-
postgres=# select two_phase from pg_replication_slots where slot_name='s';
 two_phase
---
 t
(1 row)


CASE 2 -
postgres=# select two_phase from pg_replication_slots where slot_name='s';
 two_phase
---
 f
(1 row)

so are we silently ignoring this parameter as it is not supported on v14 ?
and if yes then why not we just throw a message like
ERROR:  unrecognized subscription parameter: "two_phase"

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: extensible options syntax for replication parser?

[tushar]

On 9/24/21 11:57 PM, tushar wrote:
postgres=# select two_phase from pg_replication_slots where 
slot_name='r105'; 


Correction -Please read  'r105' to 'r1015'

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: extensible options syntax for replication parser?

[tushar]

On 9/24/21 10:36 PM, Robert Haas wrote:

Here's v9, fixing the issue reported by Fujii Masao.


Please refer this scenario where publication on v14RC1  and subscription 
on HEAD (w/patch)


--create a subscription with parameter two_phase=1 on HEAD

postgres=# CREATE SUBSCRIPTION r1015 CONNECTION 'dbname=postgres 
host=localhost port=5454' PUBLICATION p WITH (two_phase=1);

NOTICE:  created replication slot "r1015" on publisher
CREATE SUBSCRIPTION
postgres=#

--check on 14RC1

postgres=# select two_phase from pg_replication_slots where 
slot_name='r105';

 two_phase
---
 f
(1 row)

so are we silently ignoring this parameter as it is not supported on 
v14RC/HEAD ? and if yes then why not we just throw an message like

ERROR:  unrecognized subscription parameter: "two_phase"

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: extensible options syntax for replication parser?

[tushar]

On 9/24/21 10:36 PM, Robert Haas wrote:

I am not able to reproduce this failure. I suspect you made a mistake
in testing, because my test case before sending the patch was
basically the same as yours, except that I was testing with v13. But I
tried again with v12 and it seems fine:


Right, on a clean setup -I am not also not able to reproduce this issue. 
Thanks for checking at your end.


--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: extensible options syntax for replication parser?

[tushar]

On 9/23/21 8:35 PM, Robert Haas wrote:

Thanks. Looks like that version had some stupid mistakes. Here's a new one.


Thanks, the reported issue seems to be fixed now for HEAD w/patch 
(publication) to HEAD w/patch (subscription) but still getting the same 
error if we try to perform v12(publication) to HEAD 
w/patch(subscription) . I checked there is no such issue for 
v12(publication) to v14 RC1 (subscription)


postgres=#  create subscription sub123s CONNECTION 'host=127.0.0.1 
user=edb  port= dbname=postgres' PUBLICATION pp with (slot_name = 
from_v14);

ERROR:  could not create replication slot "from_v14": ERROR: syntax error
postgres=#

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: extensible options syntax for replication parser?

[tushar]

On 9/23/21 1:25 AM, Robert Haas wrote:

postgres=# create subscription test CONNECTION 'host=127.0.0.1 user=postgres' 
PUBLICATION test with (create_slot = true);
ERROR:  could not create replication slot "test": ERROR:  syntax error

Thanks. I have attempted to fix these problems in the attached version.


l checked and look like the issue is still not fixed against v7-* patches -

postgres=#  create subscription test CONNECTION 'host=127.0.0.1 user=edb 
dbname=postgres' PUBLICATION p with (create_slot = true);

ERROR:  could not create replication slot "test": ERROR:  syntax error

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: refactoring basebackup.c

[tushar]

On 7/19/21 8:29 PM, Dilip Kumar wrote:

I am not sure why this is working, from the code I could not find if
the backup target is server then are we doing anything with the -R
option or we are just silently ignoring it


OK, in an  another scenario  I can see , "-t server" working with 
"--server-compression" option  but not with -z  or -Z ?


"-t  server" with option "-z"  / or (-Z )

[tushar@localhost bin]$ ./pg_basebackup -t server:/tmp/dataN -Xnone  -z  
--no-manifest -p 9033

pg_basebackup: error: only tar mode backups can be compressed
Try "pg_basebackup --help" for more information.

tushar@localhost bin]$ ./pg_basebackup -t server:/tmp/dataNa -Z 1    
-Xnone  --server-compression=gzip4  --no-manifest -p 9033

pg_basebackup: error: only tar mode backups can be compressed
Try "pg_basebackup --help" for more information.

"-t server" with "server-compression"  (working)

[tushar@localhost bin]$ ./pg_basebackup -t server:/tmp/dataN -Xnone  
--server-compression=gzip4  --no-manifest -p 9033
NOTICE:  WAL archiving is not enabled; you must ensure that all required 
WAL segments are copied through other means to complete the backup

[tushar@localhost bin]$

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: refactoring basebackup.c

[tushar]

On 7/16/21 12:43 PM, Dilip Kumar wrote:

I think the problem is that bbsink_gzip_end_archive() is not
forwarding the end request to the next bbsink.  The attached patch so
fix it.


Thanks Dilip. Reported issue seems to be fixed now with your patch

[edb@centos7tushar bin]$ ./pg_basebackup --server-compression=gzip4  -t 
server:/tmp/data_2 -v  -Xnone -R

pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
NOTICE:  all required WAL segments have been archived
pg_basebackup: base backup completed
[edb@centos7tushar bin]$

OR

[edb@centos7tushar bin]$ ./pg_basebackup   -t server:/tmp/pv1 -Xnone   
--server-compression=gzip4 -r 1024  -P

NOTICE:  all required WAL segments have been archived
23133/23133 kB (100%), 1/1 tablespace
[edb@centos7tushar bin]$

Please refer this scenario ,where -R option is working with '-t server' 
but not with -Ft


--not working

[edb@centos7tushar bin]$ ./pg_basebackup --server-compression=gzip4  
-Ft  -D ccv   -Xnone  -R --no-manifest

pg_basebackup: error: unable to parse archive: base.tar.gz
pg_basebackup: only tar archives can be parsed
pg_basebackup: the -R option requires pg_basebackup to parse the archive
pg_basebackup: removing data directory "ccv"

--working

[edb@centos7tushar bin]$ ./pg_basebackup --server-compression=gzip4 -t   
server:/tmp/ccv    -Xnone  -R --no-manifest

NOTICE:  all required WAL segments have been archived
[edb@centos7tushar bin]$

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: refactoring basebackup.c

[tushar]

On 7/8/21 9:26 PM, Robert Haas wrote:

Here at last is a new version.
if i try to perform pg_basebackup using "-t server " option against 
localhost V/S remote machine ,

i can see difference in backup size.

data directory whose size is

[edb@centos7tushar bin]$ du -sch data/
578M    data/
578M    total

-h=localhost

[edb@centos7tushar bin]$ ./pg_basebackup -t server:/tmp/all_data2*-h 
localhost*   -Xnone --no-manifest -P -v

pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
NOTICE:  all required WAL segments have been archived
329595/329595 kB (100%), 1/1 tablespace
pg_basebackup: base backup completed

[edb@centos7tushar bin]$ du -sch /tmp/all_data2
322M    /tmp/all_data2
322M    total
[edb@centos7tushar bin]$

-h=remote

[edb@centos7tushar bin]$ ./pg_basebackup -t server:/tmp/all_data2 *-h 
* -Xnone --no-manifest -P -v

pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
NOTICE:  all required WAL segments have been archived
170437/170437 kB (100%), 1/1 tablespace
pg_basebackup: base backup completed

[edb@0 bin]$ du -sch /tmp/all_data2
167M    /tmp/all_data2
167M    total
[edb@0 bin]$

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: refactoring basebackup.c

[tushar]

On 7/8/21 9:26 PM, Robert Haas wrote:

Here at last is a new version.
Please refer this scenario ,where backup target using 
--server-compression is closing the server

unexpectedly if we don't provide -no-manifest option

[tushar@localhost bin]$ ./pg_basebackup --server-compression=gzip4  -t 
server:/tmp/data_1  -Xnone
NOTICE:  WAL archiving is not enabled; you must ensure that all required 
WAL segments are copied through other means to complete the backup
pg_basebackup: error: could not read COPY data: server closed the 
connection unexpectedly

    This probably means the server terminated abnormally
    before or while processing the request.

if we try to check with -Ft then this same scenario  is working ?

[tushar@localhost bin]$ ./pg_basebackup --server-compression=gzip4  -Ft 
-D data_0 -Xnone
NOTICE:  WAL archiving is not enabled; you must ensure that all required 
WAL segments are copied through other means to complete the backup

[tushar@localhost bin]$

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

pg_upgrade is failed for 'plpgsql_call_handler' handler

[tushar]

Hi,

In one of my testing scenario, i found pg_upgrade is failed for 
'plpgsql_call_handler' handler


Steps to reproduce - ( on any supported version of PG)

Perform initdb ( ./initdb -D  d1 ;  ./initdb -D d2)

Start d1 cluster(./pg_ctl -D d1 start) , connect to postgres (./psql 
postgres)  and create this language


postgres=# CREATE TRUSTED LANGUAGE plspl_sm HANDLER plpgsql_call_handler;
CREATE LANGUAGE

stop the server (./pg_ctl -D d1 stop)

perform pg_upgrade ( ./pg_upgrade -d d1 -D d2 -b . B .)

will fail with these message

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 825; 2612 16384 PROCEDURAL LANGUAGE plspl_sm edb
pg_restore: error: could not execute query: ERROR:  could not open 
extension control file 
"/home/edb/pg14/pg/edbpsql/share/postgresql/extension/plspl_sm.control": 
No such file or directory

Command was: CREATE OR REPLACE PROCEDURAL LANGUAGE "plspl_sm";

is this expected ?

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [Patch] ALTER SYSTEM READ ONLY

[tushar]

On 6/22/20 11:59 AM, Amul Sul wrote:

2. Now skipping the startup checkpoint if the system is read-only mode, as
discussed [2].


I am not able to perform pg_checksums o/p after shutting down my server 
in read only  mode .


Steps -

1.initdb (./initdb -k -D data)
2.start the server(./pg_ctl -D data start)
3.connect to psql (./psql postgres)
4.Fire query (alter system read only;)
5.shutdown the server(./pg_ctl -D data stop)
6.pg_checksums

[edb@tushar-ldap-docker bin]$ ./pg_checksums -D data
pg_checksums: error: cluster must be shut down
[edb@tushar-ldap-docker bin]$

Result - (when server is not in read only)

[edb@tushar-ldap-docker bin]$ ./pg_checksums -D data
Checksum operation completed
Files scanned:  916
Blocks scanned: 2976
Bad checksums:  0
Data checksum version: 1

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [Patch] ALTER SYSTEM READ ONLY

[tushar]

On 6/16/20 7:25 PM, amul sul wrote:
Attached patch proposes $Subject feature which forces the system into 
read-only
mode where insert write-ahead log will be prohibited until ALTER 
SYSTEM READ

WRITE executed.


Thanks Amul.

1) ALTER SYSTEM

postgres=# alter system read only;
ALTER SYSTEM
postgres=# alter  system reset all;
ALTER SYSTEM
postgres=# create table t1(n int);
ERROR:  cannot execute CREATE TABLE in a read-only transaction

Initially i thought after firing 'Alter system reset all' , it will be 
back to  normal.


can't we have a syntax like - "Alter system set read_only='True' ; "

so that ALTER SYSTEM command syntax should be same for all.

postgres=# \h alter system
Command: ALTER SYSTEM
Description: change a server configuration parameter
Syntax:
ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | 
DEFAULT }


ALTER SYSTEM RESET configuration_parameter
ALTER SYSTEM RESET ALL

How we are going to justify this in help command of ALTER SYSTEM ?

2)When i connected to postgres in a single user mode , i was not able to 
set the system in read only


[edb@tushar-ldap-docker bin]$ ./postgres --single -D data postgres


PostgreSQL stand-alone backend 14devel
backend> alter system read only;
ERROR:  checkpointer is not running

backend>

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[tushar]

On 4/29/20 8:52 AM, 曾文旌 wrote:

Fixed the error message to make the expression more accurate. In v33.


Thanks wenjing

Please refer this scenario  , where getting an error while performing 
cluster o/p


1)

X terminal -

postgres=# create global temp table f(n int);
CREATE TABLE

Y Terminal -

postgres=# create index index12 on f(n);
CREATE INDEX
postgres=# \q

X terminal -

postgres=# reindex index  index12;
REINDEX
postgres=#  cluster f using index12;
ERROR:  cannot cluster on invalid index "index12"
postgres=# drop index index12;
DROP INDEX

if this is an expected  , could we try  to make the error message more 
simpler, if possible.


Another issue  -

X terminal -

postgres=# create global temp table f11(n int);
CREATE TABLE
postgres=# create index ind1 on f11(n);
CREATE INDEX
postgres=# create index ind2 on f11(n);
CREATE INDEX
postgres=#

Y terminal -

postgres=# drop table f11;
ERROR:  cannot drop index ind2 or global temporary table f11
HINT:  Because the index is created on the global temporary table and 
other backend attached it.

postgres=#

it is only mentioning about ind2 index but what about ind1 and what if  
- they have lots of indexes ?
i  think - we should not mix index information while dropping the table 
and vice versa.


--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

[pg_dump] 'create index' statement is failing due to search_path is empty

[tushar]

Hi ,

While testing something else ,i found 1 scenario  where pg_dump  is failing

Below is the standalone scenario -

--connect to psql terminal and create 2 database

postgres=# create database db1;
CREATE DATABASE
postgres=# create database db2;
CREATE DATABASE

--Connect to database db1 and run these below bunch of sql ( got from 
vacuum.sql file)


\c db1

create  temp table vaccluster (i INT PRIMARY KEY);
ALTER TABLE vaccluster CLUSTER ON vaccluster_pkey;
CLUSTER vaccluster;

CREATE FUNCTION do_analyze() RETURNS VOID VOLATILE LANGUAGE SQL
    AS 'ANALYZE pg_am';
CREATE FUNCTION wrap_do_analyze(c INT) RETURNS INT IMMUTABLE LANGUAGE SQL
    AS 'SELECT $1 FROM do_analyze()';
CREATE INDEX ON vaccluster(wrap_do_analyze(i));
INSERT INTO vaccluster VALUES (1), (2);

--Take the  dump of db1 database  ( ./pg_dump -Fp db1 > /tmp/dump.sql)

--Restore the dump file into db2 database

You are now connected to database "db2" as user "tushar".
db2=# \i /tmp/dump.sql
SET
SET
SET
SET
SET
 set_config


(1 row)

SET
SET
SET
SET
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
SET
SET
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
psql:/tmp/dump.sql:71: ERROR:  function do_analyze() does not exist
LINE 1: SELECT $1 FROM do_analyze()
   ^
HINT:  No function matches the given name and argument types. You might 
need to add explicit type casts.

QUERY:  SELECT $1 FROM do_analyze()
CONTEXT:  SQL function "wrap_do_analyze" during inlining
db2=#

Workaround -

reset search_path ; before 'create index' statement in the dump.sql file .

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[tushar]

On 4/22/20 2:49 PM, 曾文旌 wrote:


I provide the TRUNCATE tablename DROP to clear the data in the GTT and 
delete the storage files.
This feature requires the current transaction to commit immediately 
after it finishes truncate.



Thanks Wenjing , Please refer this scenario

postgres=# create global temp table testing (a int);
CREATE TABLE
postgres=# begin;
BEGIN
postgres=*# truncate testing;  -- working   [1]
TRUNCATE TABLE
postgres=*# truncate testing drop;
ERROR:  Truncate global temporary table cannot run inside a transaction 
block    --that is throwing an error claiming something which i did  
successfully [1]

postgres=!#

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [IBM z Systems] Getting server crash when jit_above_cost =0

[tushar]

On 4/22/20 2:40 AM, Thomas Munro wrote:

I'm not sure if you're seeing the same problem or another similar one,
but I know that Andres got a patch along those lines into llvm.  Maybe
you could try on a more recent llvm release?
Thanks a lot Thomas. it is working fine with llvm-toolset-7.0. look 
like  issue is with llvm-toolset-6.0 .
Yesterday when we installed llvm-toolset-7  (yum install 
llvm-toolset-7.0), there was no llvm-config available under 
/opt/rh/llvm-toolset-7.0/root/usr/bin/

so we ,chosen  llvm-toolset-6 with PG v12.
today , we again fired this same yum command using asterisk , now all 
the required file have been placed under llvm-toolset-7 directory and 
things look fine.


--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

[IBM z Systems] Rpm package issues.

[tushar]

HI,

I tried to install PG v11 and v12 on IBM z/OS using YUM command , 
following -https://wiki.postgresql.org/wiki/YUM_Installation_on_z_Systems


Found that 2 issues

1) rpm packages  are failing  due to " Package .  is not signed "

PG v12 -
Package postgresql12-contrib-12.1-2PGDG.rhel7.s390x.rpm is not signed
PG v11
Package postgresql11-libs-11.6-2PGDG.rhel7.s390x.rpm is not signed

2) Rpm packages are NOT updated . still showing 11.6 and 12.1 version 
whereas latest released version is 11.7 and 12.2.


--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

[IBM z Systems] Getting server crash when jit_above_cost =0

[tushar]

v=argv@entry=0xa8c00cc0) at postmaster.c:1377
#26 0x800811f4 in main (argc=, argv=0xa8c00cc0) 
at main.c:228


--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[tushar]

On 4/20/20 2:59 PM, 曾文旌 wrote:

Please check my new patch.


Thanks Wenjing. Please refer this below scenario , getting error -  
ERROR:  could not read block 0 in file "base/16466/t4_16472": read only 
0 of 8192 bytes


Steps to reproduce

Connect to psql terminal,create a table ( create global temp table t2 (n 
int primary key ) on commit delete rows;)

exit from psql terminal and execute (./clusterdb -t t2 -d postgres -v)
connect to psql terminal and one by one execute these below sql statements
(
cluster verbose t2 using t2_pkey;
cluster verbose t2 ;
alter table t2 add column i int;
cluster verbose t2 ;
cluster verbose t2 using t2_pkey;
create unique index ind on t2(n);
create unique index concurrently  ind1 on t2(n);
select * from t2;
)
This last SQL - will throw this error -  - ERROR:  could not read block 
0 in file "base/16466/t4_16472": read only 0 of 8192 bytes


--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

While restoring -getting error if dump contain sql statements generated from generated.sql file

[tushar]

Hi ,

We have a sql file  called 'generated.sql' under src/test/regress/sql 
folder . if we run this file on psql , take the dump and try to restore 
it on another db

we are getting error like -

psql:/tmp/x:434: ERROR:  column "b" of relation "gtest1_1" is a 
generated column

psql:/tmp/x:441: ERROR:  cannot use column reference in DEFAULT expression

These sql statements , i copied from the dump file

postgres=# CREATE TABLE public.gtest30 (
postgres(# a integer,
postgres(# b integer
postgres(# );
CREATE TABLE
postgres=#
postgres=# CREATE TABLE public.gtest30_1 (
postgres(# )
postgres-# INHERITS (public.gtest30);
CREATE TABLE
postgres=# ALTER TABLE ONLY public.gtest30_1 ALTER COLUMN b SET DEFAULT 
(a * 2);

ERROR:  cannot use column reference in DEFAULT expression
postgres=#

Steps to reproduce -

connect to psql - ( ./psql postgres)
create database ( create database x;)
connect to database x (\c x )
execute generated.sql file (\i ../../src/test/regress/sql/generated.sql)
take the dump of x db (./pg_dump -Fp x > /tmp/t.dump)
create another database  (create database y;)
Connect to y db (\c y)
execute plain dump sql file (\i /tmp/t.dump)

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[tushar]

On 4/9/20 6:26 PM, 曾文旌 wrote:

On 4/7/20 2:27 PM, 曾文旌 wrote:

Vacuum full GTT, cluster GTT is already supported in 
global_temporary_table_v24-pg13.patch.

Here , it is skipping GTT

postgres=# \c foo
You are now connected to database "foo" as user "tushar".
foo=# create global temporary table  g123( c1 int) ;
CREATE TABLE
foo=# \q
[tushar@localhost bin]$ ./vacuumdb --full  foo
vacuumdb: vacuuming database "foo"
WARNING:  skipping vacuum global temp table "g123" because storage is not 
initialized for current session

The message was inappropriate at some point, so I removed it.


Thanks Wenjing. Please see -if this below behavior is correct

X terminal -

postgres=# create global temp table foo1(n int);
CREATE TABLE
postgres=# insert into foo1 values (generate_series(1,10));
INSERT 0 10
postgres=# vacuum full ;
VACUUM

Y Terminal -

[tushar@localhost bin]$ ./vacuumdb -f  postgres
vacuumdb: vacuuming database "postgres"
WARNING:  global temp table oldest relfrozenxid 3276 is the oldest in 
the entire db

DETAIL:  The oldest relfrozenxid in pg_class is 3277
HINT:  If they differ greatly, please consider cleaning up the data in 
global temp table.
WARNING:  global temp table oldest relfrozenxid 3276 is the oldest in 
the entire db

DETAIL:  The oldest relfrozenxid in pg_class is 3277
HINT:  If they differ greatly, please consider cleaning up the data in 
global temp table.



--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[tushar]

On 4/13/20 1:57 PM, 曾文旌 wrote:

[tushar@localhost bin]$ tail -20   pg_upgrade_dump_13592.log
pg_restore: error: could not execute query: ERROR:  pg_type array OID value not 
set when in binary upgrade mode

I found that the regular table also has this problem, I am very unfamiliar with 
this part, so I opened another email to consult this problem.


ohh. Thanks.

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[tushar]

On 4/7/20 2:27 PM, 曾文旌 wrote:
Vacuum full GTT, cluster GTT is already 
supported in global_temporary_table_v24-pg13.patch.


Hi Wenjing,

Please refer this scenario , where reindex   message is not coming next 
time ( after reconnecting to database) for GTT


A)
--normal table
postgres=# create table nt(n int primary key);
CREATE TABLE
--GTT table
postgres=# create global temp table gtt(n int primary key);
CREATE TABLE
B)
--Reindex  , normal table
postgres=# REINDEX (VERBOSE) TABLE  nt;
INFO:  index "nt_pkey" was reindexed
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
REINDEX
--reindex GTT table
postgres=# REINDEX (VERBOSE) TABLE  gtt;
INFO:  index "gtt_pkey" was reindexed
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
REINDEX
C)
--Reconnect  to database
postgres=# \c
You are now connected to database "postgres" as user "tushar".
D) again perform step B)

postgres=# REINDEX (VERBOSE) TABLE  nt;
INFO:  index "nt_pkey" was reindexed
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
REINDEX
postgres=# REINDEX (VERBOSE) TABLE  gtt;   <-- message  not coming
REINDEX

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[tushar]

On 4/7/20 2:27 PM, 曾文旌 wrote:
Vacuum full GTT, cluster GTT is already 
supported in global_temporary_table_v24-pg13.patch.

Please refer this below scenario , where pg_upgrade is failing
1)Server is up and running (./pg_ctl -D data status)
2)Stop the server ( ./pg_ctl -D data stop)
3)Connect to server using single user mode ( ./postgres --single -D data 
postgres) and create a global temp table

[tushar@localhost bin]$ ./postgres --single -D data1233 postgres

PostgreSQL stand-alone backend 13devel
backend> create global temp table t(n int);

--Press Ctl+D to exit

4)Perform initdb ( ./initdb -D data123)
5.Run pg_upgrade
[tushar@localhost bin]$ ./pg_upgrade -d data -D data123 -b . -B .
--
--
--
Restoring database schemas in the new cluster
  postgres
*failure*
Consult the last few lines of "pg_upgrade_dump_13592.log" for
the probable cause of the failure.
Failure, exiting

log file content  -

[tushar@localhost bin]$ tail -20   pg_upgrade_dump_13592.log
pg_restore: error: could not execute query: ERROR:  pg_type array OID 
value not set when in binary upgrade mode

Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT 
pg_catalog.binary_upgrade_set_next_pg_type_oid('13594'::pg_catalog.oid);



-- For binary upgrade, must preserve pg_class oids
SELECT 
pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('13593'::pg_catalog.oid);


CREATE GLOBAL TEMPORARY TABLE "public"."t" (
    "n" integer
)
WITH ("on_commit_delete_rows"='false');

-- For binary upgrade, set heap's relfrozenxid and relminmxid
UPDATE pg_catalog.pg_class
SET relfrozenxid = '0', relminmxid = '0'
WHERE oid = '"public"."t"'::pg_catalog.regclass;

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[tushar]

On 4/7/20 2:27 PM, 曾文旌 wrote:
Vacuum full GTT, cluster GTT is already 
supported in global_temporary_table_v24-pg13.patch.


Here , it is skipping GTT

postgres=# \c foo
You are now connected to database "foo" as user "tushar".
foo=# create global temporary table  g123( c1 int) ;
CREATE TABLE
foo=# \q
[tushar@localhost bin]$ ./vacuumdb --full  foo
vacuumdb: vacuuming database "foo"
WARNING:  skipping vacuum global temp table "g123" because storage is 
not initialized for current session


--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[tushar]

On 3/27/20 10:55 AM, 曾文旌 wrote:

Hi Wenjing,
This patch(gtt_v21_pg13.patch) is not applicable on PG HEAD, I hope 
you have prepared the patch on top of some previous commit.

Could you please rebase the patch which we can apply on HEAD ?

Yes, It looks like the built-in functions are in conflict with new code.


In this below scenario, pg_dump is failing -

test=# CREATE database foo;
CREATE DATABASE
test=# \c foo
You are now connected to database "foo" as user "tushar".
foo=# CREATE GLOBAL TEMPORARY TABLE bar(c1 bigint, c2 bigserial) on 
commit PRESERVE rows;

CREATE TABLE
foo=# \q

[tushar@localhost bin]$ ./pg_dump -Fp foo > /tmp/rf2
pg_dump: error: query to get data of sequence "bar_c2_seq" returned 0 
rows (expected 1)

[tushar@localhost bin]$

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[tushar]

On 3/27/20 10:55 AM, 曾文旌 wrote:

Hi Wenjing,
This patch(gtt_v21_pg13.patch) is not applicable on PG HEAD, I hope 
you have prepared the patch on top of some previous commit.

Could you please rebase the patch which we can apply on HEAD ?

Yes, It looks like the built-in functions are in conflict with new code.



This error message looks wrong  to me-

postgres=# reindex table concurrently t ;
ERROR:  cannot create indexes on global temporary tables using 
concurrent mode

postgres=#

Better message would be-

ERROR:  cannot reindex global temporary tables concurrently

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[tushar]

On 3/17/20 9:15 AM, 曾文旌(义从) wrote:

Please check global_temporary_table_v20-pg13.patch


There is a typo in the error message

postgres=# create global temp table test(a int ) 
with(on_commit_delete_rows=true) on commit delete rows;
ERROR:  can not defeine global temp table with on commit and with clause 
at same time

postgres=#

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[tushar]

On 3/17/20 9:15 AM, 曾文旌(义从) wrote:

reindex GTT is already supported

Please check global_temporary_table_v20-pg13.patch


Please refer this scenario -


postgres=# create global temp table co(n int) ;
CREATE TABLE

postgres=# create index fff on co(n);
CREATE INDEX

Case 1-
postgres=# reindex table  co;
REINDEX

Case -2
postgres=# reindex database postgres ;
WARNING:  global temp table "public.co" skip reindexed
REINDEX
postgres=#

Case 2 should work as similar to Case 1.

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: backup manifests

[tushar]

On 3/14/20 2:04 AM, Robert Haas wrote:

OK. Done in the attached version


Thanks. Verified.

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[tushar]

Hi Wenjing,

I have created a global table on X session but i am not able to drop 
from Y session ?


X session - ( connect to psql terminal )
postgres=# create global temp table foo(n int);
CREATE TABLE
postgres=# select * from foo;
 n
---
(0 rows)


Y session - ( connect to psql terminal )
postgres=# drop table foo;
ERROR:  can not drop relation foo when other backend attached this 
global temp table


Table has been created  so i think - user should be able to drop from 
another session as well without exit from X session.


regards,

On 3/16/20 1:35 PM, 曾文旌(义从) wrote:



2020年3月16日 下午2:23，Prabhat Sahu <mailto:prabhat.s...@enterprisedb.com>> 写道：


Hi Wenjing,
Please check the below scenario, where the Foreign table on GTT not 
showing records.


postgres=# create extension postgres_fdw;
CREATE EXTENSION
postgres=# do $d$
    begin
        execute $$create server fdw foreign data wrapper postgres_fdw 
options (host 'localhost',dbname 'postgres',port 
'$$||current_setting('port')||$$')$$;

    end;
$d$;
DO
postgres=# create user mapping for public server fdw;
CREATE USER MAPPING

postgres=# create table lt1 (c1 integer, c2 varchar(50));
CREATE TABLE
postgres=# insert into lt1 values (1,'c21');
INSERT 0 1
postgres=# create foreign table ft1 (c1 integer, c2 varchar(50)) 
server fdw options (table_name 'lt1');

CREATE FOREIGN TABLE
postgres=# select * from ft1;
 c1 | c2
+-
  1 | c21
(1 row)

postgres=# create global temporary table gtt1 (c1 integer, c2 
varchar(50));

CREATE TABLE
postgres=# insert into gtt1 values (1,'gtt_c21');
INSERT 0 1
postgres=# create foreign table f_gtt1 (c1 integer, c2 varchar(50)) 
server fdw options (table_name 'gtt1');

CREATE FOREIGN TABLE

postgres=# select * from gtt1;
 c1 |   c2
+-
  1 | gtt_c21
(1 row)

postgres=# select * from f_gtt1;
 c1 | c2
+
(0 rows)

--


I understand that postgre_fdw works similar to dblink.
postgre_fdw access to the table requires a new connection.
The data in the GTT table is empty in the newly established connection.
Because GTT shares structure but not data between connections.

Try local temp table:
create temporary table ltt1 (c1 integer, c2 varchar(50));

insert into ltt1 values (1,'gtt_c21');

create foreign table f_ltt1 (c1 integer, c2 varchar(50)) server fdw 
options (table_name 'ltt1');


select * from ltt1;
 c1 |   c2
+-
  1 | gtt_c21
(1 row)

select * from l_gtt1;
ERROR:  relation "l_gtt1" does not exist
LINE 1: select * from l_gtt1;


Wenjing



With Regards,
Prabhat Kumar Sahu
EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com/>





--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: backup manifests

[tushar]

On 3/12/20 8:16 PM, tushar wrote:

Seems like expected behavior to me. We could consider providing a more
descriptive error message, but there's now way for it to work.


Right , Error message need to be more user friendly . 


One scenario which i feel - should error out  even if  -s option is 
specified.


create  base  backup directory ( ./pg_basebackup data1)
Connect to root user and take out  the permission from pg_hba.conf file 
( chmod 004 pg_hba.conf)


run pg_validatebackup -

[centos@tushar-ldap-docker bin]$ ./pg_validatebackup data1
pg_validatebackup: error: could not open file "pg_hba.conf": Permission 
denied


run pg_validatebackup  with switch -s

[centos@tushar-ldap-docker bin]$ ./pg_validatebackup data1 -s
pg_validatebackup: backup successfully verified

here file is not accessible so i think - it should throw you an error ( 
the same above one) instead of   blindly skipping it.


--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[tushar]

On 3/9/20 10:01 PM, 曾文旌(义从) wrote:

Fixed in global_temporary_table_v18-pg13.patch.


Thanks Wenjing.

I am getting this error  "ERROR:  could not open file 
"base/13589/t3_16440": No such file or directory" if 
max_active_global_temporary_table set to 0


Please refer this scenario -

postgres=# create global temp table  tab1 (n int ) with ( 
on_commit_delete_rows='true');

CREATE TABLE
postgres=# insert into tab1 values (1);
INSERT 0 1
postgres=# select * from tab1;
 n
---
(0 rows)

postgres=# alter system set max_active_global_temporary_table=0;
ALTER SYSTEM
postgres=# \q
[tushar@localhost bin]$ ./pg_ctl -D data/ restart -c -l logs123

waiting for server to start done
server started

[tushar@localhost bin]$ ./psql postgres
psql (13devel)
Type "help" for help.

postgres=# insert into tab1 values (1);
ERROR:  could not open file "base/13589/t3_16440": No such file or directory
postgres=#

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: backup manifests

[tushar]

On 3/9/20 10:46 PM, Robert Haas wrote:

Seems like expected behavior to me. We could consider providing a more
descriptive error message, but there's now way for it to work.


Right , Error message need to be more user friendly .

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: backup manifests

[tushar]

On 3/5/20 10:25 PM, Robert Haas wrote:

Here's an updated patch set responding to many of the comments
received thus far.
Thanks Robert. There is a scenario - if user provide port of v11 server 
at the time of  creating 'base backup'  against pg_basebackup(v13+ your 
patch applied)

with option --manifest-checksums,will lead to  this  below error

[centos@tushar-ldap-docker bin]$ ./pg_basebackup -R -p 9045 
--manifest-checksums=SHA224 -D dc1

pg_basebackup: error: could not initiate base backup: ERROR: syntax error
pg_basebackup: removing data directory "dc1"
[centos@tushar-ldap-docker bin]$

Steps to reproduce -
PG v11 is running
run pg_basebackup against that with option --manifest-checksums

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[tushar]

On 3/6/20 12:35 PM, 曾文旌(义从) wrote:

Fixed in global_temporary_table_v17-pg13.patch


I observed that , we do support 'global temp' keyword with views

postgres=# create or replace  global temp view v1 as select 5;
CREATE VIEW

but if we take the dump( using pg_dumpall) then it only display 'create 
view'


look like we are skipping it ?

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[tushar]

On 3/6/20 12:35 PM, 曾文旌(义从) wrote:

Fixed in global_temporary_table_v17-pg13.patch


Thanks Wenjing.

Please refer this scenario , where i am able to set 
'on_commit_delete_rows=true'  on regular table using 'alter' Syntax  
which is not allowed using 'Create' Syntax


--Expected -

postgres=# CREATE TABLE foo () WITH (on_commit_delete_rows='true');
ERROR:  The parameter on_commit_delete_rows is exclusive to the global 
temp table, which cannot be specified by a regular table


--But user can do this with 'alter' command -
postgres=# create table foo();
CREATE TABLE
postgres=# alter table foo set (on_commit_delete_rows='true');
ALTER TABLE
postgres=#

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[tushar]

On 3/3/20 2:10 PM, 曾文旌(义从) wrote:

I fixed in global_temporary_table_v16-pg13.patch.


Please refer this scenario -

--Connect to psql -

postgres=# alter system set max_active_global_temporary_table =1;
ALTER SYSTEM

--restart the server (./pg_ctl -D data restart)

--create global temp table

postgres=# create global temp  table ccc1  (c int);
CREATE TABLE

--Try to Create another global temp table

*postgres=# create global temp  table ccc2  (c int);**
**WARNING:  relfilenode 13589/1663/19063 not exist in gtt shared hash 
when forget**

**ERROR:  out of shared memory**
**HINT:  You might need to increase max_active_gtt.**
*
postgres=# show max_active_gtt;
ERROR:  unrecognized configuration parameter "max_active_gtt"
postgres=#
postgres=# show max_active_global_temporary_table ;
 max_active_global_temporary_table
---
 1
(1 row)

postgres=#

I cannot find "max_active_gtt"  GUC . I think you are referring to  
"max_active_global_temporary_table" here ?


also , would be great  if we can make this error message  user friendly 
like  - "max connection reached"  rather than memory error


--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: backup manifests

[tushar]

There is one small observation if we use slash (/) with option -i then 
not getting the desired result


Steps to reproduce -
==

[centos@tushar-ldap-docker bin]$ ./pg_basebackup -D test

[centos@tushar-ldap-docker bin]$ touch test/*pg_notify*/dummy_file

--working
[centos@tushar-ldap-docker bin]$ ./pg_validatebackup   
--ignore=*pg_notify*  test
pg_validatebackup: * manifest_checksum = 
be9b72e1320c6c34c131533de19371a10dd5011940181724e43277f786026c7b

pg_validatebackup: backup successfully verified

--not working

[centos@tushar-ldap-docker bin]$ ./pg_validatebackup   
--ignore=*pg_notify/*  test
pg_validatebackup: * manifest_checksum = 
be9b72e1320c6c34c131533de19371a10dd5011940181724e43277f786026c7b
pg_validatebackup: error: "pg_notify/dummy_file" is present on disk but 
not in the manifest


regards,

On 3/5/20 3:40 PM, tushar wrote:

Hi,

There is one scenario  where  i somehow able to run pg_validatebackup 
successfully but when i tried to start the server , it is failing


Steps to reproduce -
--create 2 base backup directory
[centos@tushar-ldap-docker bin]$ ./pg_basebackup -D db1
[centos@tushar-ldap-docker bin]$ ./pg_basebackup -D db2

--run pg_validatebackup , use backup_manifest of db1 directory 
against  db2/  . Will get an error
[centos@tushar-ldap-docker bin]$ ./pg_validatebackup -m 
db1/backup_manifest db2/
pg_validatebackup: * manifest_checksum = 
5b131aff4a4f86e2a53efd84b003a67b9f615decb0039f19033eefa6f43c1ede

pg_validatebackup: error: checksum mismatch for file "backup_label"
--copy the backup_level of db1 to db2 folder
[centos@tushar-ldap-docker bin]$ cp db1/backup_label db2/.

--run pg_validatebackup .. working fine
[centos@tushar-ldap-docker bin]$ ./pg_validatebackup -m 
db1/backup_manifest db2/
pg_validatebackup: * manifest_checksum = 
5b131aff4a4f86e2a53efd84b003a67b9f615decb0039f19033eefa6f43c1ede

pg_validatebackup: backup successfully verified
[centos@tushar-ldap-docker bin]$

--try to start the server
[centos@tushar-ldap-docker bin]$ ./pg_ctl -D db2 start -o '-p '
waiting for server to start2020-03-05 15:33:53.471 IST [24049] 
LOG:  starting PostgreSQL 13devel on x86_64-pc-linux-gnu, compiled by 
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2020-03-05 15:33:53.471 IST [24049] LOG:  listening on IPv6 address 
"::1", port 
2020-03-05 15:33:53.471 IST [24049] LOG:  listening on IPv4 address 
"127.0.0.1", port 
2020-03-05 15:33:53.473 IST [24049] LOG:  listening on Unix socket 
"/tmp/.s.PGSQL."
2020-03-05 15:33:53.476 IST [24050] LOG:  database system was 
interrupted; last known up at 2020-03-05 15:32:51 IST

2020-03-05 15:33:53.573 IST [24050] LOG:  invalid checkpoint record
2020-03-05 15:33:53.573 IST [24050] FATAL:  could not locate required 
checkpoint record
2020-03-05 15:33:53.573 IST [24050] HINT:  If you are restoring from a 
backup, touch 
"/home/centos/pg13_bk_mani/edb/edbpsql/bin/db2/recovery.signal" and 
add required recovery options.
    If you are not restoring from a backup, try removing the file 
"/home/centos/pg13_bk_mani/edb/edbpsql/bin/db2/backup_label".
    Be careful: removing 
"/home/centos/pg13_bk_mani/edb/edbpsql/bin/db2/backup_label" will 
result in a corrupt cluster if restoring from a backup.
2020-03-05 15:33:53.574 IST [24049] LOG:  startup process (PID 24050) 
exited with exit code 1
2020-03-05 15:33:53.574 IST [24049] LOG:  aborting startup due to 
startup process failure

2020-03-05 15:33:53.575 IST [24049] LOG:  database system is shut down
 stopped waiting
pg_ctl: could not start server
Examine the log output.
[centos@tushar-ldap-docker bin]$

regards,


On 3/5/20 1:09 PM, Rajkumar Raghuwanshi wrote:

Hi,

In a negative test scenario, if I changed size to -1 in 
backup_manifest, pg_validatebackup giving

error with a random size number.

[edb@localhost bin]$ ./pg_basebackup -p 5551 -D /tmp/bold 
--manifest-checksum 'SHA256'

[edb@localhost bin]$ ./pg_validatebackup /tmp/bold
pg_validatebackup: backup successfully verified

--change a file size to -1 and generate new checksum.
[edb@localhost bin]$ vi /tmp/bold/backup_manifest
[edb@localhost bin]$ shasum -a256 /tmp/bold/backup_manifest
c3d7838cbbf991c6108f9c1ab78f673c20d8073114500f14da6ed07ede2dc44a 
 /tmp/bold/backup_manifest

[edb@localhost bin]$ vi /tmp/bold/backup_manifest

[edb@localhost bin]$ ./pg_validatebackup /tmp/bold
pg_validatebackup: error: "global/4183" has size 0 on disk but size 
*18446744073709551615* in the manifest


Thanks & Regards,
Rajkumar Raghuwanshi


On Thu, Mar 5, 2020 at 9:37 AM Suraj Kharage 
<mailto:suraj.khar...@enterprisedb.com>> wrote:



On Wed, Mar 4, 2020 at 7:21 PM tushar
mailto:tushar.ah...@enterprisedb.com>> wrote:

Hi,

There is a scenario in which i add something inside the
pg_tablespace directory , i am getting an error like-

pg_validatebackup: * manifest_checksu

Re: backup manifests

[tushar]

Hi,

There is one scenario  where  i somehow able to run pg_validatebackup 
successfully but when i tried to start the server , it is failing


Steps to reproduce -
--create 2 base backup directory
[centos@tushar-ldap-docker bin]$ ./pg_basebackup -D db1
[centos@tushar-ldap-docker bin]$ ./pg_basebackup -D db2

--run pg_validatebackup , use backup_manifest of db1 directory against  
db2/  . Will get an error
[centos@tushar-ldap-docker bin]$ ./pg_validatebackup -m 
db1/backup_manifest db2/
pg_validatebackup: * manifest_checksum = 
5b131aff4a4f86e2a53efd84b003a67b9f615decb0039f19033eefa6f43c1ede

pg_validatebackup: error: checksum mismatch for file "backup_label"
--copy the backup_level of db1 to db2 folder
[centos@tushar-ldap-docker bin]$ cp db1/backup_label db2/.

--run pg_validatebackup .. working fine
[centos@tushar-ldap-docker bin]$ ./pg_validatebackup -m 
db1/backup_manifest db2/
pg_validatebackup: * manifest_checksum = 
5b131aff4a4f86e2a53efd84b003a67b9f615decb0039f19033eefa6f43c1ede

pg_validatebackup: backup successfully verified
[centos@tushar-ldap-docker bin]$

--try to start the server
[centos@tushar-ldap-docker bin]$ ./pg_ctl -D db2 start -o '-p '
waiting for server to start2020-03-05 15:33:53.471 IST [24049] LOG:  
starting PostgreSQL 13devel on x86_64-pc-linux-gnu, compiled by gcc 
(GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2020-03-05 15:33:53.471 IST [24049] LOG:  listening on IPv6 address 
"::1", port 
2020-03-05 15:33:53.471 IST [24049] LOG:  listening on IPv4 address 
"127.0.0.1", port 
2020-03-05 15:33:53.473 IST [24049] LOG:  listening on Unix socket 
"/tmp/.s.PGSQL."
2020-03-05 15:33:53.476 IST [24050] LOG:  database system was 
interrupted; last known up at 2020-03-05 15:32:51 IST

2020-03-05 15:33:53.573 IST [24050] LOG:  invalid checkpoint record
2020-03-05 15:33:53.573 IST [24050] FATAL:  could not locate required 
checkpoint record
2020-03-05 15:33:53.573 IST [24050] HINT:  If you are restoring from a 
backup, touch 
"/home/centos/pg13_bk_mani/edb/edbpsql/bin/db2/recovery.signal" and add 
required recovery options.
    If you are not restoring from a backup, try removing the file 
"/home/centos/pg13_bk_mani/edb/edbpsql/bin/db2/backup_label".
    Be careful: removing 
"/home/centos/pg13_bk_mani/edb/edbpsql/bin/db2/backup_label" will result 
in a corrupt cluster if restoring from a backup.
2020-03-05 15:33:53.574 IST [24049] LOG:  startup process (PID 24050) 
exited with exit code 1
2020-03-05 15:33:53.574 IST [24049] LOG:  aborting startup due to 
startup process failure

2020-03-05 15:33:53.575 IST [24049] LOG:  database system is shut down
 stopped waiting
pg_ctl: could not start server
Examine the log output.
[centos@tushar-ldap-docker bin]$

regards,


On 3/5/20 1:09 PM, Rajkumar Raghuwanshi wrote:

Hi,

In a negative test scenario, if I changed size to -1 in 
backup_manifest, pg_validatebackup giving

error with a random size number.

[edb@localhost bin]$ ./pg_basebackup -p 5551 -D /tmp/bold 
--manifest-checksum 'SHA256'

[edb@localhost bin]$ ./pg_validatebackup /tmp/bold
pg_validatebackup: backup successfully verified

--change a file size to -1 and generate new checksum.
[edb@localhost bin]$ vi /tmp/bold/backup_manifest
[edb@localhost bin]$ shasum -a256 /tmp/bold/backup_manifest
c3d7838cbbf991c6108f9c1ab78f673c20d8073114500f14da6ed07ede2dc44a 
 /tmp/bold/backup_manifest

[edb@localhost bin]$ vi /tmp/bold/backup_manifest

[edb@localhost bin]$ ./pg_validatebackup /tmp/bold
pg_validatebackup: error: "global/4183" has size 0 on disk but size 
*18446744073709551615* in the manifest


Thanks & Regards,
Rajkumar Raghuwanshi


On Thu, Mar 5, 2020 at 9:37 AM Suraj Kharage 
<mailto:suraj.khar...@enterprisedb.com>> wrote:



On Wed, Mar 4, 2020 at 7:21 PM tushar
mailto:tushar.ah...@enterprisedb.com>> wrote:

Hi,

There is a scenario in which i add something inside the
pg_tablespace directory , i am getting an error like-

pg_validatebackup: * manifest_checksum =
77ddacb4e7e02e2b880792a19a3adf09266dd88553dd15cfd0c22caee7d9cc04
pg_validatebackup: error:
"pg_tblspc/16385/*PG_13_202002271*/test" is present on disk
but not in the manifest

but if i remove 'PG_13_202002271 ' directory then there is no
error

[centos@tushar-ldap-docker bin]$ ./pg_validatebackup data
pg_validatebackup: * manifest_checksum =
77ddacb4e7e02e2b880792a19a3adf09266dd88553dd15cfd0c22caee7d9cc04
pg_validatebackup: backup successfully verified


This seems expected considering current design as we don't log the
directory entries in backup_manifest. In your case, you have
tablespace with no objects (empty tablespace) then backup_manifest
does not have any entry for this hence when you remove this
tablespace directory, validator could not detect it.

Re: [Proposal] Global temporary tables

[tushar]

On 3/3/20 2:10 PM, 曾文旌(义从) wrote:

I fixed in global_temporary_table_v16-pg13.patch.
Thanks Wenjing. The reported  issue is fixed now  but  there is an 
another similar  scenario -
if we enable 'on_commit_delete_rows' to true using alter command then 
getting same issue i.e rows are not removing after commit.


x=# create global  temp table foo123(n int) with 
(on_commit_delete_rows='false');

CREATE TABLE
x=#
x=# alter table foo123 set ( on_commit_delete_rows='true');
ALTER TABLE
x=#
x=# insert into foo123 values (1);
INSERT 0 1
x=# select * from foo123;   <- row should get removed.
 n
---
 1
(1 row)

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: backup manifests

[tushar]

Hi,

There is a scenario in which i add something inside the pg_tablespace 
directory , i am getting an error like-


pg_validatebackup: * manifest_checksum = 
77ddacb4e7e02e2b880792a19a3adf09266dd88553dd15cfd0c22caee7d9cc04
pg_validatebackup: error: "pg_tblspc/16385/*PG_13_202002271*/test" is 
present on disk but not in the manifest


but if i remove 'PG_13_202002271 ' directory then there is no error

[centos@tushar-ldap-docker bin]$ ./pg_validatebackup data
pg_validatebackup: * manifest_checksum = 
77ddacb4e7e02e2b880792a19a3adf09266dd88553dd15cfd0c22caee7d9cc04

pg_validatebackup: backup successfully verified

Steps to reproduce -
--connect to psql terminal   , create a tablespace
postgres=# \! mkdir /tmp/my_tblspc
postgres=# create tablespace tbs location '/tmp/my_tblspc';
CREATE TABLESPACE
postgres=# \q

--run pg_basebackup
[centos@tushar-ldap-docker bin]$ ./pg_basebackup -D data_dir   -T 
/tmp/my_tblspc/=/tmp/new_my_tblspc

[centos@tushar-ldap-docker bin]$
[centos@tushar-ldap-docker bin]$ ls /tmp/new_my_tblspc/
PG_13_202002271

--create a new file under PG_13_* folder
[centos@tushar-ldap-docker bin]$ touch 
/tmp/new_my_tblspc/PG_13_202002271/test

[centos@tushar-ldap-docker bin]$

--run pg_validatebackup ,Getting an error which looks expected
[centos@tushar-ldap-docker bin]$ ./pg_validatebackup data_dir/
pg_validatebackup: * manifest_checksum = 
3951308eab576906ebdb002ff00ca313b2c1862592168c1f5f7ecf051ac07907
pg_validatebackup: error: "pg_tblspc/16386/PG_13_202002271/test" is 
present on disk but not in the manifest

[centos@tushar-ldap-docker bin]$

--remove the added file
[centos@tushar-ldap-docker bin]$ rm -rf   
/tmp/new_my_tblspc/PG_13_202002271/test


--run pg_validatebackup , working fine
[centos@tushar-ldap-docker bin]$ ./pg_validatebackup data_dir/
pg_validatebackup: * manifest_checksum = 
3951308eab576906ebdb002ff00ca313b2c1862592168c1f5f7ecf051ac07907

pg_validatebackup: backup successfully verified
[centos@tushar-ldap-docker bin]$

--remove the folder PG_13*
[centos@tushar-ldap-docker bin]$ rm -rf   
/tmp/new_my_tblspc/PG_13_202002271/

[centos@tushar-ldap-docker bin]$
[centos@tushar-ldap-docker bin]$ ls /tmp/new_my_tblspc/

--run pg_validatebackup ,   No error reported  ?
[centos@tushar-ldap-docker bin]$ ./pg_validatebackup data_dir/
pg_validatebackup: * manifest_checksum = 
3951308eab576906ebdb002ff00ca313b2c1862592168c1f5f7ecf051ac07907

pg_validatebackup: backup successfully verified
[centos@tushar-ldap-docker bin]$

Start the server -

[centos@tushar-ldap-docker bin]$ ./pg_ctl -D data_dir/ start -o '-p 9033'
waiting for server to start2020-03-04 19:18:54.839 IST [13097] LOG:  
starting PostgreSQL 13devel on x86_64-pc-linux-gnu, compiled by gcc 
(GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2020-03-04 19:18:54.840 IST [13097] LOG:  listening on IPv6 address 
"::1", port 9033
2020-03-04 19:18:54.840 IST [13097] LOG:  listening on IPv4 address 
"127.0.0.1", port 9033
2020-03-04 19:18:54.842 IST [13097] LOG:  listening on Unix socket 
"/tmp/.s.PGSQL.9033"
2020-03-04 19:18:54.843 IST [13097] LOG:  could not open directory 
"pg_tblspc/16386/PG_13_202002271": No such file or directory
2020-03-04 19:18:54.845 IST [13098] LOG:  database system was 
interrupted; last known up at 2020-03-04 19:14:50 IST
2020-03-04 19:18:54.937 IST [13098] LOG:  could not open directory 
"pg_tblspc/16386/PG_13_202002271": No such file or directory
2020-03-04 19:18:54.939 IST [13098] LOG:  could not open directory 
"pg_tblspc/16386/PG_13_202002271": No such file or directory

2020-03-04 19:18:54.939 IST [13098] LOG:  redo starts at 0/1828
2020-03-04 19:18:54.939 IST [13098] LOG:  consistent recovery state 
reached at 0/18000100

2020-03-04 19:18:54.939 IST [13098] LOG:  redo done at 0/18000100
2020-03-04 19:18:54.941 IST [13098] LOG:  could not open directory 
"pg_tblspc/16386/PG_13_202002271": No such file or directory
2020-03-04 19:18:54.984 IST [13097] LOG:  database system is ready to 
accept connections

 done
server started
[centos@tushar-ldap-docker bin]$

regards,

On 3/4/20 3:51 PM, tushar wrote:
Another scenario, in which if we modify Manifest-Checksum" value from 
backup_manifest file , we are not getting an error


[centos@tushar-ldap-docker bin]$ ./pg_validatebackup data/
pg_validatebackup: * manifest_checksum = 
28d082921650d0ae881de8ceb122c8d2af5f449f51ecfb446827f7f49f91f65d

pg_validatebackup: backup successfully verified

open backup_manifest file and replace

"Manifest-Checksum": 
"8d082921650d0ae881de8ceb122c8d2af5f449f51ecfb446827f7f49f91f65d"}

with
"Manifest-Checksum": "Hello World"}

rerun the pg_validatebackup

[centos@tushar-ldap-docker bin]$ ./pg_validatebackup data/
pg_validatebackup: * manifest_checksum = Hello World
pg_validatebackup: backup successfully verified

regards,

On 3/4/20 3:26 PM, tushar wrote:

Hi,
Another observation 

Re: backup manifests

[tushar]

Another scenario, in which if we modify Manifest-Checksum" value from 
backup_manifest file , we are not getting an error


[centos@tushar-ldap-docker bin]$ ./pg_validatebackup data/
pg_validatebackup: * manifest_checksum = 
28d082921650d0ae881de8ceb122c8d2af5f449f51ecfb446827f7f49f91f65d

pg_validatebackup: backup successfully verified

open backup_manifest file and replace

"Manifest-Checksum": 
"8d082921650d0ae881de8ceb122c8d2af5f449f51ecfb446827f7f49f91f65d"}

with
"Manifest-Checksum": "Hello World"}

rerun the pg_validatebackup

[centos@tushar-ldap-docker bin]$ ./pg_validatebackup data/
pg_validatebackup: * manifest_checksum = Hello World
pg_validatebackup: backup successfully verified

regards,

On 3/4/20 3:26 PM, tushar wrote:

Hi,
Another observation , if i change the ownership of a file which is 
under global/ directory

i.e

[root@tushar-ldap-docker global]# chown enterprisedb 2396

and run the pg_validatebackup command, i am getting this message -

[centos@tushar-ldap-docker bin]$ ./pg_validatebackup 
pg_validatebackup: * manifest_checksum = 
e8cb007bcc9c0deab6eff51cd8d9d9af6af35b86e02f3055e60e70e56737e877
pg_validatebackup: error: could not open file "global/2396": 
Permission denied
*** Error in `./pg_validatebackup': double free or corruption (!prev): 
0x01850ba0 ***

=== Backtrace: =
/lib64/libc.so.6(+0x81679)[0x7fa2248e3679]
./pg_validatebackup[0x401f4c]
/lib64/libc.so.6(__libc_start_main+0xf5)[0x7fa224884505]
./pg_validatebackup[0x402049]
=== Memory map: 
0040-00415000 r-xp  fd:03 4044545 
/home/centos/pg13_bk_mani/edb/edbpsql/bin/pg_validatebackup
00614000-00615000 r--p 00014000 fd:03 4044545 
/home/centos/pg13_bk_mani/edb/edbpsql/bin/pg_validatebackup
00615000-00616000 rw-p 00015000 fd:03 4044545 
/home/centos/pg13_bk_mani/edb/edbpsql/bin/pg_validatebackup
017f3000-01878000 rw-p  00:00 
0  [heap]

7fa21800-7fa218021000 rw-p  00:00 0
7fa218021000-7fa21c00 ---p  00:00 0
7fa21e122000-7fa21e137000 r-xp  fd:03 
141697 /usr/lib64/libgcc_s-4.8.5-20150702.so.1
7fa21e137000-7fa21e336000 ---p 00015000 fd:03 
141697 /usr/lib64/libgcc_s-4.8.5-20150702.so.1
7fa21e336000-7fa21e337000 r--p 00014000 fd:03 
141697 /usr/lib64/libgcc_s-4.8.5-20150702.so.1
7fa21e337000-7fa21e338000 rw-p 00015000 fd:03 
141697 /usr/lib64/libgcc_s-4.8.5-20150702.so.1
7fa21e338000-7fa224862000 r--p  fd:03 
266442 /usr/lib/locale/locale-archive
7fa224862000-7fa224a25000 r-xp  fd:03 
134456 /usr/lib64/libc-2.17.so
7fa224a25000-7fa224c25000 ---p 001c3000 fd:03 
134456 /usr/lib64/libc-2.17.so
7fa224c25000-7fa224c29000 r--p 001c3000 fd:03 
134456 /usr/lib64/libc-2.17.so
7fa224c29000-7fa224c2b000 rw-p 001c7000 fd:03 
134456 /usr/lib64/libc-2.17.so

7fa224c2b000-7fa224c3 rw-p  00:00 0
7fa224c3-7fa224c47000 r-xp  fd:03 
134485 /usr/lib64/libpthread-2.17.so
7fa224c47000-7fa224e46000 ---p 00017000 fd:03 
134485 /usr/lib64/libpthread-2.17.so
7fa224e46000-7fa224e47000 r--p 00016000 fd:03 
134485 /usr/lib64/libpthread-2.17.so
7fa224e47000-7fa224e48000 rw-p 00017000 fd:03 
134485 /usr/lib64/libpthread-2.17.so

7fa224e48000-7fa224e4c000 rw-p  00:00 0
7fa224e4c000-7fa224e9 r-xp  fd:03 4044478 
/home/centos/pg13_bk_mani/edb/edbpsql/lib/libpq.so.5.13
7fa224e9-7fa22509 ---p 00044000 fd:03 4044478 
/home/centos/pg13_bk_mani/edb/edbpsql/lib/libpq.so.5.13
7fa22509-7fa225093000 r--p 00044000 fd:03 4044478 
/home/centos/pg13_bk_mani/edb/edbpsql/lib/libpq.so.5.13
7fa225093000-7fa225094000 rw-p 00047000 fd:03 4044478 
/home/centos/pg13_bk_mani/edb/edbpsql/lib/libpq.so.5.13
7fa225094000-7fa2250b6000 r-xp  fd:03 
130333 /usr/lib64/ld-2.17.so

7fa22527d000-7fa2252a2000 rw-p  00:00 0
7fa2252b3000-7fa2252b5000 rw-p  00:00 0
7fa2252b5000-7fa2252b6000 r--p 00021000 fd:03 
130333 /usr/lib64/ld-2.17.so
7fa2252b6000-7fa2252b7000 rw-p 00022000 fd:03 
130333 /usr/lib64/ld-2.17.so

7fa2252b7000-7fa2252b8000 rw-p  00:00 0
7ffdf354f000-7ffdf357 rw-p  00:00 
0  [stack]
7ffdf3572000-7ffdf3574000 r-xp  00:00 
0  [vdso]
ff60-ff601000 r-xp  00:00 
0  [vsyscall]

Aborted
[centos@tushar-ldap-docker bin]$


I am getting the error message but along with "*** Error in 
`./pg_validatebackup': double free or corruption (!prev): 
0x01850ba0 ***"  messages


Is this expected ?

regards,

On 3/3/20 8:19 PM, tushar wrote:

On 3/3/20 4:04 PM, tushar wrote:
Thanks R

Re: backup manifests

[tushar]

Hi,
Another observation , if i change the ownership of a file which is under 
global/ directory

i.e

[root@tushar-ldap-docker global]# chown enterprisedb 2396

and run the pg_validatebackup command, i am getting this message -

[centos@tushar-ldap-docker bin]$ ./pg_validatebackup 
pg_validatebackup: * manifest_checksum = 
e8cb007bcc9c0deab6eff51cd8d9d9af6af35b86e02f3055e60e70e56737e877
pg_validatebackup: error: could not open file "global/2396": Permission 
denied
*** Error in `./pg_validatebackup': double free or corruption (!prev): 
0x01850ba0 ***

=== Backtrace: =
/lib64/libc.so.6(+0x81679)[0x7fa2248e3679]
./pg_validatebackup[0x401f4c]
/lib64/libc.so.6(__libc_start_main+0xf5)[0x7fa224884505]
./pg_validatebackup[0x402049]
=== Memory map: 
0040-00415000 r-xp  fd:03 4044545 
/home/centos/pg13_bk_mani/edb/edbpsql/bin/pg_validatebackup
00614000-00615000 r--p 00014000 fd:03 4044545 
/home/centos/pg13_bk_mani/edb/edbpsql/bin/pg_validatebackup
00615000-00616000 rw-p 00015000 fd:03 4044545 
/home/centos/pg13_bk_mani/edb/edbpsql/bin/pg_validatebackup
017f3000-01878000 rw-p  00:00 0  
[heap]

7fa21800-7fa218021000 rw-p  00:00 0
7fa218021000-7fa21c00 ---p  00:00 0
7fa21e122000-7fa21e137000 r-xp  fd:03 141697 
/usr/lib64/libgcc_s-4.8.5-20150702.so.1
7fa21e137000-7fa21e336000 ---p 00015000 fd:03 141697 
/usr/lib64/libgcc_s-4.8.5-20150702.so.1
7fa21e336000-7fa21e337000 r--p 00014000 fd:03 141697 
/usr/lib64/libgcc_s-4.8.5-20150702.so.1
7fa21e337000-7fa21e338000 rw-p 00015000 fd:03 141697 
/usr/lib64/libgcc_s-4.8.5-20150702.so.1
7fa21e338000-7fa224862000 r--p  fd:03 266442 
/usr/lib/locale/locale-archive
7fa224862000-7fa224a25000 r-xp  fd:03 134456 
/usr/lib64/libc-2.17.so
7fa224a25000-7fa224c25000 ---p 001c3000 fd:03 134456 
/usr/lib64/libc-2.17.so
7fa224c25000-7fa224c29000 r--p 001c3000 fd:03 134456 
/usr/lib64/libc-2.17.so
7fa224c29000-7fa224c2b000 rw-p 001c7000 fd:03 134456 
/usr/lib64/libc-2.17.so

7fa224c2b000-7fa224c3 rw-p  00:00 0
7fa224c3-7fa224c47000 r-xp  fd:03 134485 
/usr/lib64/libpthread-2.17.so
7fa224c47000-7fa224e46000 ---p 00017000 fd:03 134485 
/usr/lib64/libpthread-2.17.so
7fa224e46000-7fa224e47000 r--p 00016000 fd:03 134485 
/usr/lib64/libpthread-2.17.so
7fa224e47000-7fa224e48000 rw-p 00017000 fd:03 134485 
/usr/lib64/libpthread-2.17.so

7fa224e48000-7fa224e4c000 rw-p  00:00 0
7fa224e4c000-7fa224e9 r-xp  fd:03 4044478 
/home/centos/pg13_bk_mani/edb/edbpsql/lib/libpq.so.5.13
7fa224e9-7fa22509 ---p 00044000 fd:03 4044478 
/home/centos/pg13_bk_mani/edb/edbpsql/lib/libpq.so.5.13
7fa22509-7fa225093000 r--p 00044000 fd:03 4044478 
/home/centos/pg13_bk_mani/edb/edbpsql/lib/libpq.so.5.13
7fa225093000-7fa225094000 rw-p 00047000 fd:03 4044478 
/home/centos/pg13_bk_mani/edb/edbpsql/lib/libpq.so.5.13
7fa225094000-7fa2250b6000 r-xp  fd:03 130333 
/usr/lib64/ld-2.17.so

7fa22527d000-7fa2252a2000 rw-p  00:00 0
7fa2252b3000-7fa2252b5000 rw-p  00:00 0
7fa2252b5000-7fa2252b6000 r--p 00021000 fd:03 130333 
/usr/lib64/ld-2.17.so
7fa2252b6000-7fa2252b7000 rw-p 00022000 fd:03 130333 
/usr/lib64/ld-2.17.so

7fa2252b7000-7fa2252b8000 rw-p  00:00 0
7ffdf354f000-7ffdf357 rw-p  00:00 0  
[stack]
7ffdf3572000-7ffdf3574000 r-xp  00:00 0  
[vdso]
ff60-ff601000 r-xp  00:00 0  
[vsyscall]

Aborted
[centos@tushar-ldap-docker bin]$


I am getting the error message but along with "*** Error in 
`./pg_validatebackup': double free or corruption (!prev): 
0x01850ba0 ***"  messages


Is this expected ?

regards,

On 3/3/20 8:19 PM, tushar wrote:

On 3/3/20 4:04 PM, tushar wrote:
Thanks Robert.  After applying all the 5 patches (v8-00*) against PG 
v13 (commit id -afb5465e0cfce7637066eaaaeecab30b0f23fbe3) , 


There is a scenario where pg_validatebackup is not throwing an error 
if some file deleted from pg_wal/ folder and  but later at the time of 
restoring - we are getting an error


[centos@tushar-ldap-docker bin]$ ./pg_basebackup  -D test1

[centos@tushar-ldap-docker bin]$ ls test1/pg_wal/
00010010  archive_status

[centos@tushar-ldap-docker bin]$ rm -rf test1/pg_wal/*

[centos@tushar-ldap-docker bin]$ ./pg_validatebackup test1
pg_validatebackup: * manifest_checksum = 
88f1ed995c83e86252466a2c88b3e660a69cfc76c169991134b101c4f16c9df7

pg_validatebackup: backup successfully verified

[centos@tushar-ldap-docker bin]$ ./pg_ctl -D test1 start 

Re: backup manifests

[tushar]

On 3/3/20 4:04 PM, tushar wrote:
Thanks Robert.  After applying all the 5 patches (v8-00*) against PG 
v13 (commit id -afb5465e0cfce7637066eaaaeecab30b0f23fbe3) , 


There is a scenario where pg_validatebackup is not throwing an error if 
some file deleted from pg_wal/ folder and  but later at the time of 
restoring - we are getting an error


[centos@tushar-ldap-docker bin]$ ./pg_basebackup  -D test1

[centos@tushar-ldap-docker bin]$ ls test1/pg_wal/
00010010  archive_status

[centos@tushar-ldap-docker bin]$ rm -rf test1/pg_wal/*

[centos@tushar-ldap-docker bin]$ ./pg_validatebackup test1
pg_validatebackup: * manifest_checksum = 
88f1ed995c83e86252466a2c88b3e660a69cfc76c169991134b101c4f16c9df7

pg_validatebackup: backup successfully verified

[centos@tushar-ldap-docker bin]$ ./pg_ctl -D test1 start -o '-p '
waiting for server to start2020-03-02 20:05:22.732 IST [21441] LOG:  
starting PostgreSQL 13devel on x86_64-pc-linux-gnu, compiled by gcc 
(GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2020-03-02 20:05:22.733 IST [21441] LOG:  listening on IPv6 address 
"::1", port 
2020-03-02 20:05:22.733 IST [21441] LOG:  listening on IPv4 address 
"127.0.0.1", port 
2020-03-02 20:05:22.736 IST [21441] LOG:  listening on Unix socket 
"/tmp/.s.PGSQL."
2020-03-02 20:05:22.739 IST [21442] LOG:  database system was 
interrupted; last known up at 2020-03-02 20:04:35 IST
2020-03-02 20:05:22.739 IST [21442] LOG:  creating missing WAL directory 
"pg_wal/archive_status"

2020-03-02 20:05:22.886 IST [21442] LOG:  invalid checkpoint record
2020-03-02 20:05:22.886 IST [21442] FATAL:  could not locate required 
checkpoint record
2020-03-02 20:05:22.886 IST [21442] HINT:  If you are restoring from a 
backup, touch 
"/home/centos/pg13_bk_mani/edb/edbpsql/bin/test1/recovery.signal" and 
add required recovery options.
    If you are not restoring from a backup, try removing the file 
"/home/centos/pg13_bk_mani/edb/edbpsql/bin/test1/backup_label".
    Be careful: removing 
"/home/centos/pg13_bk_mani/edb/edbpsql/bin/test1/backup_label" will 
result in a corrupt cluster if restoring from a backup.
2020-03-02 20:05:22.886 IST [21441] LOG:  startup process (PID 21442) 
exited with exit code 1
2020-03-02 20:05:22.886 IST [21441] LOG:  aborting startup due to 
startup process failure

2020-03-02 20:05:22.889 IST [21441] LOG:  database system is shut down
 stopped waiting
pg_ctl: could not start server
Examine the log output.
[centos@tushar-ldap-docker bin]$

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: backup manifests

[tushar]

On 2/27/20 9:22 PM, Robert Haas wrote:

Here's a new patch set for this feature.


Thanks Robert.  After applying all the 5 patches (v8-00*) against PG v13 
(commit id -afb5465e0cfce7637066eaaaeecab30b0f23fbe3) ,


There are few issues/observations

1)Getting segmentation fault error if  we try pg_validatebackup against  
a valid backup_manifest file but data directory path is WRONG


[centos@tushar-ldap-docker bin]$ ./pg_basebackup -D bk 
--manifest-checksums=sha224


[centos@tushar-ldap-docker bin]$ cp bk/backup_manifest /tmp/.

[centos@tushar-ldap-docker bin]$ ./pg_validatebackup -m 
/tmp/backup_manifest    random_directory/
pg_validatebackup: * manifest_checksum = 
f0460cd6aa13cf0c5e35426a41af940a9231e6425cd65115a19778b7abfdaef9
pg_validatebackup: error: could not open directory "random_directory": 
No such file or directory

Segmentation fault

2) when used '-R' option at the time of create base backup

[centos@tushar-ldap-docker bin]$ ./pg_basebackup -D bar -R
[centos@tushar-ldap-docker bin]$ ./pg_validatebackup  bar
pg_validatebackup: * manifest_checksum = 
a195d3a3a82a41200c9ac92c12d764d23c810e7e91b31c44a7d04f67ce012edc
pg_validatebackup: error: "standby.signal" is present on disk but not in 
the manifest
pg_validatebackup: error: "postgresql.auto.conf" has size 286 on disk 
but size 88 in the manifest

[centos@tushar-ldap-docker bin]$

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[tushar]

On 2/27/20 9:43 AM, 曾文旌(义从) wrote:

_-- Scenario 2:_
Here I am getting the same error message in both the below cases.
We may add a "global" keyword with GTT related error message.

postgres=# create global temporary table gtt1 (c1 int unique);
CREATE TABLE
postgres=# create temporary table tmp1 (c1 int unique);
CREATE TABLE

postgres=# create temporary table tmp2 (c1 int references gtt1(c1) );
ERROR:  constraints on temporary tables may reference only temporary 
tables


postgres=# create global temporary table gtt2 (c1 int references 
tmp1(c1) );
ERROR:  constraints on temporary tables may reference only temporary 
tables

Fixed in global_temporary_table_v15-pg13.patch



Thanks Wenjing.

This below scenario is not working  i.e even 'on_commit_delete_rows' is 
true then after commit -  rows are NOT removing


postgres=#  create global  temp table foo1(n int) with 
(on_commit_delete_rows='true');

CREATE TABLE
postgres=#
postgres=# begin;
BEGIN
postgres=*# insert into foo1 values (9);
INSERT 0 1
postgres=*# insert into foo1 values (9);
INSERT 0 1
postgres=*# select * from foo1;
 n
---
 9
 9
(2 rows)

postgres=*# commit;
COMMIT
postgres=# select * from foo1;   -- after commit -there should be 0 row 
as on_commit_delete_rows is 'true'

 n
---
 9
 9
(2 rows)

postgres=# \d+ foo1
   Table "public.foo1"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats 
target | Description

+-+---+--+-+-+--+-
 n  | integer |   |  | | plain 
|  |

Access method: heap
Options: on_commit_delete_rows=true

postgres=#

but if user - create table this way then it is working as expected

postgres=#  create global  temp table foo2(n int) *on commit delete rows;*
CREATE TABLE
postgres=# begin; insert into foo2 values (9); insert into foo2 values 
(9); commit; select * from foo2;

BEGIN
INSERT 0 1
INSERT 0 1
COMMIT
 n
---
(0 rows)

postgres=#

i guess , problem is something with this syntax - create global temp 
table foo1(n int) *with (on_commit_delete_rows='true'); *


--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[tushar]

Hi,

I have created two  global temporary tables like this -

Case 1-
postgres=# create global  temp table foo(n int) *with 
(on_c*ommit_delete_rows='true');

CREATE TABLE

Case 2-
postgres=# create global  temp table bar1(n int) *on c*ommit delete rows;
CREATE TABLE


but   if i try to do the same having only 'temp' keyword , Case 2 is 
working fine but getting this error  for case 1 -


postgres=# create   temp table foo1(n int) with 
(on_commit_delete_rows='true');

ERROR:  regular table cannot specifie on_commit_delete_rows
postgres=#

postgres=#  create   temp table bar1(n int) on commit delete rows;
CREATE TABLE

i think this error message need to be more clear .

regards,
tushar

On 2/25/20 7:19 PM, Pavel Stehule wrote/:



út 25. 2. 2020 v 14:36 odesílatel Prabhat Sahu 
mailto:prabhat.s...@enterprisedb.com>> 
napsal:


Hi All,

Please check the below findings on GTT.
_-- Scenario 1:_
Under "information_schema", We are not allowed to create
"temporary table", whereas we can CREATE/DROP "Global Temporary
Table", is it expected ?


It is ok for me. temporary tables should be created only in 
proprietary schema. For GTT there is not risk of collision, so it can 
be created in any schema where are necessary access rights.


Pavel


postgres=# create temporary table information_schema.temp1(c1 int);
ERROR:  cannot create temporary relation in non-temporary schema
LINE 1: create temporary table information_schema.temp1(c1 int);
                               ^

postgres=# create global temporary table
information_schema.temp1(c1 int);
CREATE TABLE

postgres=# drop table information_schema.temp1 ;
DROP TABLE

_-- Scenario 2:_
Here I am getting the same error message in both the below cases.
We may add a "global" keyword with GTT related error message.

postgres=# create global temporary table gtt1 (c1 int unique);
CREATE TABLE
postgres=# create temporary table tmp1 (c1 int unique);
CREATE TABLE

postgres=# create temporary table tmp2 (c1 int references gtt1(c1) );
ERROR:  constraints on temporary tables may reference only
temporary tables

postgres=# create global temporary table gtt2 (c1 int references
tmp1(c1) );
ERROR:  constraints on temporary tables may reference only
temporary tables

Thanks,
Prabhat Sahu

On Tue, Feb 25, 2020 at 2:25 PM 曾文旌(义从)
mailto:wenjing@alibaba-inc.com>>
wrote:




2020年2月24日 下午5:44，Prabhat Sahu
mailto:prabhat.s...@enterprisedb.com>> 写道：

On Fri, Feb 21, 2020 at 9:10 PM 曾文旌(义从)
mailto:wenjing@alibaba-inc.com>> wrote:

Hi,
I have started testing the "Global temporary table" feature,
That's great, I see hope.
from "gtt_v11-pg13.patch". Below is my findings:

-- session 1:
postgres=# create global temporary table gtt1(a int);
CREATE TABLE

-- seeeion 2:
postgres=# truncate gtt1 ;
ERROR:  could not open file "base/13585/t3_16384": No
such file or directory

is it expected?

Oh ,this is a bug, I fixed it.

Thanks for the patch.
I have verified the same, Now the issue is resolved with v12
patch.

Kindly confirm the below scenario:

postgres=# create global temporary table gtt1 (c1 int unique);
CREATE TABLE

postgres=# create global temporary table gtt2 (c1 int
references gtt1(c1) );
ERROR:  referenced relation "gtt1" is not a global temp table

postgres=# create table tab2 (c1 int references gtt1(c1) );
ERROR:  referenced relation "gtt1" is not a global temp table

Thanks,
Prabhat Sahu


GTT supports foreign key constraints
in global_temporary_table_v13-pg13.patch


Wenjing





-- 


With Regards,
Prabhat Kumar Sahu
EnterpriseDB: http://www.enterprisedb.com
<http://www.enterprisedb.com/>



--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [Proposal] Global temporary tables

[tushar]

Hi ,

pg_upgrade  scenario is failing if database is containing  global 
temporary table


=
centos@tushar-ldap-docker bin]$ ./psql postgres
psql (13devel)
Type "help" for help.

postgres=# create global temporary table  t(n int);
CREATE TABLE
postgres=# \q
===

run pg_upgrade -

[centos@tushar-ldap-docker bin]$ ./pg_upgrade -d /tmp/t1/ -D /tmp/t2 -b 
. -B .

Performing Consistency Checks
-
Checking cluster versions    ok
Checking database user is the install user   ok
Checking database connection settings   ok
Checking for prepared transactions ok
Checking for reg* data types in user tables ok
--
--
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
--
Analyzing all rows in the new cluster    ok
Freezing all rows in the new cluster  ok
Deleting files from new pg_xact    ok
--
--
Restoring database schemas in the new cluster
ok
Copying user relation files
  /tmp/t1/base/13585/16384
error while copying relation "public.t": could not open file 
"/tmp/t1/base/13585/16384": No such file or directory

Failure, exiting

regards,

On 2/25/20 7:06 PM, Prabhat Sahu wrote:

Hi All,

Please check the below findings on GTT.
_-- Scenario 1:_
Under "information_schema", We are not allowed to create "temporary 
table", whereas we can CREATE/DROP "Global Temporary Table", is it 
expected ?


postgres=# create temporary table information_schema.temp1(c1 int);
ERROR:  cannot create temporary relation in non-temporary schema
LINE 1: create temporary table information_schema.temp1(c1 int);
                               ^

postgres=# create global temporary table information_schema.temp1(c1 int);
CREATE TABLE

postgres=# drop table information_schema.temp1 ;
DROP TABLE

_-- Scenario 2:_
Here I am getting the same error message in both the below cases.
We may add a "global" keyword with GTT related error message.

postgres=# create global temporary table gtt1 (c1 int unique);
CREATE TABLE
postgres=# create temporary table tmp1 (c1 int unique);
CREATE TABLE

postgres=# create temporary table tmp2 (c1 int references gtt1(c1) );
ERROR:  constraints on temporary tables may reference only temporary 
tables


postgres=# create global temporary table gtt2 (c1 int references 
tmp1(c1) );
ERROR:  constraints on temporary tables may reference only temporary 
tables


Thanks,
Prabhat Sahu

On Tue, Feb 25, 2020 at 2:25 PM 曾文旌(义从) <mailto:wenjing@alibaba-inc.com>> wrote:





2020年2月24日 下午5:44，Prabhat Sahu mailto:prabhat.s...@enterprisedb.com>> 写道：

On Fri, Feb 21, 2020 at 9:10 PM 曾文旌(义从)
mailto:wenjing@alibaba-inc.com>> wrote:

Hi,
I have started testing the "Global temporary table" feature,
That's great, I see hope.
from "gtt_v11-pg13.patch". Below is my findings:

-- session 1:
postgres=# create global temporary table gtt1(a int);
CREATE TABLE

-- seeeion 2:
postgres=# truncate gtt1 ;
ERROR:  could not open file "base/13585/t3_16384": No such
file or directory

is it expected?

Oh ,this is a bug, I fixed it.

Thanks for the patch.
I have verified the same, Now the issue is resolved with v12 patch.

Kindly confirm the below scenario:

postgres=# create global temporary table gtt1 (c1 int unique);
CREATE TABLE

postgres=# create global temporary table gtt2 (c1 int references
gtt1(c1) );
ERROR:  referenced relation "gtt1" is not a global temp table

postgres=# create table tab2 (c1 int references gtt1(c1) );
ERROR:  referenced relation "gtt1" is not a global temp table

Thanks,
Prabhat Sahu


GTT supports foreign key constraints
in global_temporary_table_v13-pg13.patch


Wenjing





--

With Regards,
Prabhat Kumar Sahu
EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com/>



--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [HACKERS] Block level parallel vacuum

[tushar]

On 11/27/19 11:13 PM, Masahiko Sawada wrote:

Thank you for reviewing this patch. All changes you made looks good to me.

I thought I already have posted all v34 patches but didn't, sorry. So
I've attached v35 patch set that incorporated your changes and it
includes Dilip's patch for gist index (0001). These patches can be
applied on top of the current HEAD and make check should pass.
Regards,
While doing testing of this feature against v35- patches ( minus 004) on 
Master ,
getting crash when user connect to server using single mode and try to 
perform vacuum (parallel 1 ) o/p


tushar@localhost bin]$ ./postgres --single -D data/  postgres
2019-12-03 12:49:26.967 +0530 [70300] LOG:  database system was 
interrupted; last known up at 2019-12-03 12:48:51 +0530
2019-12-03 12:49:26.987 +0530 [70300] LOG:  database system was not 
properly shut down; automatic recovery in progress
2019-12-03 12:49:26.990 +0530 [70300] LOG:  invalid record length at 
0/29F1638: wanted 24, got 0

2019-12-03 12:49:26.990 +0530 [70300] LOG:  redo is not required

PostgreSQL stand-alone backend 13devel
backend>
backend> vacuum full;
backend> vacuum (parallel 1);
TRAP: FailedAssertion("IsUnderPostmaster", File: "dsm.c", Line: 444)
./postgres(ExceptionalCondition+0x53)[0x8c6fa3]
./postgres[0x785ced]
./postgres(GetSessionDsmHandle+0xca)[0x49304a]
./postgres(InitializeParallelDSM+0x74)[0x519d64]
./postgres(heap_vacuum_rel+0x18d3)[0x4e47e3]
./postgres[0x631d9a]
./postgres(vacuum+0x444)[0x632f14]
./postgres(ExecVacuum+0x2bb)[0x63369b]
./postgres(standard_ProcessUtility+0x4cf)[0x7b312f]
./postgres[0x7b02c6]
./postgres[0x7b0dd3]
./postgres(PortalRun+0x162)[0x7b1b02]
./postgres[0x7ad874]
./postgres(PostgresMain+0x1002)[0x7aebf2]
./postgres(main+0x1ce)[0x48188e]
/lib64/libc.so.6(__libc_start_main+0xf5)[0x7f4fe6908505]
./postgres[0x481b6a]
Aborted (core dumped)

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

SSL Connection still showing TLSv1.3 even it is disabled in ssl_ciphers

[tushar]

1:!TLSv1.3
(1 row)

postgres=#

Cipher which has been rejected -should not display in the message.

Is this expected ?

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: Minimal logical decoding on standbys

[tushar]

On 07/16/2019 10:56 PM, Andres Freund wrote:

Even after calling pg_logical_slot_get_changes() multiple times? What
does
SELECT * FROM pg_replication_slots; before and after multiple calls return?

Does manually forcing a checkpoint with CHECKPOINT; first on the primary
and then the standby "fix" the issue?

Yes,eventually it gets clean up -after firing multiple times get_changes 
function or checkpoint or even both.

This same behavior we are able to see  on MASTER  -with or without patch.

but is this an old (existing) issue ?

b)pg_wal files are not recycling  and every time it is creating new files
after firing get_changes function

I'm not sure what you mean by this. Are you saying that
pg_logical_slot_get_changes() causes WAL to be written?


No, when i said - created new WAL files , i meant -after each pg_bench 
run NOT after executing  get_changes.


--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

pg_rewind is failing on PG v12 BETA/PG HEAD

[tushar]

Hi,

I found that pg_rewind is failing against  the latest sources of PG 
v12Beta2 / PG 13devel


Steps to reproduce -
=
0)mkdir /tmp/archive_dir
1)Master Setup -> ./initdb -D master  , add these parameters in 
postgresql.conf file -

"
wal_level = hot_standby
wal_log_hints = on
hot_standby = on
archive_mode=on
archive_command='cp %p /tmp/archive_dir/%f'
port=5432
"
Start the server  (./pg_ctl -D master start)
Connect to psql terminal - create table/ insert few rows / select 
pg_switch_wal(); -- fire 3 times


2)Slave Setup  ->  ./pg_basebackup -PR -X stream -c fast -h 127.0.0.1 -U 
centos -p 5432 -D slave


add these parameters in postgresql.conf file of SLAVE-
"
primary_conninfo = 'user=centos host=127.0.0.1 port=5432'
promote_trigger_file = '/tmp/t00.txt'
restore_command='cp  /tmp/archive_dir/%f %p'
port=
"
Start Slave  (./pg_ctl -D slave start)

3)Touch trigger file (touch /tmp/t00.txt)  -> - standby.signal is gone 
from standby directory and now able to insert rows on standby server.

4)stop master ( ./pg_ctl -D master stop)
5)Perform pg_rewind

[centos@mail-arts bin]$ ./pg_rewind -D master/ 
--source-server="host=localhost port= user=centos password=nothing 
dbname=postgres"

pg_rewind: servers diverged at WAL location 0/3000158 on timeline 1
*pg_rewind: error: could not open file 
"master//pg_wal/00010003": No such file or directory*

pg_rewind: fatal: could not find previous WAL record at 0/3000158

Earlier ,i  was getting this below result -

[centos@mail-arts bin]$ ./pg_rewind -D master/ 
--source-server="host=localhost port= user=centos password=edb 
dbname=postgres"

pg_rewind: servers diverged at WAL location 0/3003538 on timeline 1
pg_rewind: rewinding from last common checkpoint at 0/260 on timeline 1

pg_rewind: Done!

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: Minimal logical decoding on standbys

[tushar]

On 07/10/2019 05:12 PM, Amit Khandekar wrote:

All right. Will do that in the next patch set. For now, I have quickly
done the below changes in a single patch again (attached), in order to
get early comments if any.
Thanks Amit for your patch. i am able to see 1 issues  on Standby server 
- (where  logical replication slot created ) ,
a)size of  pg_wal folder  is NOT decreasing even after firing 
get_changes function
b)pg_wal files are not recycling  and every time it is creating new 
files after firing get_changes function


Here are the detailed steps -
create a directory with the name 'archive_dir' under /tmp (mkdir 
/tmp/archive_dir)

*SR setup -*
*Master*
.)Perform initdb  (./initdb -D master --wal-segsize=2)
.)Open postgresql.conf file and add these below parameters  at the end 
of  file

wal_level='logical'
min_wal_size=4MB
max_wal_size=4MB
hot_standby_feedback = on
archive_mode=on
archive_command='cp %p /tmp/archive_dir/%f'
.)Start the server ( /pg_ctl -D master/ start -l logsM -c )
.)Connect to psql , create physical  slot
    ->SELECT * FROM 
pg_create_physical_replication_slot('decoding_standby');

*Standby - *
.)Perform pg_basebackup ( ./pg_basebackup -D standby/ 
--slot=decoding_standby -R -v)
.)Open postgresql.conf file of standby and add these 2 parameters - at 
the end of file

  port=
primary_slot_name = 'decoding_standby'
.)Start the Standby server  ( ./pg_ctl -D standby/ start -l logsS -c )
.)Connect to psql terminal and create logical replication slot
  ->SELECT * from   pg_create_logical_replication_slot('standby', 
'test_decoding');


*MISC steps**-
*.)Connect to master and create table/insert rows ( create table t(n 
int); insert into t (values (1);)
.)Connect to standby and fire get_changes function ( select * from 
pg_logical_slot_get_changes('standby',null,null); )

.)Run pgbench ( ./pgbench -i -s 10 postgres)
.)Check the pg_wal directory size of  STANDBY
[centos@mail-arts bin]$ du -sch standby/pg_wal/
127M    standby/pg_wal/
127M    total
[centos@mail-arts bin]$

.)Connect to standby and fire get_changes function ( select * from 
pg_logical_slot_get_changes('standby',null,null); )

.)Check the pg_wal directory size of STANDBY
[centos@mail-arts bin]$ du -sch standby/pg_wal/
127M    standby/pg_wal/
127M    total
[centos@mail-arts bin]$


.)Restart both master and standby ( ./pg_ctl -D master restart -l logsM 
-c) and (./pg_ctl -D standby restart -l logsS -c )


.)Check the pg_wal directory size of STANDBY
[centos@mail-arts bin]$ du -sch standby/pg_wal/
127M    standby/pg_wal/
127M    total
[centos@mail-arts bin]$

and if we see the pg_wal files ,it is growing rampant and not reusing.

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: Minimal logical decoding on standbys

[tushar]

On 07/01/2019 11:04 AM, Amit Khandekar wrote:

Also, in the updated patch (v11), I have added some scenarios that
verify that slot is dropped when either master wal_level is
insufficient, or when slot is conflicting. Also organized the test
file a bit.


One scenario where replication slot removed even after fixing the 
problem (which Error message suggested to do)


Please refer this below scenario

Master cluster-
postgresql,conf file
wal_level=logical
hot_standby_feedback = on
port=5432

Standby cluster-
postgresql,conf file
wal_level=logical
hot_standby_feedback = on
port=5433

both Master/Slave cluster are up and running and are in SYNC with each other
Create a logical replication slot on SLAVE ( SELECT * from 
pg_create_logical_replication_slot('m', 'test_decoding'); )


change wal_level='hot_standby' on Master postgresql.conf file / restart 
the server

Run get_changes function on Standby -
postgres=# select * from pg_logical_slot_get_changes('m',null,null);
ERROR:  logical decoding on standby requires wal_level >= logical on master

Correct it on Master postgresql.conf file ,i.e set wal_level='logical'  
again / restart the server

and again fire  get_changes function on Standby -
postgres=# select * from pg_logical_slot_get_changes('m',null,null);
*ERROR:  replication slot "m" does not exist

*This looks little weird as slot got dropped/removed internally . i 
guess it should get invalid rather than removed automatically.
Lets user's  delete the slot themself rather than automatically removed  
as a surprise.


--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [pg_rewind] cp: cannot stat ‘pg_wal/RECOVERYHISTORY’: No such file or directory

[tushar]

On 06/10/2019 04:37 PM, Kuntal Ghosh wrote:

When we define a restore command, we tell the server to copy a file a
WAL file from the archive. So, it should be
restore_command='cp tmp/archive_dir1/%f %p'

This is the reason you're getting this following error.


Ohh. Mea Culpa.

Thanks for pointing out.

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [pg_rewind] cp: cannot stat ‘pg_wal/RECOVERYHISTORY’: No such file or directory

[tushar]

Hi,

Is anyone able to reproduce this one ?
Any pointer to solve this would be helpful.

regards,

On 05/27/2019 07:27 PM, tushar wrote:

Hi,

I am getting this below error - after performing pg_rewind when i try 
to start new slave ( which earlier was my master) against PGv12 Beta1.

"
cp: cannot stat ‘pg_wal/RECOVERYHISTORY’: No such file or directory
2019-05-27 18:55:47.387 IST [25500] LOG:  entering standby mode
cp: cannot stat ‘pg_wal/RECOVERYHISTORY’: No such file or directory
cp: cannot stat ‘pg_wal/RECOVERYXLOG’: No such file or directory
"

Steps to reproduce -
=
0)mkdir /tmp/archive_dir1
1)Master Setup -> ./initdb -D master  , add these parameters in 
postgresql.conf file -

"
wal_level = hot_standby
wal_log_hints = on
max_wal_senders = 2
wal_keep_segments = 64
hot_standby = on
archive_mode=on
archive_command='cp %p /tmp//archive_dir1/%f'
port=5432
"
Start the server  (./pg_ctl -D master start)
Connect to psql terminal - create table/ insert few rows

2)Slave Setup  ->  ./pg_basebackup -PR -X stream -c fast -h 127.0.0.1 
-U centos -p 5432 -D slave


add these parameters in postgresql.conf file -
"
primary_conninfo = 'user=centos host=127.0.0.1 port=5432'
promote_trigger_file = '/tmp/s1.txt'
restore_command='cp %p /tmp/archive_dir1/%f'
port=5433
"
Start Slave  (./pg_ctl -D slave start)

3)Touch trigger file (touch /tmp/s1.txt)  -> - standby.signal is gone 
from standby directory and now able to insert rows on standby server.

4)stop master ( ./pg_ctl -D master stop)
5)Perform pg_rewind
[centos@mail-arts bin]$ ./pg_rewind -D master/ 
--source-server="host=localhost port=5433 user=centos password=edb 
dbname=postgres"

pg_rewind: servers diverged at WAL location 0/3003538 on timeline 1
pg_rewind: rewinding from last common checkpoint at 0/260 on 
timeline 1


pg_rewind: Done!

6)Create standby.signal file on master directory ( touch standby.signal)

7)Modify old master/postgresql.conf file -
primary_conninfo = 'user=centos host=127.0.0.1 port=5433'
promote_trigger_file = '/tmp/s1.txt'
restore_command='cp %p /tmp/archive_dir1/%f'
port=5432

8)Try to start the new slave/old master -

[centos@mail-arts bin]$ ./pg_ctl -D m1/ start
waiting for server to start2019-05-27 18:55:47.237 IST [25499] 
LOG:  starting PostgreSQL 12beta1 on x86_64-pc-linux-gnu, compiled by 
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
2019-05-27 18:55:47.237 IST [25499] LOG:  listening on IPv6 address 
"::1", port 5432
2019-05-27 18:55:47.237 IST [25499] LOG:  listening on IPv4 address 
"127.0.0.1", port 5432
2019-05-27 18:55:47.239 IST [25499] LOG:  listening on Unix socket 
"/tmp/.s.PGSQL.5432"
2019-05-27 18:55:47.259 IST [25500] LOG:  database system was 
interrupted while in recovery at log time 2019-05-27 18:53:45 IST
2019-05-27 18:55:47.259 IST [25500] HINT:  If this has occurred more 
than once some data might be corrupted and you might need to choose an 
earlier recovery target.

cp: cannot stat ‘pg_wal/RECOVERYHISTORY’: No such file or directory
2019-05-27 18:55:47.387 IST [25500] LOG:  entering standby mode
cp: cannot stat ‘pg_wal/RECOVERYHISTORY’: No such file or directory
cp: cannot stat ‘pg_wal/RECOVERYXLOG’: No such file or directory
cp: cannot stat ‘pg_wal/RECOVERYXLOG’: No such file or directory
2019-05-27 18:55:47.402 IST [25500] LOG:  redo starts at 0/228
cp: cannot stat ‘pg_wal/RECOVERYXLOG’: No such file or directory
2019-05-27 18:55:47.410 IST [25500] LOG:  invalid record length at 
0/301E740: wanted 24, got 0
2019-05-27 18:55:47.413 IST [25509] FATAL:  the database system is 
starting up
2019-05-27 18:55:47.413 IST [25508] FATAL:  could not connect to the 
primary server: FATAL:  the database system is starting up

cp: cannot stat ‘pg_wal/RECOVERYHISTORY’: No such file or directory
cp: cannot stat ‘pg_wal/RECOVERYXLOG’: No such file or directory
2019-05-27 18:55:47.424 IST [25513] FATAL:  the database system is 
starting up
2019-05-27 18:55:47.425 IST [25512] FATAL:  could not connect to the 
primary server: FATAL:  the database system is starting up

cp: cannot stat ‘pg_wal/RECOVERYHISTORY’: No such file or directory
.cp: cannot stat ‘pg_wal/RECOVERYXLOG’: No such file or directory

Is there anything i need to change/add  to make it work ?

Thanks.



--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

[pg_rewind] cp: cannot stat ‘pg_wal/RECOVERYHISTORY’: No such file or directory

[tushar]

Hi,

I am getting this below error - after performing pg_rewind when i try to 
start new slave ( which earlier was my master) against PGv12 Beta1.

"
cp: cannot stat ‘pg_wal/RECOVERYHISTORY’: No such file or directory
2019-05-27 18:55:47.387 IST [25500] LOG:  entering standby mode
cp: cannot stat ‘pg_wal/RECOVERYHISTORY’: No such file or directory
cp: cannot stat ‘pg_wal/RECOVERYXLOG’: No such file or directory
"

Steps to reproduce -
=
0)mkdir /tmp/archive_dir1
1)Master Setup -> ./initdb -D master  , add these parameters in 
postgresql.conf file -

"
wal_level = hot_standby
wal_log_hints = on
max_wal_senders = 2
wal_keep_segments = 64
hot_standby = on
archive_mode=on
archive_command='cp %p /tmp//archive_dir1/%f'
port=5432
"
Start the server  (./pg_ctl -D master start)
Connect to psql terminal - create table/ insert few rows

2)Slave Setup  ->  ./pg_basebackup -PR -X stream -c fast -h 127.0.0.1 -U 
centos -p 5432 -D slave


add these parameters in postgresql.conf file -
"
primary_conninfo = 'user=centos host=127.0.0.1 port=5432'
promote_trigger_file = '/tmp/s1.txt'
restore_command='cp %p /tmp/archive_dir1/%f'
port=5433
"
Start Slave  (./pg_ctl -D slave start)

3)Touch trigger file (touch /tmp/s1.txt)  -> - standby.signal is gone 
from standby directory and now able to insert rows on standby server.

4)stop master ( ./pg_ctl -D master stop)
5)Perform pg_rewind
[centos@mail-arts bin]$ ./pg_rewind -D master/ 
--source-server="host=localhost port=5433 user=centos password=edb 
dbname=postgres"

pg_rewind: servers diverged at WAL location 0/3003538 on timeline 1
pg_rewind: rewinding from last common checkpoint at 0/260 on timeline 1

pg_rewind: Done!

6)Create standby.signal file on master directory ( touch standby.signal)

7)Modify old master/postgresql.conf file -
primary_conninfo = 'user=centos host=127.0.0.1 port=5433'
promote_trigger_file = '/tmp/s1.txt'
restore_command='cp %p /tmp/archive_dir1/%f'
port=5432

8)Try to start the new slave/old master -

[centos@mail-arts bin]$ ./pg_ctl -D m1/ start
waiting for server to start2019-05-27 18:55:47.237 IST [25499] LOG:  
starting PostgreSQL 12beta1 on x86_64-pc-linux-gnu, compiled by gcc 
(GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
2019-05-27 18:55:47.237 IST [25499] LOG:  listening on IPv6 address 
"::1", port 5432
2019-05-27 18:55:47.237 IST [25499] LOG:  listening on IPv4 address 
"127.0.0.1", port 5432
2019-05-27 18:55:47.239 IST [25499] LOG:  listening on Unix socket 
"/tmp/.s.PGSQL.5432"
2019-05-27 18:55:47.259 IST [25500] LOG:  database system was 
interrupted while in recovery at log time 2019-05-27 18:53:45 IST
2019-05-27 18:55:47.259 IST [25500] HINT:  If this has occurred more 
than once some data might be corrupted and you might need to choose an 
earlier recovery target.

cp: cannot stat ‘pg_wal/RECOVERYHISTORY’: No such file or directory
2019-05-27 18:55:47.387 IST [25500] LOG:  entering standby mode
cp: cannot stat ‘pg_wal/RECOVERYHISTORY’: No such file or directory
cp: cannot stat ‘pg_wal/RECOVERYXLOG’: No such file or directory
cp: cannot stat ‘pg_wal/RECOVERYXLOG’: No such file or directory
2019-05-27 18:55:47.402 IST [25500] LOG:  redo starts at 0/228
cp: cannot stat ‘pg_wal/RECOVERYXLOG’: No such file or directory
2019-05-27 18:55:47.410 IST [25500] LOG:  invalid record length at 
0/301E740: wanted 24, got 0
2019-05-27 18:55:47.413 IST [25509] FATAL:  the database system is 
starting up
2019-05-27 18:55:47.413 IST [25508] FATAL:  could not connect to the 
primary server: FATAL:  the database system is starting up

cp: cannot stat ‘pg_wal/RECOVERYHISTORY’: No such file or directory
cp: cannot stat ‘pg_wal/RECOVERYXLOG’: No such file or directory
2019-05-27 18:55:47.424 IST [25513] FATAL:  the database system is 
starting up
2019-05-27 18:55:47.425 IST [25512] FATAL:  could not connect to the 
primary server: FATAL:  the database system is starting up

cp: cannot stat ‘pg_wal/RECOVERYHISTORY’: No such file or directory
.cp: cannot stat ‘pg_wal/RECOVERYXLOG’: No such file or directory

Is there anything i need to change/add  to make it work ?

Thanks.

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: Minimal logical decoding on standbys

[tushar]

On 04/10/2019 09:39 PM, Andres Freund wrote:

  Have you reproduced this with Amit's latest version?


Yes-it is very much reproducible.

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: Minimal logical decoding on standbys

[tushar]

On 03/13/2019 08:40 PM, tushar wrote:

Hi ,

I am getting a server crash on standby while executing 
pg_logical_slot_get_changes function   , please refer this scenario


Master cluster( ./initdb -D master)
set wal_level='hot_standby in master/postgresql.conf file
start the server , connect to  psql terminal and create a physical 
replication slot ( SELECT * from 
pg_create_physical_replication_slot('p1');)


perform pg_basebackup using --slot 'p1'  (./pg_basebackup -D slave/ -R 
--slot p1 -v))
set wal_level='logical' , hot_standby_feedback=on, 
primary_slot_name='p1' in slave/postgresql.conf file
start the server , connect to psql terminal and create a logical 
replication slot (  SELECT * from 
pg_create_logical_replication_slot('t','test_decoding');)


run pgbench ( ./pgbench -i -s 10 postgres) on master and select 
pg_logical_slot_get_changes on Slave database


postgres=# select * from pg_logical_slot_get_changes('t',null,null);
2019-03-13 20:34:50.274 IST [26817] LOG:  starting logical decoding 
for slot "t"
2019-03-13 20:34:50.274 IST [26817] DETAIL:  Streaming transactions 
committing after 0/6C60, reading WAL from 0/6C28.
2019-03-13 20:34:50.274 IST [26817] STATEMENT:  select * from 
pg_logical_slot_get_changes('t',null,null);
2019-03-13 20:34:50.275 IST [26817] LOG:  logical decoding found 
consistent point at 0/6C28
2019-03-13 20:34:50.275 IST [26817] DETAIL:  There are no running 
transactions.
2019-03-13 20:34:50.275 IST [26817] STATEMENT:  select * from 
pg_logical_slot_get_changes('t',null,null);
TRAP: FailedAssertion("!(data == tupledata + tuplelen)", File: 
"decode.c", Line: 977)

server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: 2019-03-13 
20:34:50.276 IST [26809] LOG:  server process (PID 26817) was 
terminated by signal 6: Aborted



Andres - Do you think - this is an issue which needs to  be fixed ?

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [PG 9.6]make is failing for test_decoding contrib module.

[tushar]

On 03/29/2019 06:12 PM, Robert Haas wrote:

Kashif Jeeshan?


Ohh, Please read - Kashif Zeeshan.  Sorry for the typo.

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Re: [PG 9.6]make is failing for test_decoding contrib module.

[tushar]

On 03/29/2019 06:12 PM, Robert Haas wrote:

On Fri, Mar 29, 2019 at 8:24 AM tushar  wrote:

Found by one of the my colleague - Kashif Jeeshan ,  in PG 9.6 - make is

Kashif Jeeshan?
:-) , actually he is also working  on logical replication on standbys 
testing - whenever he has some bandwidth (On/off)  ..he found one issue .
 i suggested him to see the behavior on PG 9.6/ PG 10 and while doing 
so - got this issue when he  performed make against test_decoding

test_decoding.c:127: error: for each function it appears in.)
make: *** [test_decoding.o] Error 1
[centos@centos-cpula test_decoding]$

I think your tree is not clean, or you haven't built the server
correctly first.  If this were actually broken, the buildfarm would be
red:

https://buildfarm.postgresql.org/cgi-bin/show_status.pl

Try 'git clean -dfx'.


Yes, you are right.

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

[PG 9.6]make is failing for test_decoding contrib module.

[tushar]

Hi,

Found by one of the my colleague - Kashif Jeeshan ,  in PG 9.6 - make is 
failing for test_decoding contrib module.


[centos@centos-cpula test_decoding]$ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv -O2 -fPIC -I. -I. 
-I../../src/include -D_GNU_SOURCE   -c -o test_decoding.o test_decoding.c

In file included from ../../src/include/postgres.h:48,
 from test_decoding.c:13:
../../src/include/utils/elog.h:71:28: error: utils/errcodes.h: No such 
file or directory

In file included from ../../src/include/replication/slot.h:15,
 from ../../src/include/replication/logical.h:12,
 from test_decoding.c:23:
../../src/include/storage/lwlock.h:129:33: error: storage/lwlocknames.h: 
No such file or directory

test_decoding.c: In function ‘pg_decode_startup’:
test_decoding.c:127: error: ‘ERRCODE_INVALID_PARAMETER_VALUE’ undeclared 
(first use in this function)
test_decoding.c:127: error: (Each undeclared identifier is reported only 
once

test_decoding.c:127: error: for each function it appears in.)
make: *** [test_decoding.o] Error 1
[centos@centos-cpula test_decoding]$

--
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company