Re: RHEL repo package crc mismatches

[Brainmue]

Hello Devrim,

You were absolutely right.
Resynchronising solved the problem.
Now everything is OK again.
Thanks for the quick help.

Greetings
   Michael


4. Mai 2023 20:43, "Devrim Gündüz"  schrieb:

> Hi Michael,
> 
> On Thu, 2023-05-04 at 04:46 +, Brainmue wrote:
> 
>> The problem is fixed in most of the repositories I synchronise, but in
>> one I now have a new one. With the package: postgresql13-odbc-
>> 13.00.-1PGDG.rhel7.x86_64.rpm
>> 
>> For the pgdg13 RHEL 7 repository:
>> 
>> [MIRROR] postgresql13-odbc-13.00.-1PGDG.rhel7.x86_64.rpm:
>> Downloading successful, but checksum doesn't match. Calculated:
>> 2fa1642932c950ca5597d64a129fc78d2fb3909c898ade5f9bff4db73fb39ae5(sha25
>> 6)  Expected:
>> 9ed5b91c12e072d871314bfa5e8ec991bb312f360f7d1e3af8ece78945931900(sha25
>> 6)
>> 
>> It would be great if you could correct that too.
> 
> This package does not exist on main side, I believe you may need to sync
> again.
> 
> Regards,
> --
> Devrim Gündüz
> Open Source Solution Architect, PostgreSQL Major Contributor
> Twitter: @DevrimGunduz , @DevrimGunduzTR

Re: What type of Compiler to SQL? Memory-Image (Load-and-Go) Format?

[Christophe Pettus]

> On May 4, 2023, at 18:00, Wen Yi  wrote:
> 
> Hi team,
> I am a newbie to the postgres.
> When I am studying the compiler,the text book tell me there is there type of 
> compiler.
>   • Assembly Language Format
>   • Relocatable Binary Format
>   • Memory-Image (Load-and-Go) Format
> I check the postgres's sql compiler, and it's achieved by lex & yacc.
> So What type of Compiler to SQL? Is Memory-Image (Load-and-Go) Format ?

Hi,

Those aren't really "types of compilers."  They are different binary output 
formats that a compiler can generate.

PostgreSQL does not have an "SQL compiler" as such.  (It does have Just-in-Time 
compilation for some operations, but that's almost certainly not what you are 
looking for.)  It's an interpreter, in that it transforms the input language to 
an internal representation and then uses that for its execution, rather than 
reducing it all the way to machine language.

Re: What type of Compiler to SQL? Memory-Image (Load-and-Go) Format?

[David G. Johnston]

On Thu, May 4, 2023 at 6:00 PM Wen Yi  wrote:

> Hi team,
> I am a newbie to the postgres.
> When I am studying the compiler,the text book tell me there is there type
> of compiler.
>
>1. Assembly Language Format
>2. Relocatable Binary Format
>3. Memory-Image (Load-and-Go) Format
>
>
>
IIUC (I haven't formally studied compilers), none of the above, or, rather,
not applicable.

I can conjure up an analogy that says the assembly language artifact has a
similar relationship to the operating system kernel as the compiled plan
has to the database executor.

David J.

Re: What type of Compiler to SQL? Memory-Image (Load-and-Go) Format?

[Bruce Momjian]

On Thu, May  4, 2023 at 09:16:20PM -0400, Bruce Momjian wrote:
> Lex and yacc load command-specific structures, or a Query structure for
> SELECT, INSERT, UPDATE, DELETE, MERGE.
> 
> The Query structure is converted into a Plan which is executed by the
> executor.  It is not compiled into assembly language.  See this:
> 
>   https://www.postgresql.org/developer/backend/

Oh, this might help too:

https://momjian.us/main/writings/pgsql/internalpics.pdf

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

  Embrace your flaws.  They make you human, rather than perfect,
  which you will never be.

Re: What type of Compiler to SQL? Memory-Image (Load-and-Go) Format?

[Bruce Momjian]

On Fri, May  5, 2023 at 01:00:37AM +, Wen Yi wrote:
> Hi team,
> I am a newbie to the postgres.
> When I am studying the compiler,the text book tell me there is there type of
> compiler.
> 
>  1. Assembly Language Format
>  2. Relocatable Binary Format
>  3. Memory-Image (Load-and-Go) Format
> 
> I check the postgres's sql compiler, and it's achieved by lex & yacc.
> So What type of Compiler to SQL? Is Memory-Image (Load-and-Go) Format ?

Lex and yacc load command-specific structures, or a Query structure for
SELECT, INSERT, UPDATE, DELETE, MERGE.

The Query structure is converted into a Plan which is executed by the
executor.  It is not compiled into assembly language.  See this:

https://www.postgresql.org/developer/backend/

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

  Embrace your flaws.  They make you human, rather than perfect,
  which you will never be.

What type of Compiler to SQL? Memory-Image (Load-and-Go) Format?

[Wen Yi]

Hi team,
I am a newbie to the postgres.
When I am studying the compiler,the text book tell me there is there type of 
compiler.

  1.  Assembly Language Format
  2.  Relocatable Binary Format
  3.  Memory-Image (Load-and-Go) Format

I check the postgres's sql compiler, and it's achieved by lex & yacc.
So What type of Compiler to SQL? Is Memory-Image (Load-and-Go) Format ?

Thanks in advance!

Yours,
WenYi

Re: "PANIC: could not open critical system index 2662" - twice

[Thomas Munro]

On Fri, May 5, 2023 at 11:15 AM Thomas Munro  wrote:
> What does select
> pg_relation_filepath('pg_class_oid_index') show in the corrupted
> database, base/5/2662 or something else?

Oh, you can't get that far, but perhaps you could share the
pg_filenode.map file?  Or alternatively strace -f PostgreSQL while
it's starting up to see which file it's reading, just to be sure.  One
way to find clues about whether PostgreSQL did something wrong, once
we definitely have the right relfilenode for the index, aside from
examining its contents, would be to search the WAL for references to
that block with pg_waldump.  Maybe you still have enough WAL if it
happened recently?

Re: "PANIC: could not open critical system index 2662" - twice

[Thomas Munro]

On Fri, May 5, 2023 at 11:15 AM Thomas Munro  wrote:
> Now *that* is a piece of
> logic that changed in PostgreSQL 15.  It changed from sector-based
> atomicity assumptions to a directory entry swizzling trick, in commit
> d8cd0c6c95c0120168df93aae095df4e0682a08a.  Hmm.

I spoke too soon, that only changed in 16.  But still, it means there
are two files that could be corrupted here, pg_filenode.map which
might somehow be pointing to the wrong file, and the relation (index)
main fork file.

Re: "PANIC: could not open critical system index 2662" - twice

[Thomas Munro]

On Fri, May 5, 2023 at 6:11 AM Evgeny Morozov
 wrote:
> Meanwhile, what do I do with the existing server, though? Just try to
> drop the problematic DBs again manually?

That earlier link to a FreeBSD thread is surely about bleeding edge
new ZFS stuff that was briefly broken then fixed, being discovered by
people running code imported from OpenZFS master branch into FreeBSD
main branch (ie it's not exactly released, not following the details
but I think it might soon be 2.2?), but you're talking about an LTS
Ubuntu release from 2018, which shipped "ZFS on Linux" version 0.7.5,
unless you installed a newer version somehow?  So it doesn't sound
like it could be related.

That doesn't mean it couldn't be a different ZFS bug though.  While
looking into file system corruption issues that had similar symptoms
on some other file system (which turned out to be a bug in btrfs) I
did bump into a claim that ZFS could product unexpected zeroes in some
mmap coherency scenario, OpenZFS issue #14548.  I don't immediately
see how PostgreSQL could get tangled up with that problem though, as
we aren't doing that...

It seems quite interesting that it's always pg_class_oid_index block 0
(the btree meta-page), which feels more like a PostgreSQL bug, unless
the access pattern of that particular file/block is somehow highly
unusual compared to every other block and tickling bugs elsewhere in
the stack.  How does that file look, in terms of size, and how many
pages in it are zero?  I think it should be called base/5/2662.

Oooh, but this is a relation that goes through
RelationMapOidToFilenumber.  What does select
pg_relation_filepath('pg_class_oid_index') show in the corrupted
database, base/5/2662 or something else?  Now *that* is a piece of
logic that changed in PostgreSQL 15.  It changed from sector-based
atomicity assumptions to a directory entry swizzling trick, in commit
d8cd0c6c95c0120168df93aae095df4e0682a08a.  Hmm.

Re: RHEL repo package crc mismatches

[Devrim Gündüz]

Hi Michael,

On Thu, 2023-05-04 at 04:46 +, Brainmue wrote:
> The problem is fixed in most of the repositories I synchronise, but in
> one I now have a new one. With the package: postgresql13-odbc-
> 13.00.-1PGDG.rhel7.x86_64.rpm
> 
> For the pgdg13 RHEL 7 repository:
> 
> [MIRROR] postgresql13-odbc-13.00.-1PGDG.rhel7.x86_64.rpm:
> Downloading successful, but checksum doesn't match. Calculated:
> 2fa1642932c950ca5597d64a129fc78d2fb3909c898ade5f9bff4db73fb39ae5(sha25
> 6)  Expected:
> 9ed5b91c12e072d871314bfa5e8ec991bb312f360f7d1e3af8ece78945931900(sha25
> 6) 
> 
> It would be great if you could correct that too.

This package does not exist on main side, I believe you may need to sync
again.

Regards,
-- 
Devrim Gündüz
Open Source Solution Architect, PostgreSQL Major Contributor
Twitter: @DevrimGunduz , @DevrimGunduzTR

Re: "PANIC: could not open critical system index 2662" - twice

[Ron]

On 5/4/23 13:10, Evgeny Morozov wrote:
[snip]

I'm now thinking of setting up a dedicated AWS EC2 instance just for
these little DBs that get created by our automated tests. If the problem
happens there as well then that would strongly point towards a bug in
PostgreSQL, wouldn't it?


Many other people besides you would have noticed regular corruption of 
system catalogs.



Meanwhile, what do I do with the existing server, though? Just try to
drop the problematic DBs again manually?


*"Fix the hardware"* is what you do.

--
Born in Arizona, moved to Babylonia.

Re: "PANIC: could not open critical system index 2662" - twice

[Evgeny Morozov]

On 4/05/2023 6:42 pm, Laurenz Albe wrote:
> On Thu, 2023-05-04 at 15:49 +, Evgeny Morozov wrote:
>> Well, the problem happened again! Kind of... This time PG has not
>> crashed with the PANIC error in the subject, but pg_dumping certain DBs
>> again fails with
>>
>>
>> pg_dump: error: connection to server on socket
>> "/var/run/postgresql/.s.PGSQL.5434" failed: FATAL:  index
>> "pg_class_oid_index" contains unexpected zero page at block 0
> If you dumped and restored the database after the last time the error
> happened, there must be a systemic problem.

I dumped and restored the "real" databases I cared about. The tests
databases on which error now happens are new (created 2 days ago).


> Perhaps you have bad hardware, or a problem with a storage driver,
> file system or some other low-level software component.
> It might of course be a PostgreSQL bug too, but it is hard to say
> without a way to reproduce...

I'm now thinking of setting up a dedicated AWS EC2 instance just for
these little DBs that get created by our automated tests. If the problem
happens there as well then that would strongly point towards a bug in
PostgreSQL, wouldn't it? (And if nothing else, at least it won't affect
the more important DBs!)

Meanwhile, what do I do with the existing server, though? Just try to
drop the problematic DBs again manually?

Re: "PANIC: could not open critical system index 2662" - twice

[Laurenz Albe]

On Thu, 2023-05-04 at 15:49 +, Evgeny Morozov wrote:
> Well, the problem happened again! Kind of... This time PG has not
> crashed with the PANIC error in the subject, but pg_dumping certain DBs
> again fails with
> 
> 
> pg_dump: error: connection to server on socket
> "/var/run/postgresql/.s.PGSQL.5434" failed: FATAL:  index
> "pg_class_oid_index" contains unexpected zero page at block 0

If you dumped and restored the database after the last time the error
happened, there must be a systemic problem.

Perhaps you have bad hardware, or a problem with a storage driver,
file system or some other low-level software component.
It might of course be a PostgreSQL bug too, but it is hard to say
without a way to reproduce...

Yours,
Laurenz Albe

Re: "PANIC: could not open critical system index 2662" - twice

[Evgeny Morozov]

On 14/04/2023 10:42 am, Alban Hertroys wrote:
> Your problem coincides with a thread at freebsd-current with very
> similar data corruption after a recent OpenZFS import: blocks of all
> zeroes, but also missing files. So, perhaps these problems are related?
> Apparently, there was a recent fix for a data corruption issue with the 
> block_cloning feature enabled, but people are still seeing corruption even 
> when they never enabled that feature.
>
> I couldn’t really find the start of the thread in the archives, so this one 
> kind of jumps into the middle of the thread at a relevant-looking point:
>
> https://lists.freebsd.org/archives/freebsd-current/2023-April/003446.html

That thread was a bit over my head, I'm afraid, so I can't say if it's
related. I haven't detected any missing files, anyway.


Well, the problem happened again! Kind of... This time PG has not
crashed with the PANIC error in the subject, but pg_dumping certain DBs
again fails with


pg_dump: error: connection to server on socket
"/var/run/postgresql/.s.PGSQL.5434" failed: FATAL:  index
"pg_class_oid_index" contains unexpected zero page at block 0

PG server log contains:

2023-05-03 04:31:49.903 UTC [14724]
postgres@test_behavior_638186279733138190 FATAL:  index
"pg_class_oid_index" contains unexpected zero page at block 0
2023-05-03 04:31:49.903 UTC [14724]
postgres@test_behavior_638186279733138190 HINT:  Please REINDEX it.

The server PID does not change on such a pg_dump attempt, so it appears
that only the backend process for the pg_dump connection crashes this
time. I don't see any disk errors and there haven't been any OS crashes.

This currently happens for two DBs. Both of them are very small DBs
created by automated .NET tests using Npgsql as client. The code creates
such a test DB from a template DB and tries to drop it at the end of the
test. This times out sometimes and on timeout our test code tries to
drop the DB again (while the first drop command is likely still pending
on the server). This second attempt to drop the DB also timed out:

[12:40:39] Npgsql.NpgsqlException : Exception while reading from stream
 > System.TimeoutException : Timeout during reading attempt
   at
Npgsql.NpgsqlConnector.g__ReadMessageLong|194_0(NpgsqlConnector
connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean
readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean
isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior,
Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior,
Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async,
CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()

...
[12:41:41] (same error again for the same DB)

>From looking at old logs it seems like the same thing happened last time
(in April) as well. That's quite an unlikely coincidence if a bad disk
or bad filesystem was to blame, isn't it?

I've tried to reproduce this by re-running those tests over and over,
but without success so far. So what can I do about this? Do I just try
to drop those databases again manually? But what about the next time it
happens? How do I figure out the cause and prevent this problem?

Re: Invoking SQL function while doing CREATE OR REPLACE on it

[Nagendra Mahesh (namahesh)]

Thanks for the insight, Erik.
Turns out, it was indeed liquibase which was:
- dropping the functions in one transaction (inadvertently as the result of a 
DROP TYPE CASCADE)
- immediately followed by the next transaction which did a CREATE OR REPLACE 
FUNCTION which recreated these functions

The tiny gap in time after the first transaction commit, before the second 
transaction could commit was when the function actually ceased to exist.
So, the invocations during that time failed.

Thanks again.

From: Erik Wienhold 
Sent: Thursday, May 4, 2023 2:33 AM
To: Nagendra Mahesh (namahesh) ; 
pgsql-gene...@postgresql.org 
Subject: Re: Invoking SQL function while doing CREATE OR REPLACE on it 
 
> On 03/05/2023 20:17 CEST Nagendra Mahesh (namahesh)  
> wrote:
>
> I have a Postgres 14.4 cluster (AWS Aurora) to which I connect from my
> application using JDBC.
>
> I use liquibase for schema management - not only tables, but also a bunch of
> SQL stored procedures and functions. Basically, there is one liquibase
> changeSet that runs last and executes a set of SQL files which contain stored
> procedures and functions.
>
> CREATE OR REPLACE FUNCTION bar(arg1 text, arg2 text) RETURNS record LANGUAGE 
> "plpgsql" AS '
> BEGIN
>    // function body
> END;
> ';
>
> These functions / procedures are replaced ONLY when there is a change in one /
> more SQL files which are part of this changeSet. (runOnChange: true).
>
> Whenever I do a rolling deployment of my application (say, with a change in
> the function body of bar()), liquibase will execute the CREATE OR REPLACE 
> FUNCTION bar()
> as part of a transaction.
>
> In the few milliseconds while bar() is being replaced, there are other ongoing
> transactions (from other replicas of my application) which are continuously
> trying to invoke bar().
>
> Only in this tiny time window, few transactions fail with the following error:
>
> ERROR: function bar(arg1 => text, arg2 => text) does not exist
>   Hint: No function matches the given name and argument types. You might need 
>to add explicit type casts.
> Position: 4 : errorCode = 42883

CREATE OR REPLACE FUNCTION should be atomic and cannot change the function
signature.  I don't see how a function cannot exist at some point in this case.

Are you sure that Liquibase is not dropping the function before re-creating it?
If Liquibase drops and re-creates the function in separate transactions, the
transactions trying to execute that function may find it dropped when using the
read committed isolation level.

There's also a race condition bug in v14.4 that may be relevant.  It got fixed
in v14.5.  See "Fix race condition when checking transaction visibility" in
https://www.postgresql.org/docs/14/release-14-5.html.

--
Erik

Re: Trigger questions

[David G. Johnston]

On Thu, May 4, 2023 at 7:04 AM Justin  wrote:

>
>
> On Thu, May 4, 2023 at 9:49 AM DAVID ROTH  wrote:
>
>> 1) Can I create a trigger on a view?
>> 2) Do triggers cascade?
>>
>> Say I have an insert trigger on a table.
>> And, I have an insert trigger on a view that references this table
>> If I do an insert on the view, will both triggers fire?
>>
>
> Can not have triggers on Views,  Views use  RULES  which are DO INSTEAD.
> https://www.postgresql.org/docs/current/rules.html
>

Our users need not care or even know about this particular implementation
detail of views.  For them, views are a fundamental concept.

The description of create trigger makes is perfectly clear that views are a
valid "table-like" target for a trigger.

> CREATE TRIGGER creates a new trigger. CREATE OR REPLACE TRIGGER will
either create a new trigger, or replace an existing trigger. The trigger
will be associated with the specified table, view, or foreign table and
will execute the specified function function_name when certain operations
are performed on that table.

https://www.postgresql.org/docs/current/sql-createtrigger.html

Depending on the view definition, the need for a trigger may be removed
since some views are auto-updatable.

David J.

Re: Trigger questions

[Adrian Klaver]

On 5/4/23 07:03, Justin wrote:



On Thu, May 4, 2023 at 9:49 AM DAVID ROTH > wrote:


__
1) Can I create a trigger on a view?
2) Do triggers cascade?

Say I have an insert trigger on a table.
And, I have an insert trigger on a view that references this table
If I do an insert on the view, will both triggers fire?


Can not have triggers on Views,  Views use  RULES  which are DO INSTEAD.
https://www.postgresql.org/docs/current/rules.html 



That is wrong.

See

https://www.postgresql.org/docs/current/sql-createtrigger.html

The following table summarizes which types of triggers may be used on 
tables, views, and foreign tables:




Yes if you have an INSERT/UPDATE/DELETE rule on a view  that inserts 
into a table then that table's triggers will be executed.


Please note RULES should  be avoided beyond the use case for VIEWS.  
RULES are executed very early in the query tree; it is not 
trivial to write rules on Tables.


Thanks


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

Re: Trigger questions

[Justin]

On Thu, May 4, 2023 at 9:49 AM DAVID ROTH  wrote:

> 1) Can I create a trigger on a view?
> 2) Do triggers cascade?
>
> Say I have an insert trigger on a table.
> And, I have an insert trigger on a view that references this table
> If I do an insert on the view, will both triggers fire?
>

Can not have triggers on Views,  Views use  RULES  which are DO INSTEAD.
https://www.postgresql.org/docs/current/rules.html

Yes if you have an INSERT/UPDATE/DELETE rule on a view  that inserts into a
table then that table's triggers will be executed.

Please note RULES should  be avoided beyond the use case for VIEWS.  RULES
are executed very early in the query tree; it is not trivial to write rules
on Tables.

Thanks

Trigger questions

[DAVID ROTH]

1) Can I create a trigger on a view?
2) Do triggers cascade?

Say I have an insert trigger on a table.
And, I have an insert trigger on a view that references this table
If I do an insert on the view, will both triggers fire?

Re: The logfile stop upgrade after a vim write

[Erik Wienhold]

> On 04/05/2023 11:54 CEST lz ma  wrote:
>
> 1. pg_ctl -D data -l MyLog
> 2. vim MyLog : add some words, save and exit
> 3. after vim operation, MyLog will never upgrade except restart server
> I know it caused by file descripter only open once at the start by postgres,
> and vim operation rename the file to MyLog~, so postgres can't upgrade

set backupcopy=yes in .vimrc

But why would you edit active log files?

--
Erik

The logfile stop upgrade after a vim write

[lz ma]

  1.   pg_ctl -D data -l  MyLog
  2.   vim MyLog : add some words, save and exit
  3.  after vim operation, MyLog will never upgrade except restart server

I know it caused by file descripter only open once at the start by postgres, 
and vim operation rename the file to MyLog~, so postgres can't upgrade