psycopg3: prepared statements

[Daniele Varrazzo]

Hello,

I am gathering ideas about how to implement prepared statements in
psycopg3. A plan is sketched at
.

Feedback is welcome, thank you very much!

-- Daniele

Re: Avoid excessive inlining?

[Philip Semanchuk]

> On Dec 19, 2020, at 12:59 AM, Joel Jacobson  wrote:
> 
> Is there a way to avoid excessive inlining when writing pure SQL functions, 
> without having to use PL/pgSQL?

Hi Joel,
The rules for inlining are here:
https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions

According to those rules, if you declared your SQL function as VOLATILE, then 
Postgres wouldn’t inline it. From your question, I’m not sure if you want to 
have the same function inlined sometimes and not others. I can’t think of a way 
to do that offhand.

Hope this helps,
Philip

Re: Missing rows after migrating from postgres 11 to 12 with logical replication

[Lars Vonk]

Hi Adrian,

Thanks for taking the time to reply!

First to answer your questions:

1) Was there activity on the 12 instance while it was being replicated
> to that could account for the missing(deleted?) rows?
>

No there was no activity other than us doing some queries to check how far
the replication was.

2) Are the logs still available for inspection to see if there where any
> errors thrown?
>

Yes, and we dug into those. And we also found some indications that
something went wrong.

3) Are there FK relationships involved?
>

No

4) How did you determine the rows where missing?


We were alerted by a bug later that day and found that some rows were
missing in the new primary. We did a compare based on primary key and found
that several tables were missing rows. Before the switch we unfortunately
only checked max(id) and did some counts on tables and those all checked
out. We didn't do a count on all tables...

So to come back at the logs:

We dug a little deeper and we did found ERROR logs around the time we ran
the initial copies. During a period of several hours that day we see a
couple of messages like:

ERROR: requested WAL segment 000101F1001D has already been
> removed
>

This message is logged a few times and then no more (perhaps it recovered
from it?)

Other than this error there are no other errors, but unfortunately we never
checked this before migrating to the new primary...
In hindsight not very smart of course, but we never thought of this because:

a) the initial copy and the catching up all seemed fine;
b) in previous attempts when we made some errors we noticed for instance
that the WAL files on the previous primary were kept because the new
primary did not yet process them.
So we assumed when all WAL files are "gone" and the max(id) checks out the
replica is in sync and consistent with the primary;
c) our experience with hotstandby replication is that whenever a WAL
segment is missing it won't skip over it and wait until you restore it. We
assumed (and still assume) that this was also the case with logical
replication;

So the questions we now have is:

1) is it correct that a replica of a logical replication skips over missing
WAL files.
2) if so how can you know that it skipped a wal without looking at the log
files or doing a full count?
3) Is there a fail fast mechanism for logical replication (like hotstandy)
that when a WAL file is missing that it stops with further replication

Regards,
Lars

On Sun, Dec 20, 2020 at 6:58 PM Adrian Klaver 
wrote:

> On 12/20/20 8:33 AM, Lars Vonk wrote:
> > Hi,
> >
> > Just wondering if someone knows how this could have happened? Did we
> > miss out on something when setting up the logical replication? Are there
> > any scenario's in which this could happen (like database restart or
> > anything else?).
> > Or should I report this a bug (although I can't image it is)?
> > We really would like to know how we can prevent this from happening the
> > next time.
> >
> > We still have the old primary, and a snapshot of the current primary
> > around the time we flipped from the old to the new. So we could some
> > digging into the cause, but we don't know what to look for...
> >
>
> Questions I have:
>
> 1) Was there activity on the 12 instance while it was being replicated
> to that could account for the missing(deleted?) rows?
>
> 2) Are the logs still available for inspection to see if there where any
> errors thrown?
>
> 3) Are there FK relationships involved?
>
> 4) How did you determine the rows where missing?
>
>
> > Any help or tips are appreciated.
> >
> > Thanks in advance,
> >
> > Lars
> >
> >
> > On Fri, Dec 18, 2020 at 4:42 PM Lars Vonk  > > wrote:
> >
> > Hi,
> >
> > We migrated from postgres 11 to 12 using logical replication (over
> > local network). Today we noticed that one table is missing 1252 rows
> > after the replication finished and we flipped to the new primary (we
> > still have the old master database so we can recover).
> >
> > We see that these rows were inserted in the table after starting the
> > initial copy of the table. Most of the missing rows seem from new
> > inserts happening **during the initial copy** (1230) and the rest
> > (22) from inserts **during the period the replication ran** (7 days).
> >
> > After further investigation unfortunately more tables have missing
> > rows, all of them are after the initial table copy phase. We took a
> > per-table approach for the replication, starting with creating an
> > empty publication and adding tables via
> >
> > ALTER PUBLICATION pg12_migration ADD TABLE FOO
> >
> > After that we refreshed the publication on the "new postgres 12
> > primary" using
> >
> > ALTER SUBSCRIPTION pg12_migration REFRESH PUBLICATION;
> >
> > We only added new tables after the the initial copy of the previous
> > was done (the internal state was replicating).
> >
> > We nev

pg_upgrade question

[Lu, Dan]

Hi PostgreSQL Admin,

I am trying to find out if there is any step by step instruction to reconcile 
old data dir and upgraded data dir after using "-link" option to do an upgrade.

I ran this to do an upgrade from 11.5 to 12.1: pg_upgrade -d 
/hostname/pg/dev115/data -D /hostname/pg/dev121upg/data --link -b 
/pgdbadevbal800/pg/PostgreSQL-11.5/bin -B 
/pgdbadevbal800/pg/PostgreSQL-12.1/bin -p 1432 -P 2432 -v

postgresdbad:dev115:pgdbadevbal800:> pwd
/hostname/pg

postgresdbad:dev115:pgdbadevbal800:> du -sh dev121upg
2.3Gdev121upg

postgresdbad:dev115:pgdbadevbal800:> du -sh dev115
22G dev115

My goal is to be able to do an in place upgrade from 11.5 to 12.1 using the 
same data dir "/hostname/pg/dev115/data".  Without the "-link" option I need to 
double up the space usage for the instance.  What is the easiest way to 
accomplish this task?

Thanks so much for your help.

Dan



IMPORTANT: The information contained in this email and/or its attachments is 
confidential. If you are not the intended recipient, please notify the sender 
immediately by reply and immediately delete this message and all its 
attachments. Any review, use, reproduction, disclosure or dissemination of this 
message or any attachment by an unintended recipient is strictly prohibited. 
Neither this message nor any attachment is intended as or should be construed 
as an offer, solicitation or recommendation to buy or sell any security or 
other financial instrument. Neither the sender, his or her employer nor any of 
their respective affiliates makes any warranties as to the completeness or 
accuracy of any of the information contained herein or that this message or any 
of its attachments is free of viruses.

Re: Missing rows after migrating from postgres 11 to 12 with logical replication

[Adrian Klaver]

On 12/21/20 12:26 PM, Lars Vonk wrote:

Hi Adrian,

Thanks for taking the time to reply!




2) Are the logs still available for inspection to see if there where
any
errors thrown?


Yes, and we dug into those. And we also found some indications that 
something went wrong.





4) How did you determine the rows where missing?


We were alerted by a bug later that day and found that some rows were 
missing in the new primary. We did a compare based on primary key and 
found that several tables were missing rows. Before the switch we 
unfortunately only checked max(id) and did some counts on tables and 
those all checked out. We didn't do a count on all tables...


So to come back at the logs:

We dug a little deeper and we did found ERROR logs around the time we 
ran the initial copies. During a period of several hours that day we see 
a couple of messages like:


ERROR: requested WAL segment 000101F1001D has already
been removed


What was being run when the above ERROR was triggered?




Regards,
Lars

On Sun, Dec 20, 2020 at 6:58 PM Adrian Klaver 



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

Re: Missing rows after migrating from postgres 11 to 12 with logical replication

[Lars Vonk]

>
> What was being run when the above ERROR was triggered?


The initial copy of a table. Other than that we ran select
pg_size_pretty(pg_relation_size('table_name')) to see the current size of
the table being copied to get a feeling on progress.

And whenever we added a new table to the publication we ran ALTER
SUBSCRIPTION migration REFRESH PUBLICATION; to add any new table to the
subscription. But not around that timestamp, about 50 minutes before the
first occurence of that ERROR. (no ERRORS after prior ALTER SUBSCRIPTIONs).

But after the initial copy's ended there are more ERROR's on different WAL
segments missing. Each missing wal segment is logged as ERROR a couple of
times and then no more. After a couple of hours no errors are logged.

Lars

On Mon, Dec 21, 2020 at 10:23 PM Adrian Klaver 
wrote:

> On 12/21/20 12:26 PM, Lars Vonk wrote:
> > Hi Adrian,
> >
> > Thanks for taking the time to reply!
>
> >
> > 2) Are the logs still available for inspection to see if there where
> > any
> > errors thrown?
> >
> >
> > Yes, and we dug into those. And we also found some indications that
> > something went wrong.
> >
>
> > 4) How did you determine the rows where missing?
> >
> >
> > We were alerted by a bug later that day and found that some rows were
> > missing in the new primary. We did a compare based on primary key and
> > found that several tables were missing rows. Before the switch we
> > unfortunately only checked max(id) and did some counts on tables and
> > those all checked out. We didn't do a count on all tables...
> >
> > So to come back at the logs:
> >
> > We dug a little deeper and we did found ERROR logs around the time we
> > ran the initial copies. During a period of several hours that day we see
> > a couple of messages like:
> >
> > ERROR: requested WAL segment 000101F1001D has already
> > been removed
>
> What was being run when the above ERROR was triggered?
>
>
> >
> > Regards,
> > Lars
> >
> > On Sun, Dec 20, 2020 at 6:58 PM Adrian Klaver 
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Missing rows after migrating from postgres 11 to 12 with logical replication

[Adrian Klaver]

On 12/21/20 2:42 PM, Lars Vonk wrote:

What was being run when the above ERROR was triggered?


The initial copy of a table. Other than that we ran select 
pg_size_pretty(pg_relation_size('table_name')) to see the current size 
of the table being copied to get a feeling on progress.


And whenever we added a new table to the publication we ran ALTER 
SUBSCRIPTION migration REFRESH PUBLICATION; to add any new table to the 
subscription. But not around that timestamp, about 50 minutes before the 
first occurence of that ERROR. (no ERRORS after prior ALTER SUBSCRIPTIONs).


But after the initial copy's ended there are more ERROR's on different 
WAL segments missing. Each missing wal segment is logged as ERROR a 
couple of times and then no more. After a couple of hours no errors are 
logged.


Something was looking for the WAL segment.

Did you have some other replication running on the 11 instance?

In any case what was the command logged just before the ERROR.



Lars




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

Access session level local params from another transaction by process id (PID)

[Stanislav Mudrinski]

Hi,

I have a question regarding session params. We are using them thru SET 
LOCAL call request when connection is popped from pool to set, for 
example userId or API call route for current transaction so that any 
stored procedure can fetch them, if needed.


This reduces number of params for every stored procedure call.

In order to debug system locks, we want to access those values. I have 
found an idea  on https://wiki.postgresql.org/wiki/Lock_Monitoring to use


SET  application_name='%application_name_plus_additional_params%'

to be able to acces those data when we have system locks, but that adds 
anotner call on every transaction.
Is it somehow possible to access session level configuration data for 
specific PID from another transaction?



Kind regards,

Stanislav