Re: "invalid contrecord" error on replica

[Kyotaro Horiguchi]

At Sun, 2 May 2021 22:43:44 +0200, Adrien Nayrat  
wrote in 
> I also dumped 000100AA00A1 on the secondary and it
> contains all the records until AA/A1004018.
> 
> It is really weird, I don't understand how the secondary can miss the
> last 2 records of A0? It seems he did not received the
> CHECKPOINT_SHUTDOWN record?
> 
> Any idea?

This seems like stepping on the same issue with [1], in short, the
secondary having received an incomplete record but the primary forgot
of the record after restart.

Specifically, primary was writing a WAL record that starts at A0FFFB70
and continues to A1xx segment. The secondary successfully received
the first half of the record but the primary failed to write (then
send) the last half of the record due to disk full.

At this time it seems that the primary's last completed record ended
at A0FFB70. Then the CHECKPOINT_SHUTDOWN record overwrote the
already-halfly-sent record up to A0FFBE8 while restarting.

On the secondary side, there's only the first half of the record,
which had been forgotten by the primary and the last half starting at
LSN A100 was still the future in the new history on the primary.

After some time the primary reaches A100 but the first record in
the segment is of course disagrees with the history of the secondary.

1: 
https://www.postgresql.org/message-id/CBDDFA01-6E40-46BB-9F98-9340F4379505%40amazon.com

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

Re: Trusty postgresql-client-9.5_9.5.17-1.pgdg14.04+1_amd64.deb removed ?

[Adrian Klaver]

On 5/5/21 3:20 PM, Ashwin Kini wrote:

Thank you very much


Just be aware that these are archived packages, they will not be updated 
with security/bug fixes.




Regards,

Ashwin Kini



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

Re: Trusty postgresql-client-9.5_9.5.17-1.pgdg14.04+1_amd64.deb removed ?

[Adrian Klaver]

On 5/5/21 11:42 AM, Ashwin Kini wrote:

Hi all,

We were able to download trusty postrgres-9.5* debians until today 
morning. I don’t seem them anymore upstream. We are aware support for 
trusty (14.04) is discontinued.


Does this mean we wont have debians anymore in the upstream path


See here:

https://www.postgresql.org/message-id/YBMtd6nRuXyU2zS4%40msg.df7cb.de

"Distributions moving to apt-archive.postgresql.org: jessie wheezy eoan 
disco trusty precise"




http://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-9.5/ 



apt-get download postgresql-client-9.5

Err http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main 
postgresql-client-9.5 amd64 9.5.17-1.pgdg14.04+1


   404  Not Found [IP: 217.196.149.55 80]

E: Failed to fetch 
http://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-9.5/postgresql-client-9.5_9.5.17-1.pgdg14.04+1_amd64.deb  
404  Not Found [IP: 217.196.149.55 80]


Thanks,

Regards,

Ashwin Kini

signature_962795863




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

Trusty postgresql-client-9.5_9.5.17-1.pgdg14.04+1_amd64.deb removed ?

[Ashwin Kini]

Hi all,

We were able to download trusty postrgres-9.5* debians until today morning. I 
don’t seem them anymore upstream. We are aware support for trusty (14.04) is 
discontinued.

Does this mean we wont have debians anymore in the upstream path
http://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-9.5/

apt-get download postgresql-client-9.5
Err http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main 
postgresql-client-9.5 amd64 9.5.17-1.pgdg14.04+1
  404  Not Found [IP: 217.196.149.55 80]
E: Failed to fetch 
http://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-9.5/postgresql-client-9.5_9.5.17-1.pgdg14.04+1_amd64.deb
  404  Not Found [IP: 217.196.149.55 80]

Thanks,

Regards,
Ashwin Kini
[signature_962795863]

Re: Strange behavior of function date_trunc

[Pavel Luzanov]

On 05.05.2021 17:11, Tom Lane wrote:

Tomas Vondra  writes:

On 5/5/21 3:23 PM, Pavel Luzanov wrote:
It is very likely that the date_trunc function in the following 
example is executed for each line of the query. Although it marked 
as a STABLE and could only be called once. 
It could, but that's just an option - the database may do that, but 
it's not required to do it. In this case it might be beneficial, but 
it'd make the planner more complex etc. 
Yeah, there simply is not any provision for caching the results of 
stable functions in the way Pavel seems to be imagining. People have 
played around with patches for that, but nothing's been accepted.


Thank you for the clarification. It's not very obvious, at least for me.

--
Pavel Luzanov
Postgres Professional: https://postgrespro.com
The Russian Postgres Company

Postgresql post_parse_analyze_hook not getting triggered for COMMIT command

[Soumya Prasad Ukil]

Hello All,
I am trying to build a postgres extension and I have installed the following 
hooks using this document 
https://wiki.postgresql.org/images/e/e3/Hooks_in_postgresql.pdf 
The hooks that I have installed in my extension are the followings:   
   - shmem_startup_hook
   - post_parse_analyze_hook
   - ExecutorStart_hook
   - ExecutorRun_hook
   - ExecutorFinish_hook
   - ExecutorEnd_hook
   - ProcessUtility_hook
Post that I am using a java program like below to fire sql statements in a 
transaction:

Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;// insert an actor into the actor table
String SQLInsertActor = "INSERT INTO actor(first_name,last_name) 
VALUES(?,?)";try {
// connect to the database
conn = connect();
conn.setAutoCommit(false);
int i=0;while (i < 5) {
// add actor
pstmt = conn.prepareStatement(SQLInsertActor, 
Statement.RETURN_GENERATED_KEYS);
pstmt.setString(1, actor.getFirstName());
pstmt.setString(2, actor.getLastName());
pstmt.executeUpdate();// commit the transaction if everything 
is fine
conn.commit();
i++;
}
}Now what is happening I am receiving all the hook call backs for INSERT 
statement always. BEGIN and COMMIT being utility statement hooks come for 
parse, process utility hook, . However from second iteration onwards, while 
BEGIN throws parse callback, but COMMIT command does not throw parse callback. 
It directly throws process utility hook.
Can you please help why second iteration onwards, commit does not throw parse 
callback to extension?

Re: Strange behavior of function date_trunc

[Pavel Luzanov]

Hello,

On 05.05.2021 16:55, Tomas Vondra wrote:
Well, it'd not like date_trunc is executed for each row while now() is 
executed only once. The functions are executed for each row in both 
cases, but now() is simply much cheaper - it just returns a value that 
is already calculated, while date_trunc has to parse and truncate the 
value, etc.




Thanks for the explanation.



You can use CTE to execute it just once, I think:

  with x as (select date_trunc('day', '2021-04-01'::timestamptz) as x)
  select * from t where a > (select x from x);



I think it could be even easier with scalar subquery:

EXPLAIN (ANALYZE)
SELECT * FROM generate_series('2021-01-01', '2021-06-01', '1 
s'::interval) AS g(x)

WHERE g.x >= (SELECT date_trunc('day', '2021-05-05'::timestamptz));
  QUERY PLAN
---
 Function Scan on generate_series g  (cost=0.02..12.51 rows=333 
width=8) (actual time=1615.436..1815.724 rows=2332801 loops=1)

   Filter: (x >= $0)
   Rows Removed by Filter: 10713600
   InitPlan 1 (returns $0)
 ->  Result  (cost=0.00..0.01 rows=1 width=8) (actual 
time=0.005..0.005 rows=1 loops=1)

 Planning Time: 0.051 ms
 Execution Time: 1889.434 ms

--
Pavel Luzanov
Postgres Professional: https://postgrespro.com
The Russian Postgres Company

Re: Strange behavior of function date_trunc

[Tom Lane]

Tomas Vondra  writes:
> On 5/5/21 3:23 PM, Pavel Luzanov wrote:
>> It is very likely that the date_trunc function in the following example 
>> is executed for each line of the query. Although it marked as a STABLE 
>> and could only be called once.

> It could, but that's just an option - the database may do that, but it's 
> not required to do it. In this case it might be beneficial, but it'd 
> make the planner more complex etc.

Yeah, there simply is not any provision for caching the results of
stable functions in the way Pavel seems to be imagining.  People
have played around with patches for that, but nothing's been accepted.

> You can use CTE to execute it just once, I think:
>with x as (select date_trunc('day', '2021-04-01'::timestamptz) as x)
>select * from t where a > (select x from x);

Actually it's sufficient to write

select * from t where a > (select date_trunc('day', '2021-04-01'::timestamptz))

Postgres interprets that as an uncorrelated sub-select, so it's only done
once per outer query.  I think that these days, the CTE form would be
flattened into that anyway (without MATERIALIZED).

regards, tom lane

Re: PostgreSQL upgrade from 10 to 12 fails with "ERROR: column r.relhasoids does not exist at character 1616"

[Bruce Momjian]

On Wed, May  5, 2021 at 01:26:43PM +0200, Arne Henrik Segtnan wrote:
> Hi, 
> 
> Thanks a lot for the feedback, which actually solved the problem. After
> executing the below command, upgrade from 10 to 12 worked perfectly fine. 
> 
> pgsqldb=# DROP EXTENSION pg_repack CASCADE;

Great to hear, thanks.

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

  If only the physical world exists, free will is an illusion.

Re: Strange behavior of function date_trunc

[Tomas Vondra]

On 5/5/21 3:23 PM, Pavel Luzanov wrote:

Hello,

It is very likely that the date_trunc function in the following example 
is executed for each line of the query. Although it marked as a STABLE 
and could only be called once.




It could, but that's just an option - the database may do that, but it's 
not required to do it. In this case it might be beneficial, but it'd 
make the planner more complex etc.




EXPLAIN (ANALYZE)
SELECT * FROM generate_series('2021-01-01', '2021-06-01', '1 
s'::interval) AS g(x) WHERE g.x >= date_trunc('day', 
'2021-05-05'::timestamptz);

   QUERY PLAN
--- 

  Function Scan on generate_series g  (cost=0.00..15.00 rows=333 
width=8) (actual time=2801.884..3263.328 rows=2332801 loops=1)
    Filter: (x >= date_trunc('day'::text, '2021-05-05 
00:00:00+03'::timestamp with time zone))

    Rows Removed by Filter: 10713600
  Planning Time: 0.040 ms
  Execution Time: 3336.657 ms

When replacing date_trunc with now, the query is much faster:

EXPLAIN (ANALYZE)
SELECT * FROM generate_series('2021-01-01', '2021-06-01', '1 
s'::interval) AS g(x)

WHERE g.x >= now();
   QUERY PLAN
--- 

  Function Scan on generate_series g  (cost=0.00..15.00 rows=333 
width=8) (actual time=1648.777..1845.430 rows=2275325 loops=1)

    Filter: (x >= now())
    Rows Removed by Filter: 10771076
  Planning Time: 0.039 ms
  Execution Time: 1918.767 ms

The variant with now works almost as fast as with the constant. This 
suggests me that perhaps date_trunc is being executed for every line of 
the query:


EXPLAIN (ANALYZE)
SELECT * FROM generate_series('2021-01-01', '2021-06-01', '1 
s'::interval) AS g(x)

WHERE g.x >= '2021-05-05'::timestamptz;
   QUERY PLAN
--- 

  Function Scan on generate_series g  (cost=0.00..12.50 rows=333 
width=8) (actual time=1628.743..1826.841 rows=2332801 loops=1)

    Filter: (x >= '2021-05-05 00:00:00+03'::timestamp with time zone)
    Rows Removed by Filter: 10713600
  Planning Time: 0.033 ms
  Execution Time: 1901.680 ms

In this regard, I have two questions:
1. How can I find out exactly how many times the date_trunc function has 
been executed? So far, these are just my assumptions.

2. If date_trunc is indeed called multiple times, why is this happening?



Well, it'd not like date_trunc is executed for each row while now() is 
executed only once. The functions are executed for each row in both 
cases, but now() is simply much cheaper - it just returns a value that 
is already calculated, while date_trunc has to parse and truncate the 
value, etc.


You can use CTE to execute it just once, I think:

  with x as (select date_trunc('day', '2021-04-01'::timestamptz) as x)
  select * from t where a > (select x from x);


regards
Tomas

Strange behavior of function date_trunc

[Pavel Luzanov]

Hello,

It is very likely that the date_trunc function in the following example 
is executed for each line of the query. Although it marked as a STABLE 
and could only be called once.


EXPLAIN (ANALYZE)
SELECT * FROM generate_series('2021-01-01', '2021-06-01', '1 
s'::interval) AS g(x) WHERE g.x >= date_trunc('day', 
'2021-05-05'::timestamptz);

  QUERY PLAN
---
 Function Scan on generate_series g  (cost=0.00..15.00 rows=333 
width=8) (actual time=2801.884..3263.328 rows=2332801 loops=1)
   Filter: (x >= date_trunc('day'::text, '2021-05-05 
00:00:00+03'::timestamp with time zone))

   Rows Removed by Filter: 10713600
 Planning Time: 0.040 ms
 Execution Time: 3336.657 ms

When replacing date_trunc with now, the query is much faster:

EXPLAIN (ANALYZE)
SELECT * FROM generate_series('2021-01-01', '2021-06-01', '1 
s'::interval) AS g(x)

WHERE g.x >= now();
  QUERY PLAN
---
 Function Scan on generate_series g  (cost=0.00..15.00 rows=333 
width=8) (actual time=1648.777..1845.430 rows=2275325 loops=1)

   Filter: (x >= now())
   Rows Removed by Filter: 10771076
 Planning Time: 0.039 ms
 Execution Time: 1918.767 ms

The variant with now works almost as fast as with the constant. This 
suggests me that perhaps date_trunc is being executed for every line of 
the query:


EXPLAIN (ANALYZE)
SELECT * FROM generate_series('2021-01-01', '2021-06-01', '1 
s'::interval) AS g(x)

WHERE g.x >= '2021-05-05'::timestamptz;
  QUERY PLAN
---
 Function Scan on generate_series g  (cost=0.00..12.50 rows=333 
width=8) (actual time=1628.743..1826.841 rows=2332801 loops=1)

   Filter: (x >= '2021-05-05 00:00:00+03'::timestamp with time zone)
   Rows Removed by Filter: 10713600
 Planning Time: 0.033 ms
 Execution Time: 1901.680 ms

In this regard, I have two questions:
1. How can I find out exactly how many times the date_trunc function has 
been executed? So far, these are just my assumptions.

2. If date_trunc is indeed called multiple times, why is this happening?

--
Pavel Luzanov
Postgres Professional: https://postgrespro.com
The Russian Postgres Company

Re: PostgreSQL upgrade from 10 to 12 fails with "ERROR: column r.relhasoids does not exist at character 1616"

[Arne Henrik Segtnan]

Hi, 

Thanks a lot for the feedback, which actually solved the problem. After 
executing the below command, upgrade from 10 to 12 worked perfectly fine. 

pgsqldb=# DROP EXTENSION pg_repack CASCADE;


Med vennlig hilsen / Best regards,
 ___
Arne Henrik Segtnan


> 4. mai 2021 kl. 18:52 skrev Bruce Momjian :
> 
> On Tue, May  4, 2021 at 12:43:36PM -0400, Tom Lane wrote:
>> Arne Henrik Segtnan  writes:
>>> When trying to upgrade from PostgreSQL 10 to 12, the installation fails as 
>>> shown below. 
>> 
>>> 2021-05-04 13:06:05.614 CEST [12890] postgres@zabbix_db ERROR:  column 
>>> r.relhasoids does not exist at character 1616
>>> 2021-05-04 13:06:05.614 CEST [12890] postgres@zabbix_db STATEMENT:
>>> -- For binary upgrade, must preserve pg_type oid
>> 
>>> SELECT 
>>> pg_catalog.binary_upgrade_set_next_pg_type_oid('16'::pg_catalog.oid);
>>> -- For binary upgrade, must preserve pg_type array oid
>>> SELECT 
>>> pg_catalog.binary_upgrade_set_next_array_pg_type_oid('162221'::pg_catalog.oid);
>> 
>> 
>>> -- For binary upgrade, must preserve pg_class oids
>>> SELECT 
>>> pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('162220'::pg_catalog.oid);
>> 
>> 
>>> CREATE VIEW "repack"."tables" AS
>>>  SELECT ("r"."oid")::"regclass" AS "relname",
>>> "r"."oid" AS "relid",
>>> "r"."reltoastrelid",
>>> CASE
>>> .
>>> .
>>> .
>> 
>> You didn't show us the rest of the command, but it looks like
>> this view contains a reference to pg_class.relhasoids.  As
>> Bruce noted, that column no longer exists, so you're not going
>> to be able to transparently upgrade this view.
>> 
>> I don't know what this view is from, though the schema name
>> "repack" is suggestive.  Possibly you could drop whatever it's
>> a part of, and then reinstall an updated version after upgrading?
> 
> Yeah, Tom, I think you are on to something.  I couldn't figure out where
> the r.relhasoids was referenced in the rest of the log output, but if it
> is on the lines that are part of CREATE VIEW, it certainly could be the
> case that the view references a pre-PG-12 column of pg_class.  To fix
> it, Tom is right that removing the view then recreating it with PG-12
> pg_class column assumptions is the right fix.  I actually rarely see
> this kind of failure.
> 
> -- 
>  Bruce Momjian  mailto:br...@momjian.us>>
> https://momjian.us 
>  EDB  https://enterprisedb.com 
> 
> 
>  If only the physical world exists, free will is an illusion.