Re: errcodes.h and others not generated when compiling 13.6 on M1 Mac

[Tom Lane]

Sam Kidman  writes:
>> Are you using any nondefault make options?  Are you invoking our Makefile
>> from some make script of your own?

> This all came about because we use asdf as a version manager which compiles
> the versions of various programs you want to use. There's potentially a
> problem with the asdf plugin for postgres which I'd like to fix. I couldn't
> make head nor tails of the problem with asdf, so I cloned the source and
> started trying to compile it myself. The error I get when I compile it
> myself and when asdf does it are the same (the problem with header files
> not being generated)

Our manual recommends doing it this way if you invoke PG's build from
an upper-level makefile:

build-postgresql:
$(MAKE) -C postgresql MAKELEVEL=0 all

I don't know anything about asdf, but if it uses make scripts then a
likely explanation for the problem is lack of the "MAKELEVEL=0" bit.

regards, tom lane

Re: errcodes.h and others not generated when compiling 13.6 on M1 Mac

[Tom Lane]

Sam Kidman  writes:
> So for some reason that submake-generated-headers target isn't getting
> called for us.

Hmph.  What "make" version are you using exactly?  Are you using
any nondefault make options?  Are you invoking our Makefile from
some make script of your own?  (There are some hacks involving
toplevel vs not-top-level Makefiles that you might be falling
foul of.)

regards, tom lane

Re: errcodes.h and others not generated when compiling 13.6 on M1 Mac

[Tom Lane]

Sam Kidman  writes:
> Ok, so apparently it's something wrong with our environments? Is it
> possible something is failing silently? Any tips for how to troubleshoot?

Apparently so, but it's not very clear what.  You didn't answer as to
what your build environment is, eg do you have any MacPorts or Homebrew
packages in play.  The missing files are supposed to be generated by
Perl scripts, so I'm wondering if you have a broken Perl installation.
Apple's standard Perl works fine for this, which is why I'm suspicious
that you've got some other Perl installed that for some reason is
less fine.

> BTW I'm not doing anything special when compiling, just git checkout
> REL_13_6, ./configure, ./make (unless I am doing something wrong there?)

That's about what you need, yes.

> Also the README.md makes mention of an INSTALL file that contains
> instructions about prerequisites for compilation but I couldn't find it in
> the repository.

?? There's no README.md in our git tree.  There's no INSTALL there
either, because it's a generated file.  But it's just a flat text
version of the same installation instructions I pointed you to before:

https://www.postgresql.org/docs/13/installation.html

regards, tom lane

Re: Whole Database or Table AES encryption

[Bruce Momjian]

On Thu, Apr 28, 2022 at 11:55:09PM +0100, Aaron Gray wrote:
> On Wed, 27 Apr 2022 at 18:01, Bruce Momjian  wrote:
> >
> > On Wed, Apr 27, 2022 at 05:21:41PM +0100, Aaron Gray wrote:
> > > Hi,
> > >
> > > Is there any way to (virtually) encrypt whole databases or tables with
> > > AES or other types of encryption ?
> >
> > You can use storage encryption via the operating system.  Cybertec has a
> > patch for PG 12 and the community is working on an implementation,
> > perhaps for PG 16.
> 
> I would be very interested in this, are there any further pointers as
> to where to look at this ?
> Is there a repo branch in place working on this or any documentation ?

https://wiki.postgresql.org/wiki/Transparent_Data_Encryption

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson

Re: Whole Database or Table AES encryption

[Mladen Gogala]

On 4/28/22 18:55, Aaron Gray wrote:

On Wed, 27 Apr 2022 at 18:01, Bruce Momjian  wrote:

On Wed, Apr 27, 2022 at 05:21:41PM +0100, Aaron Gray wrote:

Hi,

Is there any way to (virtually) encrypt whole databases or tables with
AES or other types of encryption ?

You can use storage encryption via the operating system.  Cybertec has a
patch for PG 12 and the community is working on an implementation,
perhaps for PG 16.

I would be very interested in this, are there any further pointers as
to where to look at this ?
Is there a repo branch in place working on this or any documentation ?

Cheers,

Aaron


There is a commercial product which supports database encryption, 
including PostgreSQL, it's called Voltage:


https://www.techtarget.com/searchsecurity/feature/HP-Security-Voltages-SecureData-Enterprise-Product-overview

The company that I work for uses it to encrypt data in both Oracle and 
PostgreSQL databases.


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: Whole Database or Table AES encryption

[Aaron Gray]

On Wed, 27 Apr 2022 at 18:01, Bruce Momjian  wrote:
>
> On Wed, Apr 27, 2022 at 05:21:41PM +0100, Aaron Gray wrote:
> > Hi,
> >
> > Is there any way to (virtually) encrypt whole databases or tables with
> > AES or other types of encryption ?
>
> You can use storage encryption via the operating system.  Cybertec has a
> patch for PG 12 and the community is working on an implementation,
> perhaps for PG 16.

I would be very interested in this, are there any further pointers as
to where to look at this ?
Is there a repo branch in place working on this or any documentation ?

Cheers,

Aaron
-- 
Aaron Gray

Independent Open Source Software Engineer, Computer Language
Researcher, Information Theorist, and amateur computer scientist.

Re: Is this a reasonable use for advisory locks?

[Michael Lewis]

How many of these processes do you expect to have running concurrently? How
long does that API call take? Might it be better to update the customer (or
in a separate table as suggested) as "catch up charge process started at"
and then clear that or set completed time in another column to serialize?
That way, no need to hold that db connection while doing external work via
api.

>

Could Postgres warn about incorrect volatility class?

[Philip Semanchuk]

Hi all,
I recently discovered that a custom function that I thought was being inlined 
was not being inlined because I had declared it IMMUTABLE, but the function 
body cast an enum value to text which is a STABLE operator. Once I corrected my 
function's definition to declare it STABLE, Postgres inlined it. 

Since Postgres can apparently determine during query parsing that my function 
has a volatility class mismatch, is there a reason that Postgres can't make the 
same determination and inform me about it when I define the function? In this 
case a helpful message would have been "I sure hope you know what you're 
doing..." :-)

Thanks 
Philip

Re: int8range and index usage for <@ operator

[KOPOSOV Sergey]

On Thu, 2022-04-28 at 12:57 -0400, Tom Lane wrote:
> This email was sent to you by someone outside the University.
> You should only click on links or attachments if you are certain that the
> email is genuine and the content is safe.
>
> KOPOSOV Sergey  writes:
> > I'm trying to understand to is there a possibility to use an index for PG
> > when I have a integer column in a table and I want to execute queries with
> > this
> > integer_column <@ int8range
> > or
> > integer_column  <@ int8multirange
> > in a where clause
>
> There's no support for that at the moment.  In principle the range
> case could be converted to something like "integer_column >= lower_value
> AND integer_column <= upper_value" by attaching a support function to
> <@ and implementing the SupportRequestIndexCondition API.  I think it
> could only work for a plan-time-constant range though, else you'd not know
> whether to use equality or inequality bounds.  (Hmm ... or maybe, use
> equality always and treat it as a lossy conversion?  But infinite bounds
> would still be a headache.)

Thanks for your reply. I will refresh my memory about support functions.

In my case I am certainly thinking of a situation where I'd like to do

integer_column <@ Some_Function()

where Some_function() is a a complicated C function returning a bunch of integer
ranges at runtime depending on arguments.

   S


The University of Edinburgh is a charitable body, registered in Scotland, with 
registration number SC005336. Is e buidheann carthannais a th’ ann an Oilthigh 
Dhùn Èideann, clàraichte an Alba, àireamh clàraidh SC005336.

Re: int8range and index usage for <@ operator

[Tom Lane]

KOPOSOV Sergey  writes:
> I'm trying to understand to is there a possibility to use an index for PG
> when I have a integer column in a table and I want to execute queries with 
> this
> integer_column <@ int8range
> or
> integer_column  <@ int8multirange
> in a where clause

There's no support for that at the moment.  In principle the range
case could be converted to something like "integer_column >= lower_value
AND integer_column <= upper_value" by attaching a support function to
<@ and implementing the SupportRequestIndexCondition API.  I think it
could only work for a plan-time-constant range though, else you'd not know
whether to use equality or inequality bounds.  (Hmm ... or maybe, use
equality always and treat it as a lossy conversion?  But infinite bounds
would still be a headache.)

regards, tom lane

Re: Privilege error with c functions during postgresql upgrade from 11 -> 13

[Adrian Klaver]

On 4/28/22 02:08, Dives, Chloe wrote:

Thanks for your reply. I am using pg_upgrade to do the upgrade


In the Postgres 11 instance using psql what does:

\df+ bt_index_check

return.

In particular under the Access privileges column.

Also the output of:

\du

Going back to your original post, someone who is more familiar with 
pg_upgrade maybe can explain:


Command was: SELECT pg_catalog.binary_upgrade_set_record_init_privs(true);
REVOKE ALL ON FUNCTION "public"."bt_index_check"("index" "regclass") 
FROM PUBLIC;
REVOKE ALL ON FUNCTION "public"."bt_index_check"("index" "regclass") 
FROM "rolename1";

SET SESSION AUTHORIZATION "16416";
GRANT ALL ON FUNCTION "public"."bt_index_check"("index" "regclass") TO 
"16416";

RESET SESSION AUTHORIZATION;
SELECT pg_catalog.binary_upgrade_set_record_init_privs(false);
REVOKE ALL ON FUNCTION "public"."bt_index_check"("index" "regclass") 
FROM "16416";


In other words why the role 16416 was GRANTed ALL then REVOKEd ALL on 
the function?





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

Re: Backing up a DB excluding certain tables

[JORGE MALDONADO]

Yes, I get a warning when running psql as follows. I will search for help
in Google and PostgreSQL documentation. The warning suggests *seeing psql
reference page "Notes for Windows users"*. I will do that. I had not
noticed the warning. Thank you.

[image: image.png]

Regards,
Jorge Maldonado

On Thu, Apr 28, 2022 at 10:13 AM Adrian Klaver 
wrote:

> On 4/28/22 09:57, JORGE MALDONADO wrote:
> > Good day,
> >
> > Here is the output to commands suggested by *Adrian Klaver*. Encoding is
> > the same in both client and server. Also, there are 7 tables I want to
> > exclude.
> > image.png
> > The version of source DB is 11, and target version is 14.
> >
> > Regarding the following comment:
> >
> > *FYI, -n riopoderoso and the riopoderoso in
> > 'riopoderoso."AspNetRoleClaims"' are redundant.*
> >
> >
> > Should I remove the schema name so the exclude switch is *-T "AspNet"*
> > instead of *-T 'riopoderoso."AspNet"'?*
>
> As Tom Lane pointed out earlier -n riopoderoso is going to restrict the
> dump to objects in the riopoderoso schema so schema qualifying the table
> name is not needed.
>
> >
> > I have tried -T 'riopoderoso."Asp*"',  -T 'riopoderoso."Asp"*', -T
> > '"Asp"*', -T "Asp"*' and several other combinations unsuccessfully.
> > Also, I have escaped double quotes, single quotes and both at the same
> > time using the *^* character as documented in several sources.
>
> I don't use Windows much anymore and not all for Postgres so I can't
> comment on this. Someone who does will need to offer guidance.
>
>
> > Can I run the *pg_dump* command in *psql*? I rarely use psql.
>
> No you can't run pg_dump in psql. Speaking of psql, what happens when
> you log in with it? Do you get a code page warning?
>
>
> >
> > Regards,
> > Jorge Maldonado
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Backing up a DB excluding certain tables

[Tom Lane]

JORGE MALDONADO  writes:
>> *FYI, -n riopoderoso and the riopoderoso
>> in'riopoderoso."AspNetRoleClaims"' are redundant.*

> Should I remove the schema name so the exclude switch is *-T "AspNet"*
> instead of *-T 'riopoderoso."AspNet"'?*

No.  As I explained upthread, you'd better use either
*."AspNet"*
or
riopoderoso."AspNet"*
because otherwise the pattern won't match tables that aren't in
pg_dump's restricted search_path.

I continue to think that your problem boils down to one of getting
the quoting around the double-quoted pattern correct.  I'm not a
Windows user so I don't know much about shell quoting rules there.

regards, tom lane

Re: parallel-processing multiple similar query tasks - any example?

[Shaozhong SHI]

Well, I guess that does not work.
Never mind.

Regards,
David


On Thursday, 28 April 2022, Alvaro Herrera  wrote:

> On 2022-Apr-28, Shaozhong SHI wrote:
>
> > Expand and explain please.
>
> No, thanks.
>
> --
> Álvaro Herrera
>

int8range and index usage for <@ operator

[KOPOSOV Sergey]

Hi,

I'm trying to understand to is there a possibility to use an index for PG
when I have a integer column in a table and I want to execute queries with this
integer_column <@ int8range
or
integer_column  <@ int8multirange
in a where clause

Here is an example

***
wsdb=> create temp table xtmp (a bigint, b bigint);
CREATE TABLE
wsdb=> insert INTO xtmp  select
(random()*100)::bigint,(random()*100)::bigint  from
generate_series(0,100);
INSERT 0 101
wsdb=> create index  ON  xtmp(a);
CREATE INDEX
wsdb=> create index  ON  xtmp using gist (a);
CREATE INDEX
wsdb=> analyze xtmp;
ANALYZE
wsdb=> explain select * from xtmp where a <@ int8range(4,10);
 QUERY PLAN

 Seq Scan on xtmp  (cost=0.00..17906.01 rows=5000 width=16)
   Filter: (a <@ '[4,10)'::int8range)
(2 rows)

wsdb=> set enable_seqscan to off;
SET
wsdb=> explain select * from xtmp where a <@ int8range(4,10);
  QUERY PLAN
---
 Seq Scan on xtmp  (cost=100.00..1017906.01 rows=5000 width=16)
   Filter: (a <@ '[4,10)'::int8range)
 JIT:
   Functions: 2
   Options: Inlining true, Optimization true, Expressions true, Deforming true
(5 rows)

***
Note that the <@ operator does not use an index.
Obviously with the int8range that is maybe superfluous usage of int8range
instead of greater/smaller operators, but I'm interested in queries involving
multi-range like queries which also do not seem to use the index

select * from xtmp where a <@ ('{[3,7), [8,9)}'::int8multirange) ;
postgres=# explain select * from xtmp where a <@ ('{[3,7),
[8,9)}'::int8multirange) ;;
 QUERY PLAN

 Seq Scan on xtmp  (cost=100.00..1017906.01 rows=5000 width=16)
   Filter: (a <@ '{[3,7),[8,9)}'::int8multirange)
(2 rows)

I do know that I can solve the issue by creating a functional index on a 'dummy
range' like this:

postgres=# create index  ON  xtmp using gist (int8range(a,a+1));

and execute queries like this:

postgres=# explain select * from xtmp where int8range(a,a+1) && ('{[3,27),
[100,9)}'::int8multirange) ;

That correctly produces the query plan with bitmap index

  QUERY
PLAN

---
 Bitmap Heap Scan on xtmp  (cost=373.79..6050.55 rows=1 width=16)
   Recheck Cond: (int8range(a, (a + 1)) &&
'{[3,27),[100,9)}'::int8multirange)
   ->  Bitmap Index Scan on xtmp_int8range_idx  (cost=0.00..371.29 rows=1
width=0)
 Index Cond: (int8range(a, (a + 1)) &&
'{[3,27),[100,9)}'::int8multirange)
(4 rows)

But is there a way to avoid creating this dummy index on int8range consisting of
one element ? I somehow would have expected that integer <@ int8range operation
should use the index.

Thanks in advance,
  Sergey


PS For the test I've been using PG14.2




The University of Edinburgh is a charitable body, registered in Scotland, with 
registration number SC005336. Is e buidheann carthannais a th’ ann an Oilthigh 
Dhùn Èideann, clàraichte an Alba, àireamh clàraidh SC005336.

RE: Privilege error with c functions during postgresql upgrade from 11 -> 13

[Dives, Chloe]

Thanks for your reply. I am using pg_upgrade to do the upgrade

-Original Message-
From: Adrian Klaver 
Sent: 27 April 2022 19:00
To: Dives, Chloe ; pgsql-general@lists.postgresql.org
Subject: Re: Privilege error with c functions during postgresql upgrade from 11 
-> 13

[EXTERNAL EMAIL]


On 4/27/22 7:51 AM, Dives, Chloe wrote:
> I am doing a test run of upgrading a postgresql instance from v11 to
> v13 and am hitting the following error:
>
> *pg_restore: while PROCESSING TOC:*
>
> *pg_restore: from TOC entry 13902; 0 0 ACL FUNCTION
> "bt_index_check"("index" "regclass") rolename1*
>
> *pg_restore: error: could not execute query: ERROR:  role "16416" does
> not exist*
>


How are you doing the upgrade, using pg_upgrade or pg_dump/pg_restore?

If pg_dump then what version did you use to do the dump 11 or 13.

If you are restoring just a single database did you do pg_dumpall -g to fetch 
the globals(roles, etc) from the 11 instance and then use psql to restore those 
to the 13 instance?

>
> This role does not appear to exist in the database, and I suspect that
> id belonged to one of the dbadmin team who has recently left. I was
> also hitting this same error for pg_buffercache_pages() as well. They
> are both c functions stored in libdir.
>
> Can anyone point me towards where pg_dump is getting these outdated
> permissions from please?
>
> Thanks!



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


This email was sent by and on behalf of GAM Investments. GAM Investments is the 
corporate brand for GAM Holding AG and its direct and indirect subsidiaries. 
These companies may be referred to as ‘GAM’ or ‘GAM Investments’. In the United 
Kingdom, the business of GAM Investments is conducted by GAM (U.K.) Limited 
(No. 01664573) or one or more entities under the control of GAM (U.K.) Limited, 
including the following entities authorised and regulated by the Financial 
Conduct Authority: GAM International Management Limited (No. 01802911), GAM 
London Limited (No. 00874802), GAM Sterling Management Limited (No. 01750352), 
GAM Unit Trust Management Company Limited (No. 2873560) and GAM Systematic LLP 
(No. OC317557). GAM (U.K.) Limited and its regulated entities are registered in 
England and Wales. The registered office and principal place of business of GAM 
(U.K.) Limited and its regulated entities is at 8 Finsbury Circus, London, 
England, EC2M 7GB. The registered office of GAM Systematic LLP is at City 
House, Hills Road, Cambridge, CB2 1RE. This email, and any attachments, is 
confidential and may be privileged or otherwise protected from disclosure. It 
is intended solely for the stated addressee(s) and access to it by any other 
person is unauthorised. If you are not the intended recipient, you must not 
disclose, copy, circulate or in any other way use or rely on the information 
contained herein. If you have received this email in error, please inform us 
immediately and delete all copies of it. See - 
https://www.gam.com/en/legal/email-disclosures-eu/ for further information on 
confidentiality, the risks of non-secure electronic communication, and certain 
disclosures which we are required to make in accordance with applicable 
legislation and regulations. If you cannot access this link, please notify us 
by reply message and we will send the contents to you. GAM Investments will 
collect and use information about you in the course of your interactions with 
us. Full details about the data types we collect and what we use this for and 
your related rights is set out in our online privacy policy at 
https://www.gam.com/en/legal/privacy-policy. Please familiarise yourself with 
this policy and check it from time to time for updates as it supplements this 
notice.

Re: parallel-processing multiple similar query tasks - any example?

[Alvaro Herrera]

On 2022-Apr-28, Shaozhong SHI wrote:

> Expand and explain please.

No, thanks.

-- 
Álvaro Herrera

Re: parallel-processing multiple similar query tasks - any example?

[Shaozhong SHI]

Expand and explain please.
Regards,
David

On Thursday, 28 April 2022, Alvaro Herrera  wrote:

> On 2022-Apr-28, Shaozhong SHI wrote:
>
> > Why sleep(1)?
>
> It is sleeping to show that they are running concurrently.  If it runs
> five sleeps of one second each and the whole command lasts one second,
> then all sleeps ran in parallel.  Had the whole command taken five
> seconds, you would know that the queries ran serially.
>
> > It should be all active - doing work concurrently.
>
> They are all active simultaneously.  You just need to supply your own
> query, without any sleeps.
>
> --
> Álvaro Herrera
>

Re: ERROR: cursor variable must be a simple variable (LINE XX: OPEN vQuery.cursorReturn FOR )

[Tom Lane]

"Burke, William JCollins" 
 writes:
> I encountered the following error when compiling a pgsql function after an 
> upgrade from PostgresSQL 9.3 on RHEL7 to PostgreSQL 12.9 on RHEL8, and could 
> use some help from the community to figure out why.

> ERROR: cursor variable must be a simple variable
> LINE XX: OPEN vQuery.cursorReturn FOR
>   ^

> Based on the statement highlighted above, I understand the error message. 
> However, what I cannot figure out is why we did not get this error during 
> compilation on PostgreSQL 9.3 with RHEL. Why does it work on PG9.3, but not 
> on PG12? I feel like I am missing something.

Hmm, that error check looks about the same as it did then: it's insisting
that the datum be of PLPGSQL_DTYPE_VAR type.  I think the behavior change
is a side-effect of the refactoring that was done awhile back to unify the
treatment of row (named-composite-type) variables with RECORD variables.
A field of a row was a separate variable back then, but now it isn't.

I don't recall whether we noticed this behavior change at the time, but
given the pre-existing documentation disclaimer, I'm not too sad about it.
If somebody did want to relax this restriction, the thing to do would be
to remove the datum type limitation altogether, not just restore bug
compatibility with the old behavior.

regards, tom lane

Re: Backing up a DB excluding certain tables

[Adrian Klaver]

On 4/28/22 09:57, JORGE MALDONADO wrote:

Good day,

Here is the output to commands suggested by *Adrian Klaver*. Encoding is 
the same in both client and server. Also, there are 7 tables I want to 
exclude.

image.png
The version of source DB is 11, and target version is 14.

Regarding the following comment:

*FYI, -n riopoderoso and the riopoderoso in
'riopoderoso."AspNetRoleClaims"' are redundant.*


Should I remove the schema name so the exclude switch is *-T "AspNet"* 
instead of *-T 'riopoderoso."AspNet"'?*


As Tom Lane pointed out earlier -n riopoderoso is going to restrict the 
dump to objects in the riopoderoso schema so schema qualifying the table 
name is not needed.




I have tried -T 'riopoderoso."Asp*"',  -T 'riopoderoso."Asp"*', -T 
'"Asp"*', -T "Asp"*' and several other combinations unsuccessfully. 
Also, I have escaped double quotes, single quotes and both at the same 
time using the *^* character as documented in several sources.


I don't use Windows much anymore and not all for Postgres so I can't 
comment on this. Someone who does will need to offer guidance.




Can I run the *pg_dump* command in *psql*? I rarely use psql.


No you can't run pg_dump in psql. Speaking of psql, what happens when 
you log in with it? Do you get a code page warning?





Regards,
Jorge Maldonado



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

Re: parallel-processing multiple similar query tasks - any example?

[Alvaro Herrera]

On 2022-Apr-28, Shaozhong SHI wrote:

> Why sleep(1)?

It is sleeping to show that they are running concurrently.  If it runs
five sleeps of one second each and the whole command lasts one second,
then all sleeps ran in parallel.  Had the whole command taken five
seconds, you would know that the queries ran serially.

> It should be all active - doing work concurrently.

They are all active simultaneously.  You just need to supply your own
query, without any sleeps.

-- 
Álvaro Herrera

Re: ERROR: cursor variable must be a simple variable (LINE XX: OPEN vQuery.cursorReturn FOR )

[David G. Johnston]

On Thu, Apr 28, 2022 at 8:03 AM Burke, William J Collins <
william.bu...@collins.com> wrote:

> Hello,
>
>
>
> I encountered the following error when compiling a pgsql function after an
> upgrade from PostgresSQL 9.3 on RHEL7 to PostgreSQL 12.9 on RHEL8, and
> could use some help from the community to figure out why.
>
>
>
> ERROR: cursor variable must be a simple variable
>
> LINE XX: OPEN vQuery.cursorReturn FOR
>
>   ^
>
> SQL state: 42804
>
>
>
> After looking through the CURSOR documentation for PG9.3 and PG12, I
> noticed that the following statement is identical for both versions of
> PostgreSQL:
>
>
>
Per testing on db-fiddle this started reporting an error in v11.

I took a peek but didn't see anything directly affecting this; but quite a
bit of technical debt repayment happened that cycle and since the current
behavior matches the documentation, and there were/are no test cases for
this situation, the behavior change isn't surprising.

I'd accept a bug and backpatch solution here, though, if someone wished to
write one.  We currently support a version (10) that allows this code to
execute, seemingly without issue.

David J.

Re: parallel-processing multiple similar query tasks - any example?

[Shaozhong SHI]

No, No.

Why sleep(1)?

It should be all active - doing work concurrently.

Regards,

David

On Thu, 28 Apr 2022 at 16:17, Alvaro Herrera 
wrote:

> On 2022-Apr-28, Shaozhong SHI wrote:
>
> > multiple similar query tasks are as follows:
> >
> > select * from a_table where country ='UK'
> > select * from a_table where country='France'
> > and so on
> >
> > How best to parallel-processing such types of multiple similar query
> tasks?
> >
> > Any example available?
>
> for country in UK France Germany Ireland; do
>   echo "select pg_sleep(1); select * from a_table where country =
> '${country//\'/''/}'"
> done | \
>   xargs -d"\n" -P10 -n1 psql -X -c
>
> Note the ${country/} stuff is a bash-ism.
>
> --
> Álvaro Herrera
>

Re: parallel-processing multiple similar query tasks - any example?

[Alvaro Herrera]

On 2022-Apr-28, Shaozhong SHI wrote:

> multiple similar query tasks are as follows:
> 
> select * from a_table where country ='UK'
> select * from a_table where country='France'
> and so on
> 
> How best to parallel-processing such types of multiple similar query tasks?
> 
> Any example available?

for country in UK France Germany Ireland; do
  echo "select pg_sleep(1); select * from a_table where country = 
'${country//\'/''/}'"
done | \
  xargs -d"\n" -P10 -n1 psql -X -c

Note the ${country/} stuff is a bash-ism.

-- 
Álvaro Herrera

ERROR: cursor variable must be a simple variable (LINE XX: OPEN vQuery.cursorReturn FOR )

[Burke, William J Collins]

Hello,

I encountered the following error when compiling a pgsql function after an 
upgrade from PostgresSQL 9.3 on RHEL7 to PostgreSQL 12.9 on RHEL8, and could 
use some help from the community to figure out why.

ERROR: cursor variable must be a simple variable
LINE XX: OPEN vQuery.cursorReturn FOR
  ^
SQL state: 42804

After looking through the CURSOR documentation for PG9.3 and PG12, I noticed 
that the following statement is identical for both versions of PostgreSQL:

The cursor variable is opened and given the specified query to execute. The 
cursor cannot be open already, and it must have been declared as an unbound 
cursor variable (that is, as a simple refcursor variable). The query must be a 
SELECT, or something else that returns rows (such as EXPLAIN). The query is 
treated in the same way as other SQL commands in PL/pgSQL: PL/pgSQL variable 
names are substituted, and the query plan is cached for possible reuse. When a 
PL/pgSQL variable is substituted into the cursor query, the value that is 
substituted is the one it has at the time of the OPEN; subsequent changes to 
the variable will not affect the cursor's behavior. The SCROLL and NO SCROLL 
options have the same meanings as for a bound cursor.

Based on the statement highlighted above, I understand the error message. 
However, what I cannot figure out is why we did not get this error during 
compilation on PostgreSQL 9.3 with RHEL. Why does it work on PG9.3, but not on 
PG12? I feel like I am missing something.

Below is a sample pgsql function and composite type that are causing the error.

Custom composite type:

CREATE TYPE customCompositeType AS
(
   cursorReturn refcursor,
   code integer,
   message text
);

Function:

CREATE OR REPLACE FUNCTION getData()
  RETURNS record AS
$BODY$
DECLARE
   vQuery  customCompositeType;

BEGIN
   vQuery.cursorReturn := 'cursorName';
   vQuery.code := 0;
   vQuery.message  := 'TEST;

   -- Open Cursor
   OPEN vQuery.cursorReturn FOR
 SELECT column1,
column2,
column3
 FROM sampleTable
 ORDER BY column3 ASC;

   RETURN(vQuery);

END;
$BODY$
  LANGUAGE plpgsql
  COST 100;


I would appreciate any advice or feedback that the community has on this issue.

Thanks,
William

Re: How to set password in psql -h -d -U command line?

[alias]

Don't Do This - PostgreSQL wiki


On Thu, Apr 28, 2022 at 3:13 PM Josha Inglis  wrote:

> https://www.postgresql.org/docs/current/libpq-envars.html
>
> Either set the PGPASSWORD environment variable or set up a .pgpass file
>
> Josha
>
> On Thu, 28 Apr 2022 at 19:33, Shaozhong SHI 
> wrote:
>
>> I tried various ways to set password in psql command line, but got no
>> luck.
>>
>> Can anyone help?
>>
>> Regards,
>>
>> David
>>
> --
> Kind Regards
>
> Josha Inglis
>

Re: How to set password in psql -h -d -U command line?

[Josha Inglis]

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

Either set the PGPASSWORD environment variable or set up a .pgpass file

Josha

On Thu, 28 Apr 2022 at 19:33, Shaozhong SHI  wrote:

> I tried various ways to set password in psql command line, but got no luck.
>
> Can anyone help?
>
> Regards,
>
> David
>
-- 
Kind Regards

Josha Inglis

Re: How to set password in psql -h -d -U command line?

[Joao Miguel Ferreira]

Hello,

I usually use this approach:

PGPASSWORD=secret psql -h 127.0.0.1 -p 5432 -U joao mydatabase

it has the down side that the password is shown in the command. need to be
careful about that aspect


On Thu, Apr 28, 2022 at 10:33 AM Shaozhong SHI 
wrote:

> I tried various ways to set password in psql command line, but got no luck.
>
> Can anyone help?
>
> Regards,
>
> David
>

Aw: How to set password in psql -h -d -U command line?

[Karsten Hilbert]

> I tried various ways to set password in psql command line, but got no luck.

Have you tried all the methods that you showed in your mail or did you try 
others as well ?

Best regards,
Karsten

About postgresql-db Directory

[Junsong Yang]

Hi,

A directory called postgresql-db appeared after the postgres instance was 
deleted. I noticed that the mounting point(/var/lib/pgdata)disappeared. But 
another directory called postgresql-db appeared at /var/lib/postgresql-db which 
preserved all the data in the previous deployment like this 
/var/lib/postgresql-db/pgdata.

Does anyone know what's going on here?
BTW, I'm using postgres 13.6.
Thank you very much.

Kind regards,
Junsong

How to set password in psql -h -d -U command line?

[Shaozhong SHI]

I tried various ways to set password in psql command line, but got no luck.

Can anyone help?

Regards,

David

Re: Replication with Patroni not working after killing secondary and starting again

[Zb B]

> When the secondary starts up it should continue replicating from where
> it stopped. However, it can only do this if the necessary information is
> still available. If WAL files have been deleted in the mean time. it
> can't replay them. There should be error messages in your logs on what
> went wrong

I did another test using different wal_sender_timeout parameter, as the
time of the secondary being shut down was longer than the default 60s for
this parameter.
I was hoping it would help but the result was the same (records were not
replicated to the secondary after the patroni start). Well, I just verified
again that the records were replicated after about 15 minutes to the
secondary, so probably the timeout setting helped, or I was not patient
enough before. Is it normal to wait so long for the replication? (the
original transaction in primary took about 5 minutes and was about 3000
small records). I am providing more details for completeness below:

I get the following errors on the primary DB:
2022-04-28 04:36:50.544 EDT [13794] WARNING:  archive_mode enabled, yet
archive_command is not set
2022-04-28 04:37:34.893 EDT [14755] ERROR:  replication slot
"xyzd3riardb05" does not exist
2022-04-28 04:37:34.893 EDT [14755] STATEMENT:  START_REPLICATION SLOT
"xyzd3riardb05" 0/700 TIMELINE 18
2022-04-28 04:37:34.915 EDT [14756] ERROR:  replication slot
"xyzd3riardb05" does not exist
2022-04-28 04:37:34.915 EDT [14756] STATEMENT:  START_REPLICATION SLOT
"xyzd3riardb05" 0/700 TIMELINE 18
2022-04-28 04:37:39.925 EDT [14763] ERROR:  replication slot
"xyzd3riardb05" does not exist
2022-04-28 04:37:39.925 EDT [14763] STATEMENT:  START_REPLICATION SLOT
"xyzd3riardb05" 0/700 TIMELINE 18
2022-04-28 04:37:44.924 EDT [14768] ERROR:  replication slot
"xyzd3riardb05" does not exist
2022-04-28 04:37:44.924 EDT [14768] STATEMENT:  START_REPLICATION SLOT
"xyzd3riardb05" 0/700 TIMELINE 18
and after some time such errors stop to appear.

and when I execute:
su postgres -c "psql -c \"SELECT * FROM pg_replication_slots;\""

I get the following the slot seems to exist:
 slot_name   | plugin | slot_type | datoid | database | temporary | active
| active_pid | xmin | catalog_xmin | restart_lsn | confirmed_f
lush_lsn | wal_status | safe_wal_size | two_phase
---++---++--+---+++--+--+-+
-++---+---
 xyzd3riardb05 || physical  ||  | f | f
 ||  |  | 0/73289E8   |
 | reserved   |   | f
 pdc2b || physical  ||  | f | f
 ||  |  | 0/726D398   |
 | reserved   |   | f
 pdc2b_standby || physical  ||  | f | f
 ||  |  | 0/726D398   |
 | reserved   |   | f
(3 rows)

And as I said I just verified that the records were replicated after about
15 minutes to the secondary.