Re: Rules and Command Status - update/insert/delete rule with series of commands in action

[Alban Hertroys]

> On 31 May 2024, at 00:34, johnlu...@hotmail.com wrote:
> 
> On 5/30/24 4:56 PM, David G. Johnston wrote:

(…)

>>  If anything is done it would have to be new syntax.  
>> 
>> 
> A much bigger task surely. 
> 
> On 5/30/24 5:19 PM, Adrian Klaver wrote:
>> 
>> 2) Use INSTEAD OF triggers: 
>> 
>> 
> 
> Unfortunately the same functionality as in my example with the RULE is not 
> supported for triggers on views :   from the manual
> INSTEAD OF triggers may only be defined on views, and only at row level;
> 
> A RULE is essentially a statement-level operation which is what I need for 
> this particular case.  A row-level trigger would not work because it 
> cannot "see" the query causing it to be fired,   and also ,  (most 
> importantly) is not fired at all if no rows match the original query, 
> whereas a RULE is always in effect regardless of which rows are involved. 
> before.  I should add that the RULE I showed in my example is not the 
> only RULE being used on this view  -there are other conditional RULEs,   
> and the combined effect is of being able to change the effect of the original 
> statement into a set of new statements,   one of which does what is needed.
> 
> And if you are now inclined to say "well,maybe the application itself is 
> poorly written and should be changed"  -   I would have to agree,but that 
> is not mine to change.
> 
> But I suppose that my next question,   given what you both say about the RULE 
> system being a dead-end,  is whether there is any likelihood of supporting an 
> INSTEAD OF trigger on a view at statement level?   Maybe that stands more 
> chance of going somewhere?

What you’re attempting to do boils down to adding a virtualisation layer over 
the database.

Several middleware products exist that provide data virtualisation, products 
that are accessed as a database (or as a web service, or both) that pass on 
queries to connected systems. The virtualisation layer rewrites those queries 
between the data sources and the user-visible virtual database connection and 
between generalised SQL and native dialects and languages.

If existing products support your particular use-case though, namely rewriting 
operational data-storage queries to data-source specific DML statements and 
then report the correct number of affected rows back, I don’t know.

However, an important reason that PG rules are deprecated (as I understand it) 
is that it is very hard to get right for generated columns, which are 
operations with side-effects (such as incrementing a sequence value, for 
example) that are included in those queries rewritten by the specified rules.
I doubt that a data virtualisation layer would be able to solve that particular 
problem.

Nevertheless, considering what path you’re on, they may be worth looking at. I 
don’t think there are any open-source initiatives (unfortunately), they’re all 
commercial products AFAIK, and not cheap. With a suitable use-case they can be 
rather valuable tools too though.

Regards,

Alban Hertroys
--
Als je de draak wilt steken met iemand,
dan helpt het,
als die een punthoofd heeft.

Re: ERROR: found xmin from before relfrozenxid; MultiXactid does no longer exist -- apparent wraparound

[Laurenz Albe]

On Thu, 2024-05-30 at 14:58 +, Alanoly Andrews wrote:
> We have a postgres 10.7 database which reports a number of issues on 
> user-created
> tables as well as system tables. Most errors are one of the following:
> -- ERROR:  found xmin 1888159934 from before relfrozenxid 1998177448
> -- ERROR:  MultiXactId 613819197 does no longer exist -- apparent wraparound
> -- ERROR:  could not access status of transaction 1927393975
>    DETAIL:  Could not open file "pg_xact/072E": No such file or directory.
> 
> Is there a way to repairing the corruption in this database?
> Postgres Version 10.7 on Linux(Ubuntu).

Perhaps, but you should hire an expert if the data are important for you.

Yours,
Laurenz Albe

Re: ERROR: found xmin from before relfrozenxid; MultiXactid does no longer exist -- apparent wraparound

[Thom Brown]

On Fri, May 31, 2024, 09:29 Laurenz Albe  wrote:

> On Thu, 2024-05-30 at 14:58 +, Alanoly Andrews wrote:
> > We have a postgres 10.7 database which reports a number of issues on
> user-created
> > tables as well as system tables. Most errors are one of the following:
> > -- ERROR:  found xmin 1888159934 from before relfrozenxid 1998177448
> > -- ERROR:  MultiXactId 613819197 does no longer exist -- apparent
> wraparound
> > -- ERROR:  could not access status of transaction 1927393975
> >DETAIL:  Could not open file "pg_xact/072E": No such file or
> directory.
> >
> > Is there a way to repairing the corruption in this database?
> > Postgres Version 10.7 on Linux(Ubuntu).
>
> Perhaps, but you should hire an expert if the data are important for you.
>

Also, while it's too late now, this could be the result of a bug in the
version you are using that was subsequently repaired in 10.15:

Prevent possible data loss from concurrent truncations of SLRU logs (Noah
Misch)

This rare problem would manifest in later “apparent wraparound” or “could
not access status of transaction” errors.

This is why it's important to keep up-to-date, but even the latest minor
10.x release is out of date as support was dropped back in 2022.

If you manage to get this up and running again, I strongly recommend
upgrading to the latest major and minor release (16.3 at the time of
writing).

Before you try doing anything though, create a physical backup of your
database as situations like this tend to require invasive action that could
potentially make the situation even worse.

Also, did this problem only happen in the last day or two? How frequently
do you take backups? If you have a backup from just before this issue
starting showing itself, and you can afford losing data changes that have
occured since the backup, you may find it far easier and quicker to resort
to using that backup. Of course, you would need to prove to yourself that
the backup was safe by running a VACUUM FREEZE on each database in that
backup before starting to use it.  If that runs without issue, you're
probably in the clear.

Best of luck.

Thom

>

Re: Rules and Command Status - update/insert/delete rule with series of commands in action

[Adrian Klaver]

On 5/30/24 15:34, johnlu...@hotmail.com wrote:


On 5/30/24 4:56 PM, David G. Johnston wrote:


Very little interest exists in working on user-specified rules.  They 
are practically deprecated.



Ah  -   pity  -   see my last comment to Adrian's




Any interest in perhaps providing a choice via a configuration
parameter?


 If anything is done it would have to be new syntax.



A much bigger task surely.


On 5/30/24 5:19 PM, Adrian Klaver wrote:


2) Use INSTEAD OF triggers:




Unfortunately the same functionality as in my example with the RULE is 
not supported for triggers on views :   from the manual


|INSTEAD OF| triggers may only be defined on views, *and only at row level*;


A RULE is essentially a statement-level operation which is what I need 
for this particular case.  A row-level trigger would not work 
because it cannot "see" the query causing it to be fired, and also ,  
(most importantly) is not fired at all if no rows match the original 
query, whereas a RULE is always in effect regardless of which rows 
are involved. before.  I should add that the RULE I showed in my 
example is not the only RULE being used on this view  -    there are 
other conditional RULEs,   and the combined effect is of being able to 
change the effect of the original statement into a set of new 
statements,   one of which does what is needed.




Basically don't give the user what they asked for, give them some 
cobbled together  on the fly version. Who decides that what the user 
needs? Seems to me this the point at which to have a discussion with the 
application developers about having the application asking the correct 
questions, rather then going down the road of bait and switch.





And if you are now inclined to say "well,    maybe the application 
itself is poorly written and should be changed"  -   I would have to 
agree,    but that is not mine to change.





But I suppose that my next question,   given what you both say about the 
RULE system being a dead-end,  is whether there is any likelihood of 
supporting an INSTEAD OF trigger on a view at statement level?   Maybe 
that stands more chance of going somewhere?




Cheers, John Lumby




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

Re: ERROR: found xmin from before relfrozenxid; MultiXactid does no longer exist -- apparent wraparound

[Alanoly Andrews]

Thanks, Thom.
I understand from your response that there is really no way to repair the 
current damage.

Yes, we do take daily backups and we have, in fact, restored the database 
cluster to a point in time before the corruption, suffering some loss of data 
in the process. I'm now working with the snapshot of the corrupted database (on 
a different box) to see if there is something that can be done to repair the 
damage and avoid such a scenario in future.  Yes, and I know that upgrading the 
Postgres version is the stock answer for situations like this. The upgrade is 
in the works.

But I was still interested in what the postgres gurus/programmers/hackers had 
to say about this event.

Regards.
Alanoly.

From: Thom Brown 
Sent: May 31, 2024 6:14 AM
To: Laurenz Albe 
Cc: Alanoly Andrews ; pgsql-general@lists.postgresql.org 

Subject: Re: ERROR: found xmin from before relfrozenxid; MultiXactid does no 
longer exist -- apparent wraparound

You don't often get email from t...@linux.com. Learn why this is 
important

[Email External/Externe] Caution opening links or attachments/attention lors de 
l'ouverture de liens ou de pièces jointes.

On Fri, May 31, 2024, 09:29 Laurenz Albe 
mailto:laurenz.a...@cybertec.at>> wrote:
On Thu, 2024-05-30 at 14:58 +, Alanoly Andrews wrote:
> We have a postgres 10.7 database which reports a number of issues on 
> user-created
> tables as well as system tables. Most errors are one of the following:
> -- ERROR:  found xmin 1888159934 from before relfrozenxid 1998177448
> -- ERROR:  MultiXactId 613819197 does no longer exist -- apparent wraparound
> -- ERROR:  could not access status of transaction 1927393975
>DETAIL:  Could not open file "pg_xact/072E": No such file or directory.
>
> Is there a way to repairing the corruption in this database?
> Postgres Version 10.7 on Linux(Ubuntu).

Perhaps, but you should hire an expert if the data are important for you.

Also, while it's too late now, this could be the result of a bug in the version 
you are using that was subsequently repaired in 10.15:

Prevent possible data loss from concurrent truncations of SLRU logs (Noah Misch)

This rare problem would manifest in later “apparent wraparound” or “could not 
access status of transaction” errors.

This is why it's important to keep up-to-date, but even the latest minor 10.x 
release is out of date as support was dropped back in 2022.

If you manage to get this up and running again, I strongly recommend upgrading 
to the latest major and minor release (16.3 at the time of writing).

Before you try doing anything though, create a physical backup of your database 
as situations like this tend to require invasive action that could potentially 
make the situation even worse.

Also, did this problem only happen in the last day or two? How frequently do 
you take backups? If you have a backup from just before this issue starting 
showing itself, and you can afford losing data changes that have occured since 
the backup, you may find it far easier and quicker to resort to using that 
backup. Of course, you would need to prove to yourself that the backup was safe 
by running a VACUUM FREEZE on each database in that backup before starting to 
use it.  If that runs without issue, you're probably in the clear.

Best of luck.

Thom


This e-mail may be privileged and/or confidential, and the sender does not 
waive any related rights and obligations. Any distribution, use or copying of 
this e-mail or the information it contains by other than an intended recipient 
is unauthorized. If you received this e-mail in error, please advise me (by 
return e-mail or otherwise) immediately.


Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits 
et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce 
message ou des renseignements qu'il contient par une personne autre que le 
(les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par 
erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un 
autre moyen.'.

Issue with PostgreSQL Installer on Windows and Special Characters in the superuser password

[Timo Schwaak]

Dear PostgreSQL community, I hope this is the right place for my concern. Today 
I discovered a strange behaviour of the Windows Installer of PostgreSQL. While 
migrating an application database from MSSQL, the Postgres setup did not seem 
to complete correctly and had problems starting the Postgres service after 
installation. I found that the service did not seem to be created at all. After 
some trying around with help I found online, trying non-domain users and some 
other stuff that was proposed for the problem, I found the problem lay in my 
chosen password. My password was randomly generated with alphanumeric + special 
characters. While this did not work, switching to alphanumeric-only passwords 
seemed to solve the problem. I suspect that the quotation marks in particular 
are causing problems in the setup, as they seem to terminate some strings 
prematurely in the scripts used by the installer. My setup was as follows: OS: 
Windows Server 2019, also tested on Windows Server 2022 PSQL version: 16.3 
(latest installer from EDB) Steps to reproduce: 1. Run the installer 2. Enter 
any password that contains quotation marks 3. The installer should fail at the 
end and report some errors. Could you please help me investigate this, is this 
a known problem or is there even a known workaround? If this is not a known 
issue, can you help me point this report in the right direction so that it can 
be addressed? Best regards, Timo

Re: Issue with PostgreSQL Installer on Windows and Special Characters in the superuser password

[Adrian Klaver]

On 5/31/24 14:18, Timo Schwaak wrote:

Dear PostgreSQL community,

I hope this is the right place for my concern.
Today I discovered a strange behaviour of the Windows Installer of 
PostgreSQL.
While migrating an application database from MSSQL, the Postgres setup 
did not seem to complete correctly and had problems starting the 
Postgres service after installation. I found that the service did not 
seem to be created at all.
After some trying around with help I found online, trying non-domain 
users and some other stuff that was proposed for the problem, I found 
the problem lay in my chosen password. My password was randomly 
generated with alphanumeric + special characters. While this did not 
work, switching to alphanumeric-only passwords seemed to solve the 
problem. I suspect that the quotation marks in particular are causing 
problems in the setup, as they seem to terminate some strings 
prematurely in the scripts used by the installer.

My setup was as follows:

OS: Windows Server 2019, also tested on Windows Server 2022
PSQL version: 16.3 (latest installer from EDB)
Steps to reproduce:
1. Run the installer
2. Enter any password that contains quotation marks
3. The installer should fail at the end and report some errors.


Could you please help me investigate this, is this a known problem or is 
there even a known workaround? If this is not a known issue, can you 
help me point this report in the right direction so that it can be 
addressed?


1) Don't use passwords with quotation marks.

2) Contact https://www.enterprisedb.com/ and file an issue with them.
The installer is their product not part of the community distribution.

3) Poke pgadmin-support here:

https://www.postgresql.org/list/pgadmin-support/

That is from EDB also, maybe someone over there can help.

4) Jump on this thread at pgsql-bugs about installer :

https://www.postgresql.org/message-id/flat/DU0PR10MB71447B1C6946731AD6C73D13C0E72%40DU0PR10MB7144.EURPRD10.PROD.OUTLOOK.COM

Or start a new one here:

https://www.postgresql.org/account/login/?next=/account/submitbug/




Best regards,

Timo


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