Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question

[Garfield Lewis]

Tom Lane  writes:
> Doesn't that work already?

Hi Tom,

This works perfectly well for a NON-UPDATABLE cursor:


[lzsystem@nucky LZRDB-5220] $ psql -U lzpgsupr -d wdbs -f curs.pgs -e

BEGIN;

BEGIN

CREATE TABLE t0(c0 int);

psql:curs.pgs:2: NOTICE:  DDL was performed without updating catalog tables: 
Note that CREATE TABLE from a non-SDM client does not maintain LzRelational 
catalog tables

CREATE TABLE

INSERT INTO t0 VALUES 
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15);

INSERT 0 16

DECLARE cur0 SCROLL CURSOR FOR SELECT * FROM t0;

DECLARE CURSOR

MOVE FORWARD 10 IN cur0;

MOVE 10

MOVE FORWARD -3 IN cur0;

MOVE 3

MOVE BACKWARD 3 IN cur0;

MOVE 3

FETCH PRIOR FROM cur0;

 c0



  2

(1 row)



ROLLBACK;

ROLLBACK

However, adding FOR UPDATE gets me this:


[lzsystem@nucky LZRDB-5220] $ psql -U lzpgsupr -d wdbs -f curs.pgs -e

BEGIN;

BEGIN

CREATE TABLE t0(c0 int);

CREATE TABLE

INSERT INTO t0 VALUES 
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15);

INSERT 0 16

DECLARE cur0 /*SCROLL*/ CURSOR FOR SELECT * FROM t0 FOR UPDATE;

DECLARE CURSOR

MOVE FORWARD 10 IN cur0;

MOVE 10

MOVE FORWARD -3 IN cur0;

psql:curs.pgs:7: ERROR:  cursor can only scan forward

HINT:  Declare it with SCROLL option to enable backward scan.

MOVE BACKWARD 3 IN cur0;

psql:curs.pgs:8: ERROR:  current transaction is aborted, commands ignored until 
end of transaction block

FETCH PRIOR FROM cur0;

psql:curs.pgs:9: ERROR:  current transaction is aborted, commands ignored until 
end of transaction block

ROLLBACK;

ROLLBACK


In fact, adding both SCROLL and FOR UPDATE specifically says they are not 
compatible:

[lzsystem@nucky LZRDB-5220] $ psql -U lzpgsupr -d wdbs -f curs.pgs -e

BEGIN;

BEGIN

CREATE TABLE t0(c0 int);

INSERT INTO t0 VALUES 
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15);

INSERT 0 16

DECLARE cur0 SCROLL CURSOR FOR SELECT * FROM t0 FOR UPDATE;

psql:curs.pgs:4: ERROR:  DECLARE SCROLL CURSOR ... FOR UPDATE is not supported

DETAIL:  Scrollable cursors must be READ ONLY.

MOVE FORWARD 10 IN cur0;

psql:curs.pgs:6: ERROR:  current transaction is aborted, commands ignored until 
end of transaction block

MOVE FORWARD -3 IN cur0;

psql:curs.pgs:7: ERROR:  current transaction is aborted, commands ignored until 
end of transaction block

MOVE BACKWARD 3 IN cur0;

psql:curs.pgs:8: ERROR:  current transaction is aborted, commands ignored until 
end of transaction block

FETCH PRIOR FROM cur0;

psql:curs.pgs:9: ERROR:  current transaction is aborted, commands ignored until 
end of transaction block

ROLLBACK;

ROLLBACK


We are running Postgres 14:

[sysprog@nucky workspace] (h-master-LZRDB-5220-fix-WCOC-failure)*$ psql -V
psql (PostgreSQL) 14.7

Is this allowed maybe in Postgres 15?

Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question

[Adrian Klaver]

On 4/24/23 08:25, Garfield Lewis wrote:

Tom Lane  writes:
 > Doesn't that work already?

Hi Tom,





[sysprog@nucky workspace] (h-master-LZRDB-5220-fix-WCOC-failure)*$ psql -V

psql (PostgreSQL) 14.7

Is this allowed maybe in Postgres 15?



This:

psql:curs.pgs:2: NOTICE:  DDL was performed without updating catalog 
tables: Note that CREATE TABLE from a non-SDM client does not maintain 
LzRelational catalog tables


seems to indicate you are using some sort of Postgres fork.

Is that the case and if so what is the fork?

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

what happens if a failed transaction is not rolled back?

[Siddharth Jain]

Hi All,

i understand when writing application code, we should rollback a
transaction that fails to commit. this is typically done in the catch block
of a try-catch exception handler. but what if the developer does not
rollback the transaction? what happens in that case?

note that i am not asking: what happens if a transaction is not rolled back?
i am asking: what happens if a *failed* transaction is not rolled back?

failed transaction = you try to commit it but get an exception back from
the database.

thanks.

S.

Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question

[Tom Lane]

Garfield Lewis  writes:
> DECLARE cur0 SCROLL CURSOR FOR SELECT * FROM t0 FOR UPDATE;
> psql:curs.pgs:4: ERROR:  DECLARE SCROLL CURSOR ... FOR UPDATE is not supported
> DETAIL:  Scrollable cursors must be READ ONLY.

Ah.  Yeah, I don't think anyone is contemplating changing that.
Scrollable cursors with side effects would have unpleasant semantic
issues about when do the side-effects happen, and can they happen
multiple times (or, perhaps, not at all if you never read all of
the query output)?  FOR UPDATE would be slightly less messy than
other kinds of side-effects, since it's idempotent; but only slightly.

regards, tom lane

Re: what happens if a failed transaction is not rolled back?

[Adrian Klaver]

On 4/24/23 08:37, Siddharth Jain wrote:

Hi All,

i understand when writing application code, we should rollback a 
transaction that fails to commit. this is typically done in the catch 
block of a try-catch exception handler. but what if the developer does 
not rollback the transaction? what happens in that case?


note that i am not asking: what happens if a transaction is not rolled back?
i am asking: what happens if a /failed/ transaction is not rolled back?

failed transaction = you try to commit it but get an exception back from 
the database.


In Python:

import psycopg2
con = psycopg2.connect("dbname=test host=localhost  user=postgres")
cur = con.cursor()
cur.execute("select 1/0")
DivisionByZero: division by zero

cur.execute("select 1")
InFailedSqlTransaction: current transaction is aborted, commands ignored 
until end of transaction block






thanks.

S.


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

Re: what happens if a failed transaction is not rolled back?

[Adrian Klaver]

On 4/24/23 08:43, Adrian Klaver wrote:

On 4/24/23 08:37, Siddharth Jain wrote:

Hi All,

i understand when writing application code, we should rollback a 
transaction that fails to commit. this is typically done in the catch 
block of a try-catch exception handler. but what if the developer does 
not rollback the transaction? what happens in that case?


note that i am not asking: what happens if a transaction is not rolled 
back?

i am asking: what happens if a /failed/ transaction is not rolled back?

failed transaction = you try to commit it but get an exception back 
from the database.


In Python:

import psycopg2
con = psycopg2.connect("dbname=test host=localhost  user=postgres")
cur = con.cursor()
cur.execute("select 1/0")
DivisionByZero: division by zero

cur.execute("select 1")
InFailedSqlTransaction: current transaction is aborted, commands ignored 
until end of transaction block


Forgot to add.

To get past above:

con.rollback()
cur.execute("select 1")







thanks.

S.




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

Re: what happens if a failed transaction is not rolled back?

[David G. Johnston]

On Mon, Apr 24, 2023 at 8:37 AM Siddharth Jain  wrote:

> Hi All,
>
> i understand when writing application code, we should rollback a
> transaction that fails to commit. this is typically done in the catch block
> of a try-catch exception handler. but what if the developer does not
> rollback the transaction? what happens in that case?
>
> note that i am not asking: what happens if a transaction is not rolled
> back?
> i am asking: what happens if a *failed* transaction is not rolled back?
>
> failed transaction = you try to commit it but get an exception back from
> the database.
>
>
There isn't anything special about a failed transaction compared to any
other transaction that you leave open.

Might help to describe what the application does with the connection
subsequent to the point of attempted commit.

David J.

Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question

[Garfield Lewis]

> Adrian Klaver mailto:adrian.kla...@aklaver.com>> 
> wrote:

> This:

> psql:curs.pgs:2: NOTICE:  DDL was performed without updating catalog
 > tables: Note that CREATE TABLE from a non-SDM client does not maintain
 > LzRelational catalog tables

> seems to indicate you are using some sort of Postgres fork.
>
> Is that the case and if so what is the fork?

This is not a fork… it is pure Postgres 14 with an extension that checks for a 
certain environment and reports a NOTICE otherwise.

--
Regards,
Garfield A. Lewis

Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question

[Garfield Lewis]

> Garfield Lewis mailto:garfield.le...@lzlabs.com>> 
> wrote:

> This is not a fork… it is pure Postgres 14 with an extension that checks for 
> a certain environment and reports a NOTICE otherwise.

Oops, said extension meant trigger…

--
Regards,
Garfield A. Lewis

Re: what happens if a failed transaction is not rolled back?

[Luca Ferrari]

On Mon, Apr 24, 2023 at 5:37 PM Siddharth Jain  wrote:
> i am asking: what happens if a failed transaction is not rolled back?

A transaction either terminates by a commit or by a rollback. A failed
transaction is an opened transaction that can be terminated only by a
rollback.
If you don't rollback, then your transaction is hold by the driver,
that means for instance your pooler is not able to recycle it.
Clearly, the exact behavior depends by the driver.

Re: what happens if a failed transaction is not rolled back?

[David Wheeler]

> On 25 Apr 2023, at 1:47 am, David G. Johnston  
> wrote:
> 
> There isn't anything special about a failed transaction compared to any other 
> transaction that you leave open.

Now I’m curious. Does it have the same impact on performance that an idle in 
transaction connection has? Eg does it prevent vacuum? Does it still hold locks?

David

Re: what happens if a failed transaction is not rolled back?

[David G. Johnston]

On Mon, Apr 24, 2023 at 12:56 PM David Wheeler 
wrote:

>
>
> On 25 Apr 2023, at 1:47 am, David G. Johnston 
> wrote:
>
>
> There isn't anything special about a failed transaction compared to any
> other transaction that you leave open.
>
>
> Now I’m curious. Does it have the same impact on performance that an idle
> in transaction connection has? Eg does it prevent vacuum? Does it still
> hold locks?
>
>
Absent documentation to the contrary I would expect the system to at best
be in an idle-in-transaction state as-if the failed command never was
executed.  The concept of savepoints, whether in use in a particular
transaction, would require at least that much state be preserved.

David J.

Re: what happens if a failed transaction is not rolled back?

[Tom Lane]

"David G. Johnston"  writes:
> On Mon, Apr 24, 2023 at 12:56 PM David Wheeler 
> wrote:
>> Now I’m curious. Does it have the same impact on performance that an idle
>> in transaction connection has? Eg does it prevent vacuum? Does it still
>> hold locks?

> Absent documentation to the contrary I would expect the system to at best
> be in an idle-in-transaction state as-if the failed command never was
> executed.

A quick experiment will show you that we release locks as soon as the
transaction is detected to have failed.  I believe the same is true of
other interesting resources such as snapshots (which'd be what affects
vacuum) but it's less easy to observe that from the SQL level.  At least
by intention, a failed transaction won't hold any resources that would
impact other sessions.

> The concept of savepoints, whether in use in a particular
> transaction, would require at least that much state be preserved.

Of course, we can't release resources that were acquired by a still-live
subtransaction, a/k/a savepoint.

regards, tom lane

RE: FW: Error!

[Arquimedes Aguirre]

Hi Dear

Ok thanks so much for the explanation, but I don't understand, what does that 
connection have to do with the error that I described at the beginning, it 
gives me a password error with any user, what should I do to solve this problem 
and be able to start the connection?.

I would like to know other opinions from other people since this is a bigger 
large list of help users regarding this matter.

With any user that I enter, I get the same error when I go to enter the 
password.

In the link that you sent, there is the operation of the entire program pgadmin 
4 and it is very good, but where I can see some explanation in detail to the 
problem that is being presented to me, at what point exactly could be the 
answers or support for this type of failure

Thanks so much for attention and collaboration!

Best regards!

Sent from Mail for Windows


From: Adrian Klaver 
Sent: Saturday, April 22, 2023 11:04:01 AM
To: Arquimedes Aguirre ; pgsql-general list 

Subject: Re: FW: Error!

On 4/22/23 07:31, Arquimedes Aguirre wrote:
> Hi Dear Good Morning!
>


Please do not top post. Either bottom or inline post per:

https://en.wikipedia.org/wiki/Posting_style

>
> How to know if I have connection or not connection? May you explain for
> more details? please.

 From the screenshot you have two Postgres servers active, PostgreSQL 15
and punta. In the punta server you have two databases django-active and
postgres. FYI, you don't want to add tables to the postgres database.
It's primary purpose is to be a database you can connect to in the
absence of other databases. The screenshot also shows you are connected
to the databases. If you were not there would be a red x on the icon
next to the database name. To get down to the tables you will need to
click on Schemas and then Public. There will a series of items below
that including Tables. Click on that and pick the table you want to work
with. All of this is spelled out in more detail here:

https://www.pgadmin.org/docs/pgadmin4/6.21/index.html

Also you probably also look at the Properties for the PostgreSQL 15
server shown in the screenshot to see what host and port it is running on.

>
> I attach a screen what I did…
>
> Thanks for his attention and collaboration!
>
>
>  >
>

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

Re: FW: Error!

[Adrian Klaver]

On 4/24/23 15:00, Arquimedes Aguirre wrote:

Hi Dear

Ok thanks so much for the explanation, but I don't understand, what does 
that connection have to do with the error that I described at the 
beginning, it gives me a password error with any user, what should I do 
to solve this problem and be able to start the connection?.



Well your problem description has changed over the course of this 
conversation so at this point I don't really know what the error is that 
you are referring to.


USING TEXT not screenshots provide the command you are having the 
problem with and the error message you are getting.


You also need to be more specific about what connection as the last 
screenshot you provided showed you where connected using pgAdmin4.




I would like to know other opinions from other people since this is a 
bigger large list of help users regarding this matter.


1) I'm going to say as a generalization the  users on this list probably:

   a) Don't use pgAdmin4.
  and/or
   b) Don't use Windows.

2) There is a pgAdmin support list

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



With any user that I enter, I get the same error when I go to enter the 
password.


In the link that you sent, there is the operation of the entire program 
pgadmin 4 and it is very good, but where I can see some explanation in 
detail to the problem that is being presented to me, at what point 
exactly could be the answers or support for this type of failure


Without knowing the command used and the error message it will be 
difficult to point at a documented answer.




Thanks so much for attention and collaboration!

Best regards!




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

RE: FW: Error!

[Arquimedes Aguirre]

No, it has not changed the error that I have been experiencing since the 
beginning. This is the error:

psql: error: connection to server at "localhost" (::1), port 5432 failed: 
FATAL: password authentication failed for user "punta" Press any key to 
continue . .

Has always been the same error

I got question, because can’t you send a screenshots, with the image it’s much 
easier to identify the error or problem and you can also read the message, I 
don’t understand?

Thanks!

Sent from Mail for Windows

From: Adrian Klaver
Sent: Monday, April 24, 2023 5:42 PM
To: Arquimedes Aguirre; pgsql-general 
list
Subject: Re: FW: Error!

On 4/24/23 15:00, Arquimedes Aguirre wrote:
> Hi Dear
>
> Ok thanks so much for the explanation, but I don't understand, what does
> that connection have to do with the error that I described at the
> beginning, it gives me a password error with any user, what should I do
> to solve this problem and be able to start the connection?.


Well your problem description has changed over the course of this
conversation so at this point I don't really know what the error is that
you are referring to.

USING TEXT not screenshots provide the command you are having the
problem with and the error message you are getting.

You also need to be more specific about what connection as the last
screenshot you provided showed you where connected using pgAdmin4.

>
> I would like to know other opinions from other people since this is a
> bigger large list of help users regarding this matter.

1) I'm going to say as a generalization the  users on this list probably:

a) Don't use pgAdmin4.
   and/or
b) Don't use Windows.

2) There is a pgAdmin support list

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

>
> With any user that I enter, I get the same error when I go to enter the
> password.
>
> In the link that you sent, there is the operation of the entire program
> pgadmin 4 and it is very good, but where I can see some explanation in
> detail to the problem that is being presented to me, at what point
> exactly could be the answers or support for this type of failure

Without knowing the command used and the error message it will be
difficult to point at a documented answer.

>
> Thanks so much for attention and collaboration!
>
> Best regards!
>


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

Re: FW: Error!

[Adrian Klaver]

On 4/24/23 16:16, Arquimedes Aguirre wrote:
No, it has not changed the error that I have been experiencing since the 
beginning. This is the error:


psql: error: connection to server at "localhost" (::1), port 5432 
failed: FATAL: password authentication failed for user "punta" Press any 
key to continue . .


1) To be clear what password are you using the master password you set 
up for pgAdmin4 or the one for the Postgres server/database you are 
trying to connect to?


2) Did you create a password for the punta user on that server?

3) Find the pg_hba.conf file for that server and post it as text.



Has always been the same error

I got question, because can’t you send a screenshots, with the image 
it’s much easier to identify the error or problem and you can also read 
the message, I don’t understand?


Many folks on this list only use text email so screenshots have to be 
opened in another program. Also it is simple to get the text. In the 
terminal/console right click and click on Select All and then hit Enter 
to copy the content and then paste to your email.




Thanks!

Sent from Mail  for Windows

*From: *Adrian Klaver 
*Sent: *Monday, April 24, 2023 5:42 PM
*To: *Arquimedes Aguirre ; pgsql-general 
list 

*Subject: *Re: FW: Error!

On 4/24/23 15:00, Arquimedes Aguirre wrote:
 > Hi Dear
 >
 > Ok thanks so much for the explanation, but I don't understand, what does
 > that connection have to do with the error that I described at the
 > beginning, it gives me a password error with any user, what should I do
 > to solve this problem and be able to start the connection?.


Well your problem description has changed over the course of this
conversation so at this point I don't really know what the error is that
you are referring to.

USING TEXT not screenshots provide the command you are having the
problem with and the error message you are getting.

You also need to be more specific about what connection as the last
screenshot you provided showed you where connected using pgAdmin4.

 >
 > I would like to know other opinions from other people since this is a
 > bigger large list of help users regarding this matter.

1) I'm going to say as a generalization the  users on this list probably:

     a) Don't use pgAdmin4.
    and/or
     b) Don't use Windows.

2) There is a pgAdmin support list

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



 >
 > With any user that I enter, I get the same error when I go to enter the
 > password.
 >
 > In the link that you sent, there is the operation of the entire program
 > pgadmin 4 and it is very good, but where I can see some explanation in
 > detail to the problem that is being presented to me, at what point
 > exactly could be the answers or support for this type of failure

Without knowing the command used and the error message it will be
difficult to point at a documented answer.

 >
 > Thanks so much for attention and collaboration!
 >
 > Best regards!
 >


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



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

Re: FW: Error!

[Erik Wienhold]

> On 25/04/2023 01:34 CEST Adrian Klaver  wrote:
>
> On 4/24/23 16:16, Arquimedes Aguirre wrote:
> > I got question, because can’t you send a screenshots, with the image
> > it’s much easier to identify the error or problem and you can also read
> > the message, I don’t understand?
>
> Many folks on this list only use text email so screenshots have to be
> opened in another program. Also it is simple to get the text. In the
> terminal/console right click and click on Select All and then hit Enter
> to copy the content and then paste to your email.

And this list is text searchable which helps others who may stumble on the
same or a similar issue in the future.

--
Erik

Re: what happens if a failed transaction is not rolled back?

[Merlin Moncure]

On Mon, Apr 24, 2023 at 4:20 PM Tom Lane  wrote:

> "David G. Johnston"  writes:
> > On Mon, Apr 24, 2023 at 12:56 PM David Wheeler 
> > wrote:
> >> Now I’m curious. Does it have the same impact on performance that an
> idle
> >> in transaction connection has? Eg does it prevent vacuum? Does it still
> >> hold locks?
>
> > Absent documentation to the contrary I would expect the system to at best
> > be in an idle-in-transaction state as-if the failed command never was
> > executed.
>
> A quick experiment will show you that we release locks as soon as the
> transaction is detected to have failed.  I believe the same is true of
> other interesting resources such as snapshots (which'd be what affects
> vacuum) but it's less easy to observe that from the SQL level.  At least
> by intention, a failed transaction won't hold any resources that would
> impact other sessions.
>
> > The concept of savepoints, whether in use in a particular
> > transaction, would require at least that much state be preserved.
>
> Of course, we can't release resources that were acquired by a still-live
> subtransaction, a/k/a savepoint.
>


I think testing pg_stat_activity.backend_xid being not null does the trick.
If it's null, it either never took an xid by doing something that is worth
having one assigned after transaction start (including immediately after
procedure commit;), or had one that was released when aborted (if there is
an active savepoint it would keep backend_xid not null).  Of course, you
can't do that from the aborted transaction until it's rolled back first.

Hm. I also noticed when looking at this that aborted transactions with
savepoints are not subjected to the idle_in_transaction timeout which is a
bit surprising.
.

merlin

murmur3 hash binary data migration from Oracle to PostgreSQL

[Jagmohan Kaintura]

Hi Team,

We are doing Migration from Oracle to PostgreSQL. In SOurce database we
have Binary data stored using murmur3 hashing function. In Oracle this data
is being generated from the Java code and inserted into the Oracle
database.

As part of Migration processes the reference data on which this murmur3 is
generated is also getting changed while migrating to PostgreSQL.

In PostgreSQL do we have any mechanism for fetching this murmur3 hash
function for any UUID.

Please let me know, what ever solution is available for the implementation.



-- 
*Best Regards,*
Jagmohan

Re: what happens if a failed transaction is not rolled back?

[Tom Lane]

Merlin Moncure  writes:
> Hm. I also noticed when looking at this that aborted transactions with
> savepoints are not subjected to the idle_in_transaction timeout which is a
> bit surprising.

Hmm ... I think it's intentional that idle_in_transaction no longer
applies once the transaction has failed.  But if there's a live
savepoint, then we should enforce it since resources may still be
held.  Seems like a bug, if your observation is accurate.

regards, tom lane

Re: what happens if a failed transaction is not rolled back?

[Merlin Moncure]

On Mon, Apr 24, 2023 at 8:41 PM Tom Lane  wrote:

> Merlin Moncure  writes:
> > Hm. I also noticed when looking at this that aborted transactions with
> > savepoints are not subjected to the idle_in_transaction timeout which is
> a
> > bit surprising.
>
> Hmm ... I think it's intentional that idle_in_transaction no longer
> applies once the transaction has failed.  But if there's a live
> savepoint, then we should enforce it since resources may still be
> held.  Seems like a bug, if your observation is accurate.
>
>
hm,  double checking, it's not.

merlin

wiki.postgres ​ Tighten trigger permission checks already resolved

[jian he]

Hi.
The following Todo item seems  already resolved in pg15.

https://wiki.postgresql.org/wiki/Todo#Triggers

>  Tighten trigger permission checks
>
>- Security leak with trigger functions?
>
>
>
But it seems to not appear in the pg15 release notes. (I searched for the
keywords "trigger" and "function").

Re: murmur3 hash binary data migration from Oracle to PostgreSQL

[Erik Wienhold]

> On 25/04/2023 03:21 CEST Jagmohan Kaintura  wrote:
>
> We are doing Migration from Oracle to PostgreSQL. In SOurce database we have
> Binary data stored using murmur3 hashing function. In Oracle this data is
> being generated from the Java code and inserted into the Oracle database.

Do you store the hash and the binary data?  The hash is a key to the binary
data?

> As part of Migration processes the reference data on which this murmur3 is
> generated is also getting changed while migrating to PostgreSQL.

Why is the data changing during migration?  Shouldn't a migration preserve
the data and only adapt it if the database model needs to change?

> In PostgreSQL do we have any mechanism for fetching this murmur3 hash
> function for any UUID.

I don't understand what you mean by that.  What does it have to do with UUID?

Do you want to generate the MurmurHash in Postgres?  Postgres has no builtin
support for that hash function and I can't find any extension in a quick
online search.

Or do you want to just look up rows by the MurmurHash?  That's a trivial
SELECT statement.  Store the hash in an indexed column of type bytea to have
performant lookups.

--
Erik

Re: wiki.postgres ​ Tighten trigger permission checks already resolved

[Tom Lane]

jian he  writes:
> The following Todo item seems  already resolved in pg15.
> https://wiki.postgresql.org/wiki/Todo#Triggers
>> Tighten trigger permission checks
>> - Security leak with trigger functions?
>> 

> But it seems to not appear in the pg15 release notes. (I searched for the
> keywords "trigger" and "function").

The case shown at the head of that thread was fixed more than a decade
ago, cf commit 891e6e7bf (CVE-2012-0866).  However, the followup questions
discussed in the thread are still live: should there be a run-time not
only trigger-creation-time privilege check, and if so what should it
check exactly?  And is a separate TRIGGER privilege even reasonable,
rather than just saying you must be table owner to create a trigger?

regards, tom lane

Re: murmur3 hash binary data migration from Oracle to PostgreSQL

[Jeffrey Walton]

On Mon, Apr 24, 2023 at 9:22 PM Jagmohan Kaintura
 wrote:
>
> We are doing Migration from Oracle to PostgreSQL. In SOurce database we have 
> Binary data stored using murmur3 hashing function. In Oracle this data is 
> being generated from the Java code and inserted into the Oracle database.
>
> As part of Migration processes the reference data on which this murmur3 is 
> generated is also getting changed while migrating to PostgreSQL.
>
> In PostgreSQL do we have any mechanism for fetching this murmur3 hash 
> function for any UUID.
>
> Please let me know, what ever solution is available for the implementation.

My apologies if I misparsed a couple of statements. I am having
trouble determining if you are migrating away from Murmur3.

If you are selecting a new digest, then SipHash would be an excellent
choice. It was designed to avoid collisions and be fast. Plus it was
designed by Jean-Philippe Aumasson and Daniel J. Bernstein. It doesn't
get much better than those two fellows.

Jeff