Re: murmur3 hash binary data migration from Oracle to PostgreSQL
> On 25/04/2023 12:44 CEST Jagmohan Kaintura wrote: > > No we want to generate murmur3 format only. > > > On 25-Apr-2023, at 8:52 AM, Jeffrey Walton wrote: > > > > 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. I don't understand. In your original message you write: > On 25/04/2023 03:21 CEST Jagmohan Kaintura wrote: > > In Oracle this data is being generated from the Java code and inserted into > the Oracle database. Do you generate the hash in Java or in Oracle? I don't know if Oracle Database provides a MurmurHash function but in Java you could use apache-commons[0] and change your Java code to also insert the hash (if you don't already do so). [0] https://commons.apache.org/proper/commons-codec/apidocs/org/apache/commons/codec/digest/MurmurHash3.html PS: Please don't top post. https://wiki.postgresql.org/wiki/Mailing_Lists#Email_etiquette_mechanics -- Erik
Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist
>The solution is the same whether you upgrade or not: you need >to adjust your search_path to include the "oracle" schema, >or else explicitly qualify references to orafce functions.Thank you very much >for your help. To use the to_date functions of Orafce 3.0.1, we created the following to_date function in the public schema of the old database. - CREATE OR REPLACE FUNCTION to_date(str text) RETURNS date AS $$ SELECT $1::date; $$ LANGUAGE sql IMMUTABLE STRICT; COMMENT ON FUNCTION public.to_date(text) IS 'Convert string to date'; - To avoid using a to_date function with the same name and parameter in the pg_catalog schema first, the search_path of the old database is set as follows: "$user", public, pg_catalog Make sure that public is searched before pg_catalog. After the database is upgraded, in order to solve the changes in Oracle 3.24, we have added oracle schema to the search_path, as shown below： "$user", public, oracle, pg_catalog The following error occurred when I ran my application. 42P13:ERROR:42P13: return type mismatch in function declared to return pg_catalog.date When I put the oracle schema at the end of the search_path, the problem was solved. The search_path settings without problems are as follows: "$user", public, pg_catalog, oracle Why does it report an error when i put oracle between public and pg_catalog? At 2023-04-20 01:18:15, "Tom Lane" wrote: >gzh writes: >> Thank you for your prompt reply. >> Is there another solution if the database is not upgraded to 12.14? > >The solution is the same whether you upgrade or not: you need >to adjust your search_path to include the "oracle" schema, >or else explicitly qualify references to orafce functions. > > regards, tom lane
DBD::Pg (version 3.16.3) returns EMPTY char columns as 'undef'
Hello,
We're using the above DBD::Pg version on Linux together with PostgreSQL 15.1
On fetch empty char columns are returned as (Perl) 'undef'
while ( my @row_ary = $dba->FetchArray()) {
foreach my $i (0..$#row_ary) {
if ($row_ary[$i] eq undef) {
print $row_ary[1] . "\n";
next;
}
...
which later leads in our code to NULL values '\N' in the writing of a CSV-like
export
files. Ofc NULL values in the database are something else as '' char
strings.
How this must be distinguished with DBD::Pg?
Thanks
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist
> On 25/04/2023 13:34 CEST gzh wrote: > > >The solution is the same whether you upgrade or not: you need > >to adjust your search_path to include the "oracle" schema, > >or else explicitly qualify references to orafce functions. > Thank you very much for your help. > > To use the to_date functions of Orafce 3.0.1, we created the following > to_date function in the public schema of the old database. > > - > CREATE OR REPLACE FUNCTION to_date(str text) RETURNS date AS $$ SELECT > $1::date; $$ LANGUAGE sql IMMUTABLE STRICT; COMMENT ON FUNCTION > public.to_date(text) IS 'Convert string to date'; > - > > To avoid using a to_date function with the same name and parameter in the > pg_catalog schema first, the search_path of the old database is set as > follows: > > "$user", public, pg_catalog > > Make sure that public is searched before pg_catalog. > After the database is upgraded, in order to solve the changes in Oracle > 3.24, we have added oracle schema to the search_path, as shown below： > > "$user", public, oracle, pg_catalog > > The following error occurred when I ran my application. > > 42P13:ERROR:42P13: return type mismatch in function declared to return > pg_catalog.date > > When I put the oracle schema at the end of the search_path, the problem was > solved. > The search_path settings without problems are as follows: > > "$user", public, pg_catalog, oracle > > Why does it report an error when i put oracle between public and pg_catalog? When you created function to_date(text) your search_path was probably "$user", public, pg_catalog Thereby the function was created with return type pg_catalog.date and without a search_path setting. The cast to date in the function body, however, is unqualified and thus relies on the session search_path. When adding oracle to the session search_path before pg_catalog, the cast will be to oracle.date (orafce defines its own date type) instead of pg_catalog.date. The function return type, however, is still declared as pg_catalog.date. To fix this create the function with an explicit search_path, i.e. CREATE FUNCTION to_date(text) RETURNS oracle.date SET search_path = oracle ... Or write the cast as $1::oracle.date to not rely on the search_path at all. -- Erik
Re: DBD::Pg (version 3.16.3) returns EMPTY char columns as 'undef'
2023年4月25日(火) 21:42 Matthias Apitz :
>
>
> Hello,
>
> We're using the above DBD::Pg version on Linux together with PostgreSQL 15.1
> On fetch empty char columns are returned as (Perl) 'undef'
>
> while ( my @row_ary = $dba->FetchArray()) {
>foreach my $i (0..$#row_ary) {
> if ($row_ary[$i] eq undef) {
> print $row_ary[1] . "\n";
> next;
> }
> ...
> which later leads in our code to NULL values '\N' in the writing of a
> CSV-like export
> files. Ofc NULL values in the database are something else as '' char
> strings.
>
> How this must be distinguished with DBD::Pg?
"eq undef" looks very wrong there:
$ perl -e "printf(qq|%i\n|, '' eq undef);"
1
$ perl -e "printf(qq|%i\n|, defined '');"
1
You probably want "if (!defined $row_ary[$i])". And possibly warnings enabled:
$ perl -w -e "printf(qq|%i\n|, '' eq undef);"
Use of uninitialized value in string eq at -e line 1.
1
$ perl -w -e "printf(qq|%i\n|, defined '');"
1
Regards
Ian Barwick
Re: FW: Error!
Am 25.04.23 um 01:16 schrieb 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 . . ... Hi, is it possible that postgres is listening on ipv4(127.0.0.1) only and you trying connect to ipv6(::1)? try connect to 127.0.0.1 as "server" instead of "localhost".
Re: FW: Error!
On 4/25/23 07:31, postgresql439...@heinz-it.de wrote: Am 25.04.23 um 01:16 schrieb 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 . . ... Hi, is it possible that postgres is listening on ipv4(127.0.0.1) only and you trying connect to ipv6(::1)? try connect to 127.0.0.1 as "server" instead of "localhost". Then you would get this error: psql: error: connection to server at "::1", port 5432 failed: Connection refused Is the server running on that host and accepting TCP/IP connections? -- Adrian Klaver adrian.kla...@aklaver.com
Re: murmur3 hash binary data migration from Oracle to PostgreSQL
>
> No we want to generate murmur3 format only.
>
If you need a server-side murmur3 function
- one alternative is the https://github.com/markokr/pghashlib extension.
psql (15.2 (Debian 15.2-2))
Type "help" for help.
db=# create extension hashlib;
CREATE EXTENSION
db=#
select hash_string('', 'murmur3');
select hash_string('a', 'murmur3');
select hash_string('abcdefg', 'murmur3');
select encode(hash128_string('abcdefg', 'murmur3'), 'hex');
+-+
| hash_string |
+-+
| 0 |
+-+
(1 row)
+-+
| hash_string |
+-+
| 1009084850 |
+-+
(1 row)
+-+
| hash_string |
+-+
| -2009294074 |
+-+
(1 row)
+--+
| encode |
+--+
| 069b3c88 |
+--+
(1 row)
In my test, I have used this fork:
https://github.com/bgdevlab/pghashlib/tree/bgdevlab/builds
RUN mkdir -p /pghashlib \
&& git clone --depth 1 --branch bgdevlab/builds
https://github.com/bgdevlab/pghashlib.git /pghashlib \
&& cd /pghashlib \
&& make USE_PGXS=1 \
&& make USE_PGXS=1 install \
&& rm -rf /pghashlib
Regards,
Imre
VACUUM (INDEX_CLEANUP OFF) and GIN indexes
Does VACUUM (INDEX_CLEANUP OFF) flush the pending list for GIN indexes, or is that skipped as well?
Re: VACUUM (INDEX_CLEANUP OFF) and GIN indexes
On Tue, Apr 25, 2023 at 9:18 AM Christophe Pettus wrote: > Does VACUUM (INDEX_CLEANUP OFF) flush the pending list for GIN indexes, or is > that skipped as well? It's skipped by VACUUM, but not by ANALYZE. So if you're using the reloption version of index_cleanup=off, it isn't necessarily going to stop autovacuum/autoanalyze from doing pending list cleanup. The ANALYZE pending list cleanup path has some problems: https://postgr.es/m/cah2-wzkjrk556envtflmyxedw91xguwiyzvep2kp5yqt_-3...@mail.gmail.com -- Peter Geoghegan
Re: VACUUM (INDEX_CLEANUP OFF) and GIN indexes
> On Apr 25, 2023, at 09:35, Peter Geoghegan wrote: > > It's skipped by VACUUM, but not by ANALYZE. So if you're using the > reloption version of index_cleanup=off, it isn't necessarily going to > stop autovacuum/autoanalyze from doing pending list cleanup. Ugh, thanks. I wasn't aware that it was flushed by ANALYZE as well. The current algorithm for pending list flush is kind of problematic in itself, because it relies on the highly optimistic assumption that the pending list won't grow very much while the list is being flushed.
Re: DBD::Pg (version 3.16.3) returns EMPTY char columns as 'undef'
On 2023-04-25 14:41:45 +0200, Matthias Apitz wrote:
> We're using the above DBD::Pg version on Linux together with PostgreSQL 15.1
> On fetch empty char columns are returned as (Perl) 'undef'
>
> while ( my @row_ary = $dba->FetchArray()) {
What is FetchArray? Neither perldoc DBI nor perldoc DBD::Pg mentions
this method. Did you use a wrapper around DBI? (I would have expected
fetchrow_array here)
> foreach my $i (0..$#row_ary) {
> if ($row_ary[$i] eq undef) {
> print $row_ary[1] . "\n";
> next;
So when any column is null you want to print the first one and skip to
the next one?
> }
> ...
> which later leads in our code to NULL values '\N' in the writing of a
> CSV-like export
> files. Ofc NULL values in the database are something else as '' char
> strings.
Works for me (PostgreSQL 14, Perl 5.34, DBI 1.643, DBD::Pg 3.15):
% cat empty_char
#!/usr/bin/perl
use v5.34;
use warnings;
use Data::Dumper;
use DBIx::SimpleConnect;
my $dbh = DBIx::SimpleConnect->connect("default");
$dbh->do("drop table if exists empty_char");
$dbh->do("create table empty_char (id serial primary key, t char(5))");
$dbh->do("insert into empty_char(t) values(null)");
$dbh->do("insert into empty_char(t) values('')");
$dbh->do("insert into empty_char(t) values(' ')");
$dbh->do("insert into empty_char(t) values('a')");
$dbh->do("insert into empty_char(t) values('a')");
my $data = $dbh->selectall_arrayref(
"select * from empty_char",
{Slice => {}}
);
print Dumper($data);
(DBIx::SimpleConnect is just a simple wrapper which looks up connection
strings. It returns a normal DBI database handle object)
% ./empty_char
$VAR1 = [
{
't' => undef,
'id' => 1
},
{
'id' => 2,
't' => ' '
},
{
't' => ' ',
'id' => 3
},
{
't' => 'a',
'id' => 4
},
{
'id' => 5,
't' => 'a'
}
];
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
Re: FW: Error!
Am 25.04.23 um 17:02 schrieb Adrian Klaver: On 4/25/23 07:31, postgresql439...@heinz-it.de wrote: Am 25.04.23 um 01:16 schrieb 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 . . ... Hi, is it possible that postgres is listening on ipv4(127.0.0.1) only and you trying connect to ipv6(::1)? try connect to 127.0.0.1 as "server" instead of "localhost". Then you would get this error: psql: error: connection to server at "::1", port 5432 failed: Connection refused Is the server running on that host and accepting TCP/IP connections? OK, my mistake. but is there a possibility to put in the same (may be copy/paste) credentials with different clients and cause this error? spoiler: there are many and the password 123456 was invented for this case. conclusion: after a few tests i would bet on a password which causes an ugly hiccup and this could be not users fail. testing: - windows installer with default settings, password is quite simple 123456. - in pgadmin create a new role with "special" password(i take the german specialties üöä or even some symbols). no errors. - in pgadmin add new server with credentials of new role ... automatic try to connect. connected! looks like there is nothing wrong at all. BUT - start the "SQL Shell" or try psql directly. - default settings(localhost:5432:postgres:postgres) and the simple password 123456 - connected. may be a warning about encoding/codepage, but connected. try credentials for new role. result: see TO first/last message. try change codepage (the docs says 1252 is appropriate for german). same result. password file in ANSI, same result. conclusion: see above the hardest fail: i tried first of all something like "special" password for superuser during installation and get lost without any idea why. error: "failed to load SQL modules into the database cluster." next one follows, warning: "Problem running post-install step. Installation may not complete correctly Error reading file C:/Program Files/PostgreSQL/15/data/postgresql.conf" result: initialization failed, data directory doesn't exist. the final shot is the installation-summary log, no errors, no comments about that even something could be not as expected. how someone would see this and get to understanding that his/her keyboard has wrong keys to type right passwords? PS to whom it may concern: it's not my problem and i don't use it this way. but other people have no choice. safe the lost souls, do it better, please! A.H.
Re: DBD::Pg (version 3.16.3) returns EMPTY char columns as 'undef'
El día martes, abril 25, 2023 a las 09:58:10 +0900, Ian Lawrence Barwick
escribió:
> "eq undef" looks very wrong there:
>
> $ perl -e "printf(qq|%i\n|, '' eq undef);"
> 1
> $ perl -e "printf(qq|%i\n|, defined '');"
> 1
>
> You probably want "if (!defined $row_ary[$i])". And possibly warnings enabled:
>
> ...
Ian,
Thanks for this hint. It fixed it. I modified the code to:
...
foreach my $i (0..$#row_ary) {
if (!defined $row_ary[$i]) {
printf $row_ary[0] . " | " . $row_ary[1] . " | " . $row_ary[2] .
"\n";
next;
}
set one row to have a NULL value (others have '' or a real string for
the column varvalue):
testdb=# update adm_partab set varvalue = NULL where
varname='DocumentUploadSshPassw';
UPDATE 1
testdb=# \pset null ''
Null-Anzeige ist »«.
testdb=# select * from adm_partab where varname='DocumentUploadSshPassw';
product |varname | varvalue
-++--
1 | DocumentUploadSshPassw |
and run the modiefied code which prints only the row with the NULL
value, exports everything fine and the NULL value as '\N':
~sisis/sc/dbtool < unl 2>&1 | more
connected to Pg:testdb
begin operation: UNLOAD (adm_partab)
1 | DocumentUploadSshPassw |
366 rows unloaded...
grep -C3 '|\\N' adm_partab.load
2|CIR_auto_idm_informieren|N
2|CIR_GebMahn_SO|N
2|CIR_BR_Immer_Benachrichtigen|N
1|DocumentUploadSshPassw|\N <***
2|CIR_AutoTempMedien|N
2|CIR_PrintCmd_List_Ascii|/opt/lib/sisis/bin/AsciiPrint.sh
2|CIR_Such_Kategorie_4|902 Schlagwort
even the warning is printed for this perl line 1196:
~sisis/sc/dbtool < unl 2>&1 | grep 1196
Use of uninitialized value $row_ary[2] in concatenation (.) or string at
/home/sisis/sc/dbtool.pl line 1196.
Thanks again and
Kind Regards
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
