Physical replication high shared buffers hits

[Talal Majali]

Hello,
We have been struggling with weird issue with postgresql physical
replication, and i wonder if someone had this problem before, we have two
servers (primary, replica) same hardware spec  connected directly with
switch running postgresql 13.4, with replication lag less than 1 second, we
use physical replication, and one of the tables is heavily loaded with
writes and locks (up to one second), and reads, when we start reading from
this table from the replica we found a weird issue, same query on the
primary is taking few milliseconds, on the replica it grows until we do
vacuum analyze and when we did explain analyze buffers, what we noticed
that there are huge difference in shared buffers hit for this query on the
replica vs the primary, please find the details below:

on primary:
https://explain.depesz.com/s/TuMD
on replica:
https://explain.depesz.com/s/auJp

Note the Buffers shared hit difference between the two queries,
again when we run this query on the replica after vacuuming analyze the
table:
https://explain.depesz.com/s/vodZ

note that the buffers shared hit grows on the replica for this query or any
query related to this table until we do vacuum analyze.

Regards

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

[Sam Kidman]

Hello

I recently upgraded one of our applications to use postgres 13.6. When I
try to compile from a source checkout or by running asdf install (our
version manager which also compiles 13.6) I get the same error message:

../../src/include/utils/elog.h:71:10: fatal error: 'utils/errcodes.h' file
not found

After digging around in the source it looks like it's supposed to be
generated. I can't figure out why this isn't working on my machine though.
It seems to happen on any M1 mac (my colleagues can reproduce it)

Any help on how to fix or debug this further would be appreciated.

Regards, Sam
www.fresho.com

Re: "create function... depends on extension..." not supported. Why?

[Alvaro Herrera]

On 2022-Apr-26, Tom Lane wrote:

> I suppose that "DEPENDS ON EXTENSION" was modeled after the commands
> to control extension membership, which likewise exist only in ALTER
> form because CREATE's behavior for that is hard-wired.  If you wanted
> to hand-wave a lot, you could maybe claim that ownership and extension
> membership/dependency are similar kinds of relationships and so it
> makes sense that the command structures for manipulating them are
> similar.  But TBH that would probably be reverse-engineering an
> explanation.  I think that "we didn't bother" is more nearly the
> situation.

IIRC Abhijit and I discussed this point offline, and our conclusion was
that DEPENDS ON EXTENSION was not of general enough usefulness to
warrant support for it in CREATE FUNCTION -- keeping in mind that we
would need support for it in CREATE INDEX, CREATE TRIGGER, CREATE
MATERIALIZED VIEW as well.

It's probably not that much code.  The effort of writing the code wasn't
the barrier we were optimizing for, but rather for getting the whole
*idea* accepted.

If this feature is suddenly so useful as to *require* support in the
various CREATE commands, that is most satisfying to me and I volunteer
to reviewing patches that implement it.

-- 
Álvaro Herrera   48°01'N 7°57'E  —  https://www.EnterpriseDB.com/

Re: Fresh eyeballs needed: input into error

[Laurenz Albe]

On Tue, 2022-04-26 at 14:31 -0700, Rich Shepard wrote:
> I'm getting a syntax error with an input into statement and I cannot see the
> cause.
> 
> A MWE and the result (N.B.: line one wrapped for readability; it ends with
> 'values'):
> insert into people (person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
> loc_phone_ext,direct_phone,direct_fax,cell_phone,email,active,comment) values 
> (6000,'No','Name',null,404,1,null,null,null,null,null,null,null);
> 
> 2: ERROR:  syntax error at end of input
> LINE 2: (
>   ^
> What do I keep missing?

Another explanation is that there was already something in your query buffer
when you entered that statement, so that together it caused a syntactically
incorrect statement, something like (in psql):

test=> insert
test-> insert into people (person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
loc_phone_ext,direct_phone,direct_fax,cell_phone,email,active,comment) values 
(6000,'No','Name',null,404,1,null,null,null,null,null,null,null);
ERROR:  syntax error at or near "insert"
LINE 2: insert into people (person_nbr,lname,fname,job_title,company...
^

Yours,
Laurenz Albe

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

[Laurenz Albe]

On Wed, 2022-04-27 at 10:05 +1000, Sam Kidman wrote:
> I recently upgraded one of our applications to use postgres 13.6. When I try 
> to compile
> from a source checkout or by running asdf install (our version manager which 
> also compiles 13.6)
> I get the same error message:
> 
> ../../src/include/utils/elog.h:71:10: fatal error: 'utils/errcodes.h' file 
> not found
> 
> After digging around in the source it looks like it's supposed to be 
> generated. I can't
> figure out why this isn't working on my machine though. It seems to happen on 
> any M1 mac
> (my colleagues can reproduce it)
> 
> Any help on how to fix or debug this further would be appreciated.

I'd say that you didn't ./configure the PostgreSQL source.

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

partitioned tables in high OLTP db

[Radoslav Nedyalkov]

Hello,

I'd like to ask for your opinion and experience you might have with
partitioned tables on a production OLTP database.
We have a couple of tables reaching 1T and beyond and liked to have them
partitioned.
We built a pg14 full volume db.
To get an impression of the new performance we're executing most frequent
oltp queries
and comparing plans and execution times with the production "monolithic"
pg12 db.
For most of the queries we managed to get the optimal plans.
Execution times however increased , especially with planning time. Say from
1ms to 6ms/10ms
Typically there are joins between 3-6 partitioned (~40 partitions) tables.

That's the only thing we can examine . No load/stress tests are available.

Something else we should consider?  (I saw lock contention has been noted
in the forum.)

Thank you for your time.

Regards,
Rado

Re: Fresh eyeballs needed: input into error [FIXED]

[Rich Shepard]

On Tue, 26 Apr 2022, Bruce Momjian wrote:


I am curious what OS psql was using that was fixed by a re-login?


Bruce,

This desktop's running Slackware64-14.2.

Regards,

Rich

Re: Fresh eyeballs needed: input into error

[Rich Shepard]

On Wed, 27 Apr 2022, Laurenz Albe wrote:


test=> insert
test-> insert into people (person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
loc_phone_ext,direct_phone,direct_fax,cell_phone,email,active,comment) values
(6000,'No','Name',null,404,1,null,null,null,null,null,null,null);
ERROR:  syntax error at or near "insert"
LINE 2: insert into people (person_nbr,lname,fname,job_title,company...
   ^


Laurenz,


From time to time I mis-type a character or make another error and psql

responds with a different prompt and no explicit explanation. I'll then type
C-c and get the default => prompt back. Your example is appreciated and may
very well have been the cause, but I kept trying at the default prompt.

I'll re-read the psql man page to better understand the different prompts.

Thank you,

Rich

Re: Fresh eyeballs needed: input into error

[Rob Sargent]

> On Apr 27, 2022, at 5:43 AM, Rich Shepard  wrote:
> 
> On Wed, 27 Apr 2022, Laurenz Albe wrote:
> 
> 
> From time to time I mis-type a character or make another error and psql
> responds with a different prompt and no explicit explanation. I'll then type
> C-c and get the default => prompt back. Your example is appreciated and may
> very well have been the cause, but I kept trying at the default prompt.
> 
> I'll re-read the psql man page to better understand the different prompts.
> 
> Thank you,
> 
> Rich
> 
Have you tried \r instead of C-c?

> 

Replication with Patroni not working after killing secondary and starting again

[Zb B]

Hi,
I am new to Patroni and PostgreSQL.We have set up a cluster with etcd (3
nodes), Patroni (2 nodes) and PostgreSQL (2 nodes) with replication from
primary to secondary. Seemed to be working fine and we started some tests.
One of the tests gave us unsatisfactory results. Specifically when we start
a long transaction with multiple inserts (we use remote Java app for that)
and during execution of this transaction we kill the secondary database by
using the following:
first list the processes:
ps aux | egrep '(patroni|postgresql)'
and
uses the kill -9 command for the two processes that have postgres in the
name then the following happens:

1) Java app continues with the long transaction which is OK
2) Patroni is not restarting automatically again on the secondary, which I
assume is as expected (?)
3) When the transaction finishes and we start the patroni on secondary by
using the commands:
systemctl enable patroni
systemctl start patroni

 the database starts on secondary after a while but the replication from
the primary is not working anymore. Trying new transactions does not
replicate the data either. The same set up worked before in other scenarios
- replicated data (e.g. when using short transactions, killing the
secondary, restarting secondary).

Thus my questions:
1) Is it normal that replication stops working if we kill secondary
postgres and start it again using patroni? Do we need to do any additional
steps except the commands above that start patroni?
2) Is it normal that patroni is not started again automatically after we
kill it and postgres on secondary?
3) Or there is something wrong with our setup and the replication should be
recovered automatically after we kill the secondary and start the patroni
again on secondary?

Thanks,

Zbigniew

Re: Fresh eyeballs needed: input into error

[Rich Shepard]

On Wed, 27 Apr 2022, Rob Sargent wrote:


Have you tried \r instead of C-c?


Rob,

No, I haven't. I'll try it to reset the query buffer the next time I make a
mistake entering a command.

Thanks,

Rich

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

[Tom Lane]

Laurenz Albe  writes:
> On Wed, 2022-04-27 at 10:05 +1000, Sam Kidman wrote:
>> I recently upgraded one of our applications to use postgres 13.6. When I try 
>> to compile
>> from a source checkout or by running asdf install (our version manager which 
>> also compiles 13.6)
>> I get the same error message:
>> 
>> ../../src/include/utils/elog.h:71:10: fatal error: 'utils/errcodes.h' file 
>> not found

> I'd say that you didn't ./configure the PostgreSQL source.

No, we dealt with that case long ago: you get

$ make
You need to run the 'configure' program first. See the file
'README.git' for installation instructions, or visit: 

make: *** [all] Error 1

Anyway, 13.6 certainly builds on my M1 laptop, as well as my
M1 buildfarm animal [1], so it's not a case of "it's completely
broken".  But you need the appropriate prerequisites, eg
an Xcode installation [2].  I wonder exactly what configure
options the OP is using, and what's installed on his Mac
beyond bare macOS.

regards, tom lane

[1] 
https://buildfarm.postgresql.org/cgi-bin/show_history.pl?nm=sifaka&br=REL_13_STABLE
[2] 
https://www.postgresql.org/docs/devel/installation-platform-notes.html#INSTALLATION-NOTES-MACOS

Re: "create function... depends on extension..." not supported. Why?

[Tom Lane]

Alvaro Herrera  writes:
> IIRC Abhijit and I discussed this point offline, and our conclusion was
> that DEPENDS ON EXTENSION was not of general enough usefulness to
> warrant support for it in CREATE FUNCTION -- keeping in mind that we
> would need support for it in CREATE INDEX, CREATE TRIGGER, CREATE
> MATERIALIZED VIEW as well.

Yeah.  Quite aside from the code bloat and grammar bloat, my main
fear about that would be the assumption that we could wedge a consistent
syntax for this into all those different sorts of CREATE commands.
At the very least we'd probably find ourselves having to make those
keywords more reserved than they are now.

regards, tom lane

Whole Database or Table AES encryption

[Aaron Gray]

Hi,

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

Regards,
Aaron
-- 
Aaron Gray

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

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

[Dives, Chloe]

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
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";
GRANT ALL ON FUNCTION "public"."bt_index_check"("index" "regclass") TO 
"rolename1";
GRANT ALL ON FUNCTION "public"."bt_index_check"("index" "regclass") TO 
"rolename2";

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!
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: Fresh eyeballs needed: input into error [FIXED]

[Francisco Olarte]

Rich:

On Wed, 27 Apr 2022 at 14:38, Rich Shepard  wrote:
> On Tue, 26 Apr 2022, Bruce Momjian wrote:
> > I am curious what OS psql was using that was fixed by a re-login?
> This desktop's running Slackware64-14.2.

Where you using cut&paste / selection or something similar? Lately
I've been getting weird errors on paste operations due to bracketed
paste, which are puzzling, It seems to have interactions with readline
and other stuff depending on where the cursor is and some other
things. Last I remembered when pasting some things to guile invisible
chars ( paste brackets ) where intercepted by its REPL leading to
bizarre errors. And sometimes after a relogin things are done a bit
different and make problem disappear.




Francisco Olarte.

Re: Fresh eyeballs needed: input into error [FIXED]

[Rich Shepard]

On Wed, 27 Apr 2022, Francisco Olarte wrote:


Where you using cut&paste / selection or something similar? Lately I've
been getting weird errors on paste operations due to bracketed paste,
which are puzzling, It seems to have interactions with readline and other
stuff depending on where the cursor is and some other things. Last I
remembered when pasting some things to guile invisible chars ( paste
brackets ) where intercepted by its REPL leading to bizarre errors. And
sometimes after a relogin things are done a bit different and make problem
disappear.


Francisco,

Yes. I often use the trackball to highlight plain ASCII text, including the
enclosing '...', and paste it to the psql shell command line. Most of the
time it works. When psql presents prompt 2 instead of command success I
re-enter by re-keyboarding. That usually clears up the issue.

Thanks,

Rich

Re: Whole Database or Table AES encryption

[Bruce Momjian]

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.  You can also do column-level encryption on the
server side via pgcrypto, or on the client side.

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

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

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

[Adrian Klaver]

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

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

[Peter J. Holzer]

On 2022-04-27 15:27:34 +0200, Zb B wrote:
> Hi,
> I am new to Patroni and PostgreSQL.We have set up a cluster with etcd (3
> nodes), Patroni (2 nodes) and PostgreSQL (2 nodes) with replication from
> primary to secondary.

Pretty much the setup we have.

> Seemed to be working fine and we started some tests. One of the tests
> gave us unsatisfactory results. Specifically when we start a long
> transaction with multiple inserts (we use remote Java app for that)
> and during execution of this transaction we kill the secondary
> database by using the following:
[...]
>  the database starts on secondary after a while but the replication from the
> primary is not working anymore.
[...]
> Thus my questions:
> 1) Is it normal that replication stops working if we kill secondary postgres
> and start it again using patroni? Do we need to do any additional steps except
> the commands above that start patroni?

No.

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.

> 2) Is it normal that patroni is not started again automatically after we kill
> it and postgres on secondary?

Depends on your system setup. Did you tell systemd to automatically
restart patroni? Patroni obviously can't do anything by itself after
it's been killed.

> 3) Or there is something wrong with our setup and the replication should be
> recovered automatically after we kill the secondary and start the patroni 
> again
> on secondary?

I assume (but you really haven't given us enough information, so I could
be wrong) that you haven't configured a replication slot and you haven't
enough WAL segments to last through the downtime naturally.

hp

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


signature.asc
Description: PGP signature

Re: Backing up a DB excluding certain tables

[JORGE MALDONADO]

Is this the correct way to answer when you say that I must *keep the list
cc'd*? I am not sure if I only have to reply to the
*pgsql-general@lists.postgresql.org
* list or also include individual
emails like yours and Adrian's.

I have tried many ways to make this pg_dump command work without success. I
just do not know what is going on. The last test I performed was as follows
which only excludes 1 table in the *riopoderoso* schema but it did not
work. Such a table is included in the resulting backup plain format file.

*pg_dump -E win1252 -f c:/temp/respaldo.backup -n riopoderoso -F p -h
localhost -p 5432 -U postgres -W -s -T 'riopoderoso."AspNetRoleClaims"'
riopoderoso*

If there are any additional comments please let me know. I will keep
trying. It is important to exclude these tables because they are created
and managed by other means. Such tables are part of the authentication
feature included in ASP.NET Core.

With respect,
Jorge Maldonado


On Tue, Apr 26, 2022 at 6:36 PM Tom Lane  wrote:

> [ please keep the list cc'd ]
>
> JORGE MALDONADO  writes:
> > As for the answer by *Tom Lane*, I am not restoring the DB but only
> getting
> > the backup in plain format. I see that tables that contain "AspNet" in
> > their name are part of the resulting dumped file. For example, the
> > following is part of the resulting backup plain file:
>
> > CREATE TABLE riopoderoso."AspNetRoleClaims" (
> > "Id" integer NOT NULL,
> > "RoleId" character varying(450) NOT NULL,
> > "ClaimType" text,
> > "ClaimValue" text
> > );
>
> Ah.  Now that you actually showed us what you're doing, there are
> two problems:
>
> 1. "aspnet*.*" is trying to match a *schema* name starting with "aspnet",
> not a *table* name.  What you want is more like "*.aspnet*", or possibly
> "riopoderoso.aspnet*".  (You can't just write "aspnet*", because
> riopoderoso isn't going to be in pg_dump's search path, and that pattern
> would only match tables in the search path.)
>
> 2. You're not accounting for case.  Per the discussion of patterns
> in the psql reference manual, to match an upper-case name you'd need
> to spell it with the correct casing and then put double quotes around
> it.
>
> Actually there's a third problem, which is to get the shell to not strip
> the double quotes from the pattern before handing it to pg_dump.
>
> For me, a dump command like
>
> pg_dump -n riopoderoso -T '*."AspNet"*' ...
>
> does what you want.  However, I gather you're doing this on Windows,
> and I'm not sure whether shell command quoting rules are the same there.
> You might need something weird like backslashing the double quotes.
>
> regards, tom lane
>

Re: Backing up a DB excluding certain tables

[David G. Johnston]

On Wed, Apr 27, 2022 at 3:46 PM JORGE MALDONADO 
wrote:

> Is this the correct way to answer when you say that I must *keep the list
> cc'd*? I am not sure if I only have to reply to the 
> *pgsql-general@lists.postgresql.org
> * list or also include individual
> emails like yours and Adrian's.
>
>
Most of us prefer (or at least I don't see many complaints - I do have a
preference for being included specifically) if the individual responders
are kept too (reply-all) but in any case the list needs to be addressed.

The additional convention is to inline post (with trimming) as opposed to
top-posting like you did here.  As a secondary option, bottom-post.

I have tried many ways to make this pg_dump command work without success. I
> just do not know what is going on. The last test I performed was as follows
> which only excludes 1 table in the *riopoderoso* schema but it did not
> work. Such a table is included in the resulting backup plain format file.
>
> *pg_dump -E win1252 -f c:/temp/respaldo.backup -n riopoderoso -F p -h
> localhost -p 5432 -U postgres -W -s -T 'riopoderoso."AspNetRoleClaims"'
> riopoderoso*
>

That doesn't make any sense.

Using a clean testing database are you able to reproduce the problem?

What version of PostgreSQL and from where?

If you substitute in ? for various characters in the name (particularly the
table name portion) can you make it work?  Is there some kind of encoding
difference so the name you are typing in pg_dump and the name stored in the
database, while looking the same, are actually different?  Copy-and-paste
the name from the pg_dump file back into the command line.

David J.

parallel-processing multiple similar query tasks - any example?

[Shaozhong SHI]

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?

Regards,

David

Re: Backing up a DB excluding certain tables

[David G. Johnston]

On Wed, Apr 27, 2022 at 4:16 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Apr 27, 2022 at 3:46 PM JORGE MALDONADO 
> wrote:
>
>> Is this the correct way to answer when you say that I must *keep the
>> list cc'd*? I am not sure if I only have to reply to the 
>> *pgsql-general@lists.postgresql.org
>> * list or also include individual
>> emails like yours and Adrian's.
>>
>>
> Most of us prefer (or at least I don't see many complaints - I do have a
> preference for being included specifically) if the individual responders
> are kept too (reply-all) but in any case the list needs to be addressed.
>
> The additional convention is to inline post (with trimming) as opposed to
> top-posting like you did here.  As a secondary option, bottom-post.
>
> I have tried many ways to make this pg_dump command work without success.
>> I just do not know what is going on. The last test I performed was as
>> follows which only excludes 1 table in the *riopoderoso* schema but it
>> did not work. Such a table is included in the resulting backup plain format
>> file.
>>
>> *pg_dump -E win1252 -f c:/temp/respaldo.backup -n riopoderoso -F p -h
>> localhost -p 5432 -U postgres -W -s -T 'riopoderoso."AspNetRoleClaims"'
>> riopoderoso*
>>
>
> That doesn't make any sense.
>
> Using a clean testing database are you able to reproduce the problem?
>
> What version of PostgreSQL and from where?
>
> If you substitute in ? for various characters in the name (particularly
> the table name portion) can you make it work?  Is there some kind of
> encoding difference so the name you are typing in pg_dump and the name
> stored in the database, while looking the same, are actually different?
> Copy-and-paste the name from the pg_dump file back into the command line.
>
>
Also, which shell are you using?  Maybe it doesn't like single quotes for
arguments/options, only double-quotes, so the single quotes are becoming
part of the argument input?

David J.

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

[David G. Johnston]

On Wed, Apr 27, 2022 at 4:34 PM 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?
>
>
You should search for how to run processes/commands in parallel in whatever
client-side execution environment you want to use.  There isn't anything
specific to PostgreSQL here.

David J.

Re: Backing up a DB excluding certain tables

[Adrian Klaver]

On 4/27/22 15:45, JORGE MALDONADO wrote:
Is this the correct way to answer when you say that I must *keep the 
list cc'd*? I am not sure if I only have to reply to the 
*pgsql-general@lists.postgresql.org 
* list or also include 
individual emails like yours and Adrian's.


I have tried many ways to make this pg_dump command work without 
success. I just do not know what is going on. The last test I performed 
was as follows which only excludes 1 table in the *riopoderoso* schema 
but it did not work. Such a table is included in the resulting backup 
plain format file.


*pg_dump -E win1252 -f c:/temp/respaldo.backup -n riopoderoso -F p -h 
localhost -p 5432 -U postgres -W -s -T 'riopoderoso."AspNetRoleClaims"' 
riopoderoso*


Using the command line client psql what does:

show client_encoding ;
show server_encoding ;

return?

Also in psql what does:

\dt riopoderoso."Asp"*

return?


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




If there are any additional comments please let me know. I will keep 
trying. It is important to exclude these tables because they are created 
and managed by other means. Such tables are part of the authentication 
feature included in ASP.NET  Core.


With respect,
Jorge Maldonado





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

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

[David G. Johnston]

On Wed, Apr 27, 2022 at 4:44 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Apr 27, 2022 at 4:34 PM 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?
>>
>>
> You should search for how to run processes/commands in parallel in
> whatever client-side execution environment you want to use.  There isn't
> anything specific to PostgreSQL here.
>
>
You should also read:

https://www.postgresql.org/docs/current/parallel-query.html

If you want to see when a single query can be executed using parallel
workers.

David J.