restore and recovery using WAL: unkown messages in serverlog
Hello, I'm testing WAL based backup and restore/recovery and encounter some unclear messages in the 'serverlog' on recovery. The used procedure on a test server is: Kick the copy of the current WAL to the archive: $ psql -Usisis -c "select pg_switch_wal();" $ ls -l /data/postgresql11/wal_archive insgesamt 49212 -rw--- 1 postgres postgres 16777216 3. Sep 07:53 00010001008B -rw--- 1 postgres postgres 16777216 3. Sep 08:52 00010001008C -rw--- 1 postgres postgres 16777216 3. Sep 08:54 00010001008D Now we backup the cluster: $ pg_basebackup -Usisis -Ft -D /data/postgresql11/backup-20190903-1 $ ls -l /data/postgresql11/backup-20190903-1 insgesamt 6379972 -rw--- 1 postgres postgres 6509913088 3. Sep 09:02 base.tar -rw--- 1 postgres postgres 16779264 3. Sep 09:02 pg_wal.tar $ ls -l /data/postgresql11/wal_archive/ insgesamt 65620 -rw--- 1 postgres postgres 16777216 3. Sep 07:53 00010001008B -rw--- 1 postgres postgres 16777216 3. Sep 08:52 00010001008C -rw--- 1 postgres postgres 16777216 3. Sep 08:54 00010001008D -rw--- 1 postgres postgres 16777216 3. Sep 09:02 00010001008E -rw--- 1 postgres postgres 342 3. Sep 09:02 00010001008E.0028.backup We stop and "destroy" the cluster (i.e. we move it away): # /etc/init.d/postgres stop # mv /data/postgresql11/data /data/postgresql11/data.away # mkdir /data/postgresql11/data # chown postgres:postgres /data/postgresql11/data # chmod 0700 /data/postgresql11/data We restore the cluster as user 'postgres' and recover based on the WAL: $ tar xf /data/postgresql11/backup-20190903-1/base.tar-C /data/postgresql11/data/ $ tar xf /data/postgresql11/backup-20190903-1/pg_wal.tar -C /data/postgresql11/data/pg_wal/ For recover we create a file recovery.conf $ vim /data/postgresql11/data/recovery.conf restore_command = 'cp /data/postgresql11/wal_archive/%f %p' Now start PostgreSQL again: # /etc/init.d/postgres start In the 'serverlog' it gives some errors (marked with ***>); sorry that the messages are in German, it means 'cp: stat failed for ..., file or directory not found': 2019-09-03 09:18:44.355 CEST [25388] LOG: starte Wiederherstellung aus Archiv 2019-09-03 09:18:45.249 CEST [25388] LOG: Logdatei »00010001008E« aus Archiv wiederhergestellt 2019-09-03 09:18:45.306 CEST [25388] LOG: Redo beginnt bei 1/8E28 2019-09-03 09:18:45.308 CEST [25388] LOG: konsistenter Wiederherstellungszustand erreicht bei 1/8E000130 2019-09-03 09:18:45.308 CEST [25387] LOG: Datenbanksystem ist bereit, um lesende Verbindungen anzunehmen 2019-09-03 09:18:46.024 CEST [25388] LOG: Logdatei »00010001008F« aus Archiv wiederhergestellt ***> cp: der Aufruf von stat für '/data/postgresql11/wal_archive/000100010090' ist nicht möglich: Datei oder Verzeichnis nicht gefunden 2019-09-03 09:18:47.838 CEST [25388] LOG: Redo fertig bei 1/8F000140 2019-09-03 09:18:47.869 CEST [25388] LOG: Logdatei »00010001008F« aus Archiv wiederhergestellt ***> cp: der Aufruf von stat für '/data/postgresql11/wal_archive/0002.history' ist nicht möglich: Datei oder Verzeichnis nicht gefunden 2019-09-03 09:18:47.900 CEST [25388] LOG: gewählte neue Zeitleisten-ID: 2 2019-09-03 09:18:47.961 CEST [25388] LOG: Wiederherstellung aus Archiv abgeschlossen ***> cp: der Aufruf von stat für '/data/postgresql11/wal_archive/0001.history' ist nicht möglich: Datei oder Verzeichnis nicht gefunden 2019-09-03 09:18:48.072 CEST [25387] LOG: Datenbanksystem ist bereit, um Verbindungen anzunehmen What is causing the errors about the missing files in /data/postgresql11/wal_archive? Btw: Is there away to run the server for German UTF-8, but with English messages in the log? Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
PG SQL and LIKE clause
Hello, We're porting a huge Library Management System, written using all kind of languages one can think of (C, C++, ESQL/C, Perl, Java, ...) on Linux from the DBS Sybase to PG, millions of lines of code, which works also with DBS Oracle and in the past with INFORMIX-SE and -ONLINE. We got to know that in CHAR columns with trailing blanks a SELECT ... FROM ... WHERE name LIKE 'Ali' does not match in 'name' having 'Ali '. I glanced through our code with grep pipelines and found some hundred places which would be affected by this problem. I'm not interested in a religious discussion if or if not this behaviour of PG is correcter or better than in Sybase. It's just different to Sybase. Any hints to address this problem? Or is there any compile time option for the PG server to address this? Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub Mientras haya voluntad de lucha habrá esperanza de vencer.
Re: PG SQL and LIKE clause
El día Friday, September 13, 2019 a las 07:33:10AM +0200, Pavel Stehule escribió: > > We got to know that in CHAR columns with trailing blanks a > > > > SELECT ... FROM ... WHERE name LIKE 'Ali' > > > > does not match in 'name' having 'Ali '. > > > > I glanced through our code with grep pipelines and found some hundred > > places which would be affected by this problem. I'm not interested in a > > religious discussion if or if not this behaviour of PG is correcter or > > better than in Sybase. It's just different to Sybase. > > > > Any hints to address this problem? Or is there any compile time option > > for the PG server to address this? > > > > There is not simple solution - you should to write own patch and used > patched postgres. Hello, As we compiled our own PG 11.4 and as we're alone on the server with our software plus PG, this could be an option to consider. Do you have an idea where to look for this in the PG source. Maybe it's just deleting the trailing blanks from the column content before applying the string compare with regular expressions. Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
Re: Installed PostgreSQL-11 in Ubuntu 18.04.02 Server Edition: No existing local cluster is suitable as a default target
El día Wednesday, September 18, 2019 a las 11:50:45AM +0200, Marco Ippolito escribió: > I installed PostgreSQL-11 in Ubuntu 18.04.02 Server Edition using package > manager: sudo apt-get install postgresql-11. But now I'm not able to call > psql: > > (base) marco@pc:~$ sudo su -l postgres > [sudo] password for marco: > No directory, logging in with HOME=/ > postgres@pc:/$ psql > Warning: No existing local cluster is suitable as a default target. > Please > see man pg_wrapper(1) how to specify one. > Error: You must install at least one postgresql-client- package > postgres@pc:/$ > > I tried to specify the port to use but it asks for a postgresql-client: > > (base) marco@pc:~$ sudo -u postgres /usr/bin/psql -p 5432 > Error: You must install at least one postgresql-client- package > > But the latest postgresql-client is already installed: > > (base) marco@pc:~$ sudo apt-get install postgresql-client-11 > Reading package lists... Done > Building dependency tree > Reading state information... Done > postgresql-client-11 is already the newest version (11.5-1.pgdg18.04+1). > 0 to upgrade, 0 to newly install, 0 to remove and 0 not to upgrade. > > How to solve the problem? Looking forward to your kind help. > Marco Have you setup and started any PG cluster? See https://www.postgresql.org/docs/11/runtime.html (chapter 18. Server Setup and Operation). matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
Re: Installed PostgreSQL-11 in Ubuntu 18.04.02 Server Edition: No existing local cluster is suitable as a default target
El día Wednesday, September 18, 2019 a las 12:44:20PM +0200, Marco Ippolito
escribió:
> thanks Andreas.
>
> After changing ownership of /usr/lib/postgresql to postgres user,
>
> postgres@pc:/home/marco$ /usr/lib/postgresql/11/bin/pg_ctl -D
> /usr/local/pgsql/data -l /usr/lib/postgresql/11/log/logfile start
> waiting for server to start done
> server started
> postgres@pc:/home/marco$
>
> But now... how to call and use psql?
Marco, I compiled 11.4 on Linux for our needs and wrote a Confluence
page about this and the cluster setup. I could send you this pages in PDF
off-list.
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
Re: Installed PostgreSQL-11 in Ubuntu 18.04.02 Server Edition: No existing local cluster is suitable as a default target
El día miércoles, septiembre 18, 2019 a las 07:41:41a. m. -0700, Adrian Klaver escribió: > On 9/18/19 6:13 AM, Marco Ippolito wrote: > > Thanks Matthias, > > very kind. Hopefully I will get some hints on how to solve my problem. > > You should probably take a look at: > > https://help.ubuntu.com/lts/serverguide/postgresql.html > > FYI, you should set the permission son /usr/lib/postgresql back to root > and use: > > sudo pg_ctl NAK. In my understanding, the dir above the data dir of the cluster should be owned by 'postgres' and the pg_ctl run as 'postgres': # mkdir /data/postgresql11 # chown postgres /data/postgresql11 # su - postgres $ LANG=de_DE.UTF-8 export LANG $ /usr/local/sisis-pap/pgsql/bin/pg_ctl -D /data/postgresql11/data initdb This is stolen from the pages mentioned, i.e. https://www.postgresql.org/docs/11/runtime.html (chapter 18. Server Setup and Operation) and worked out of the box fine for me. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub Mientras haya voluntad de lucha habrá esperanza de vencer.
PGPASSWORD in crypted form, for example BlowFish or SHA-256
Hello, Our software, a huge ILS, is running on Linux with DBS Sybase. To connect to the Sybase server (over the network, even on localhost), credentials must be known: a user (say 'sisis') and its password. For Sybase we have them stored on the disk of the system in a file syb.npw as: $ cat /opt/lib/sisis/etc/syb/syb.npw sisis:e53902b9923ab2fb sa:64406def48efca8c for the user 'sisis' and the administrator 'sa'. Our software has as shared library a blob which knows how to decrypt the password hash above shown as 'e53902b9923ab2fb' into clear text which is then used in the ESQL/C or Java layer to connect to the Sybase server. For PostgreSQL the password must be typed in (for pgsql) or can be provided in an environment variable PGPASSWORD=blabla Is there somehow an API in PG to use ciphered passwords and provide as a shared library the blob to decrypt it? If not, we will use the mechanism same as we use for Sybase. Or any other idea to not make detectable the credentials? This was a request of our customers some years ago. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub Mientras haya voluntad de lucha habrá esperanza de vencer.
Re: PGPASSWORD in crypted form, for example BlowFish or SHA-256
El día Thursday, September 19, 2019 a las 10:31:01PM +1000, rob stone escribió: > Hello, > > On Thu, 2019-09-19 at 12:30 +0200, Matthias Apitz wrote: > > Hello, > > > > Our software, a huge ILS, is running on Linux with DBS Sybase. To > > connect to the Sybase server (over the network, even on localhost), > > credentials must be known: a user (say 'sisis') and its password. > > > > For Sybase we have them stored on the disk of the system in a file > > syb.npw as: > > > > $ cat /opt/lib/sisis/etc/syb/syb.npw > > sisis:e53902b9923ab2fb > > sa:64406def48efca8c > > > > for the user 'sisis' and the administrator 'sa'. Our software has as > > shared library a blob which knows how to decrypt the password hash > > above > > shown as 'e53902b9923ab2fb' into clear text which is then used in the > > ESQL/C or Java layer to connect to the Sybase server. > > > > For PostgreSQL the password must be typed in (for pgsql) or can be > > provided in an environment variable PGPASSWORD=blabla > > > > Is there somehow an API in PG to use ciphered passwords and provide > > as a > > shared library the blob to decrypt it? If not, we will use the > > mechanism same as > > we use for Sybase. Or any other idea to not make detectable the > > credentials? This was a request of our customers some years ago. > > > > > https://www.postgresql.org/docs/11/auth-password.html > > Chapters 20.5 and 20.6 may give you more information. The form of the password hash store in the PG server or interchange over the network is not my question. The question is more: When the Linux server starts and with this the (ESQL/C written) application servers are starting, they need the password to connect and this is not provided at this moment from some keyboard or humanbeing. It must be stored on the server and available in clear for the server, but not for other eyes on the server, i.e. the place of the sorage must be ciphered. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
updating sequence value for column 'serial'
Hello,
We have in a database some 400 tables, 75 of them have a 'serial'
column, like the one in the example table 'titel_daten', column 'katkey'.
I want to create a SQL script to adjust alls these sequences to the
max+1 value in its column after loading the database from CSV file.
I found no other way as the code below (the RAISE NOTICE is
only for test at the moment and the output is correct for this table,
i.e current max in 'katkey' is 330721):
sisis=# DO $$
sisis$# DECLARE
sisis$#maxikatkey integer := ( select max(katkey) from titel_daten );
sisis$#result integer := 1;
sisis$# BEGIN
sisis$#maxikatkey := maxikatkey +1;
sisis$#RAISE NOTICE '%', maxikatkey ;
sisis$#result := (SELECT SETVAL('titel_daten_katkey_seq', maxikatkey) );
sisis$#RAISE NOTICE '%', result ;
sisis$# END $$;
NOTICE: 330722
NOTICE: 330723
DO
Is there any better way? Thanks
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Mientras haya voluntad de lucha habrá esperanza de vencer.
Re: updating sequence value for column 'serial'
El día martes, septiembre 24, 2019 a las 08:01:46a. m. -0700, Adrian Klaver
escribió:
> On 9/24/19 7:47 AM, Matthias Apitz wrote:
> >
> > Hello,
> >
> > We have in a database some 400 tables, 75 of them have a 'serial'
> > column, like the one in the example table 'titel_daten', column 'katkey'.
> >
> > I want to create a SQL script to adjust alls these sequences to the
> > max+1 value in its column after loading the database from CSV file.
> > I found no other way as the code below (the RAISE NOTICE is
> > only for test at the moment and the output is correct for this table,
> > i.e current max in 'katkey' is 330721):
> >
> > sisis=# DO $$
> > sisis$# DECLARE
> > sisis$#maxikatkey integer := ( select max(katkey) from titel_daten );
> > sisis$#result integer := 1;
> > sisis$# BEGIN
> > sisis$#maxikatkey := maxikatkey +1;
> > sisis$#RAISE NOTICE '%', maxikatkey ;
> > sisis$#result := (SELECT SETVAL('titel_daten_katkey_seq', maxikatkey) );
> > sisis$#RAISE NOTICE '%', result ;
> > sisis$# END $$;
> > NOTICE: 330722
> > NOTICE: 330723
> > DO
> >
> > Is there any better way? Thanks
>
> I have not found a better way. I use ALTER SEQUENCE .. RESTART 330722
> though:
Yes, but I found no way to use a variable like 'maxikatkey' in the ALTER
SEQUENCE ...
it only excepts digits like 330722.
Sometimes, when the table has no rows for example, the SELECT MAX(...) FROM ...
returns . I'm surprised about that even maxikatkey := maxikatkey +1; does
not set it to 1 'maxikatkey'. Should I worry about this in SELECT SETVAL(...)
or can
I make it somehow to 1 or 0?
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Mientras haya voluntad de lucha habrá esperanza de vencer.
signature.asc
Description: PGP signature
Re: updating sequence value for column 'serial'
El día miércoles, septiembre 25, 2019 a las 07:42:11a. m. -0700, Adrian Klaver
escribió:
> >>> sisis$# DECLARE
> >>> sisis$#maxikatkey integer := ( select max(katkey) from titel_daten );
> >>> sisis$#result integer := 1;
> >>> sisis$# BEGIN
> >>> sisis$#maxikatkey := maxikatkey +1;
> >>> sisis$#RAISE NOTICE '%', maxikatkey ;
> >>> sisis$#result := (SELECT SETVAL('titel_daten_katkey_seq', maxikatkey)
> >>> );
> >>> sisis$#RAISE NOTICE '%', result ;
> >>> sisis$# END $$;
> >>> NOTICE: 330722
> >>> NOTICE: 330723
> >>> DO
> >>>
> >>> Is there any better way? Thanks
> >>
> >> I have not found a better way. I use ALTER SEQUENCE .. RESTART 330722
> >> though:
> >
> > Yes, but I found no way to use a variable like 'maxikatkey' in the ALTER
> > SEQUENCE ...
> > it only excepts digits like 330722.
>
> DO $$
> DECLARE
> max_id int;
> BEGIN
> SELECT INTO max_id max(id) + 1 FROM seq_test;
> RAISE NOTICE 'Max id is %', max_id;
> EXECUTE 'ALTER SEQUENCE seq_test_id_seq RESTART ' || max_id::text;
> END;
> $$ LANGUAGE plpgsql;
Hi Adrian,
I adopted your code to the name of my table 'ig_target_ipfilter' and its
SERIAL column 'id'; it does not work (and I don't know how it could
works because in the 'ALTER SEQUENCE ...' stmt is somehow missing '... WITH
value ...')
or do I understand something wrong?):
cat -n /home/apitzm/postgreSQL/test.sql
1 DO $$
2 DECLARE
3 max_id int;
4 BEGIN
5 SELECT INTO max_id max(id) + 1 FROM ig_target_ipfilter ;
6 RAISE NOTICE 'Max id in % is %', 'ig_target_ipfilter', max_id;
7 EXECUTE 'ALTER SEQUENCE ig_target_ipfilter_id_seq RESTART ' ||
max_id::text;
8 END;
9 $$ LANGUAGE plpgsql;
psql -Usisis -dsisis < /home/apitzm/postgreSQL/test.sql
NOTICE: Max id in ig_target_ipfilter is
ERROR: query string argument of EXECUTE is null
KONTEXT: PL/pgSQL function inline_code_block line 7 at EXECUTE
Please clarify. Thanks
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Mientras haya voluntad de lucha habrá esperanza de vencer.
signature.asc
Description: PGP signature
Perl DBI converts UTF-8 again to UTF-8 before sending it to the server
Hello,
We're facing the problem that UTF-8 data to be INSERT'ed into a CHAR
column is converted again to UTF-8, assuming it's ISO. I have here a
small Perl program which can be used for testing:
#!/usr/local/bin/perl
use utf8;
my $PGDB = 'dbi:Pg:dbname=newsisis;host=127.0.0.1';
my $PGDB_USER = 'sisis';
my $SQL_INSERT = 'INSERT INTO dbctest (tstchar25, tstint) VALUES (?, ?)';
use DBI;
my $dbh = DBI->connect($PGDB, $PGDB_USER)
|| die "Couldn't connect to $PGDB as user $PGDB_USER: $DBI::errstr\n";
print "DBI is version $DBI::VERSION, DBD::Pg is version $DBD::Pg::VERSION\n";
$dbh->do("SET client_encoding TO UTF8");
$dbh->{pg_enable_utf8} = 1;
my $sth = $dbh->prepare( $SQL_INSERT )
|| die "Can't prepare insert statement $SQL_INSERT: $DBI::errstr";
my $text = "\xc3\xa4";
print "text: ".$text."\n";
$sth->execute($text, 1) or die $sth->errstr, "\n";
Running this, gives the following output:
$ ./utf8.pl
DBI is version 1.642, DBD::Pg is version 3.8.0
text: ä
$ ./utf8.pl | od -tx1
000 44 42 49 20 69 73 20 76 65 72 73 69 6f 6e 20 31
020 2e 36 34 32 2c 20 44 42 44 3a 3a 50 67 20 69 73
040 20 76 65 72 73 69 6f 6e 20 33 2e 38 2e 30 0a 74
060 65 78 74 3a 20 c3 a4 0a
^
(this shows that the var '$text' contains \xc3a4, an UTF-8 'ä'
(a-Umlaut).
If we now look into the table in hex we see:
$ printf "select tstchar25::bytea from dbctest ;\n" | psql -Usisis -dnewsisis
tstchar25
--
\xc383c2a42020202020202020202020202020202020202020202020
(1 Zeile)
i.e. the 'ä' is converted again, like this cmd would do:
$ printf 'ä' | iconv -f iso-8859-1 -t utf-8 | od -tx1
000 c3 83 c2 a4
and ofc it's looking broken:
$ printf "select tstchar25 from dbctest ;\n" | psql -Usisis -dnewsisis
tstchar25
---
ä
(1 Zeile)
I watched the trafic between the client ./utf8.pl and the server with
strace and it's sent broken already to the server:
...
write(1, "text: \303\244\n", 9) = 9
sendto(3, "P\0\0\0G\0INSERT INTO dbctest (tstchar25, tstint) VALUES ($1,
$2)\0\0\2\0\0\0\0\0\0\0\0B\0\0\0\33\0\0\0\0\0\2\0\0\0\4\303\203\302\244\0\0\0\0011\0\1\0\0D\0\0\0\6P\0E\0\0\0\t\0\0\0\0\0S\0\0\0\4",
122, MSG_NOSIGNAL, NULL, 0) = 122
...
see the sequence '\303\203\302\244' in octal values.
What is the problem with DBI? Thanks
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
3. Oktober! Wir gratulieren! Der Berliner Fernsehturm wird 50
aus: https://www.jungewelt.de/2019/10-02/index.php
signature.asc
Description: PGP signature
Re: Perl DBI converts UTF-8 again to UTF-8 before sending it to the server
El día viernes, octubre 04, 2019 a las 04:29:32p. m. +0200, Christoph Moench-Tegeder escribió: > ## Matthias Apitz (g...@unixarea.de): > > > my $text = "\xc3\xa4"; > > print "text: ".$text."\n"; > > Your output is lying to you: > you need a binmode(STDOUT, ':encoding(utf8)'), which will make this print > "ä", and a utf8::decode($text), after which you get "ä". And when you > pass that $text through DBD::Pg, it will still be an "ä". And when you > change $text to "ä", it still works. Most other combinations will > either fail with "\xc3\xa4" or "ä". Thanks, but: ... my $text = "ä \xc3\xa4"; print "text before STDOUT set to UTF-8: ".$text."\n"; binmode(STDOUT, ':encoding(utf8)'); print "text after STDOUT set to UTF-8: ".$text."\n"; utf8::decode($text); print "text after utf8::decode(\$text): ".$text."\n"; $sth->execute($text, 1) or die $sth->errstr, "\n"; gives: ./utf8.pl text before STDOUT set to UTF-8: ä text after STDOUT set to UTF-8: ä ä text after utf8::decode($text): ä ä printf "select tstchar25::bytea from dbctest ;\n" | psql -Usisis -dnewsisis tstchar25 ---- \xc3a420c383c2a4202020202020202020202020202020202020202020 (1 Zeile) > Welcome to modern perl's handling of utf8. Cue "Everything is Fine" meme. Seems so :-( matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub 3. Oktober! Wir gratulieren! Der Berliner Fernsehturm wird 50 aus: https://www.jungewelt.de/2019/10-02/index.php signature.asc Description: PGP signature
SELECT d02name::bytea FROM ... && DBI::Pg
Hello,
I can SELECT && print a column in hex with:
pos71=# select d02name::bytea from d02ben where d02bnr = '1048313' ;
...
\x50c3a46461676f67697363686520486f6368736368756c65205765696e67617274656e2020
...
but when I use the same in Perl DBI::Pg with:
...
$sth=$dbh->prepare( "select d02name::bytea from d02ben where d02bnr =
'1048313'");
$sth->execute or die "exec error\n".${DBI::errstr}."\n";
while ( (@row = $sth->fetchrow_array) ) {
print "$row[0]\n";
}
It prints the UTF-8 string and not the hex string:
./utf8-01.pl
DBI is version 1.642, DBD::Pg is version 3.8.0
client_encoding=UTF8, server_encoding=UTF8
Pädagogische Hochschule Weingarten
I checked with strace that the PG server delivers to DBI:Pg the
hex string, so it must be converted somewhere (or in print ...) again to
UTF-8. See below.
Any ideas?
matthias
recvfrom(3, "T\0\0\0
\0\1d02name\0\0\0\0\0\0\0\0\0\0\21\377\377\377\377\377\377\0\0D\0\0\0\376\0\1\0\0\0\364\\x50c3a46461676f67697363686520486f6368736368756c65205765696e67617274656e2020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020C\0\0\0\rSELECT
1\0Z\0\0\0\5I", 16384, 0, NULL, NULL) = 308
write(1, "P\303\244dagogische Hochschule Weingarten
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
Re: SELECT d02name::bytea FROM ... && DBI::Pg
El día jueves, octubre 10, 2019 a las 06:46:02p. m. +0200, Francisco Olarte escribió: > Matthias: > > On Thu, Oct 10, 2019 at 1:25 PM Matthias Apitz wrote: > > I can SELECT && print a column in hex with: > > pos71=# select d02name::bytea from d02ben where d02bnr = '1048313' ; > > ... > > > > \x50c3a46461676f67697363686520486f6368736368756c65205765696e67617274656e2020 > > ... > > > > but when I use the same in Perl DBI::Pg with: > > $sth=$dbh->prepare( "select d02name::bytea from d02ben where d02bnr = > > '1048313'"); > ... > > It prints the UTF-8 string and not the hex string: > > May be because perl does not need the contents in hex DBI converts it > to a native string, which is very similar to a "bytea" inside the db ( > not in the wire protocol, but it does a similar thing for numbers ). Hmm. But *I* do need the content in hex to see if the varchar column contains correct encoded UTF-8 data. We're on the way to port a huge database application from Sybase to PostgreSQL and are facing any kind of problems one can think of. Magically, sometimes strings, expected to be coded in UTF-8, arrive in the Perl $variables coded in ISO-8859-1 and than cause other problems when German Umlauts should be translated into HTML encodings like ü etc. to be presented in the web browser. Perl (and Java) sucks, it does magic things below the surface of string (objects). That's why I like C :-) matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: Perl DBI converts UTF-8 again to UTF-8 before sending it to the server
Christoph,
May I come back to the UTF-8 problem, but now for the reading aspect:
I connect to the PG server with:
$dbh = DBI->connect($PGDB, $PGDB_USER, $PGDB_PASS,
{ pg_utf8_flag => 1,
pg_enable_utf8 => 1,
AutoCommit => 0,
RaiseError => 0,
PrintError => 0,
}
);
and do a SELECT for a column which contains UTF-8 data (I double checked
this with SQL and ::bytea):
$sth=$dbh->prepare(
"select d02name from d02ben where d02bnr = '1048313'")
or die "parse error\n".$DBI::errstr."\n";
$sth->execute
or die "exec error\n".$DBI::errstr."\n";
but when I now fetch the first row with:
@row = $sth->fetchrow_array;
$HexStr = unpack("H*", $row[0]);
print "HexStr: " . $HexStr . "\n";
print "$row[0]\n";
The resulting column contains ISO data:
HexStr:
50e46461676f67697363686520486f6368736368756c65205765696e67617274656e2020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020
Pdagogische Hochschule Weingarten
Following the man page of DBD::Pg the attribute pg_enable_utf8 => 1
should ensure that strings are returned from DBI with the UTF-8 flag
switched on. The server sends the string in UTF-8 as I can see with
strace:
...
recvfrom(3, "T\0\0\0
\0\1d02name\0\0\1\313\237\0\3\0\0\4\22\377\377\0\0\0|\0\0D\0\0\0\203\0\1\0\0\0yP\303\244dagogische
Hochschule Weingarten
C\0\0\0\rSELECT 1\0Z\0\0\0\5T", 16384, 0, NULL,
NULL) = 185
write(1, "HexStr:
50e46461676f67697363686520486f6368736368756c65205765696e67617274656e2020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020\n",
249) = 249
write(1, "P\344dagogische Hochschule Weingarten
But why it gets translated to ISO?
Thanks for your help again.
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
signature.asc
Description: PGP signature
DBD::Pg exorts char columns with trailing blanks
Hello, When we export char columns with our Perl tools, they come out with trailing blanks (in Sybase they don't). Can this be suppressed? Thanks matthias -- Sent using Dekko from my Ubuntu device
Re: DBD::Pg exorts char columns with trailing blanks
El día viernes, octubre 18, 2019 a las 03:01:58p. m. +0200, Tom Lane escribió: > Matthias Apitz writes: > > When we export char columns with our Perl tools, they come out with > > trailing blanks (in Sybase they don't). Can this be suppressed? > > Switch to varchar, perhaps? > > regards, tom lane Sometimes people does not know, what they propose. We have a historical 25 years grown ILS which runs on top of Sybase, Oracle, Informix ... and should now be ported to PostgreSQL. We can't simple switch internal table structures and adopt some 10.000.000 lines of code (or debug while it is now crashing). Thanks anyway. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub 3. Oktober! Wir gratulieren! Der Berliner Fernsehturm wird 50 aus: https://www.jungewelt.de/2019/10-02/index.php
PostgreSQL && data types in ESQL/C
Hello,
On our project roadmap to port our LMS (Library Management System) from
Sybase/Oracle to PostgreSQL we are now in the phase of addressing the
ESQL/C and C++ code parts (some million lines of code).
I wrote a small ESQL/C test code to see how the various data types are
handled.
In general: Is there any good manual about ESQL/C in PostgreSQL?
Because, even if there are standards any implementation has its details.
In detail:
I've created a table with the most used data types:
$ cat mytypes.sql
create table mytypes (
myint integer,
mychar char (4),
mydate date,
myvchar varchar(81),
myblob bytea
) ;
and have loaded a row with some data which is shown in pgsql as:
$ printf "select * from mytypes;\n" | psql -Usisis -d newsisis
myint | mychar | mydate | myvchar|myblob
---+++--+--
1 | char | 08.05.1945 | освобождение |
\xd0bed181d0b2d0bed0b1d0bed0b6d0b4d0b5d0bdd0b8d0b50a
(1 Zeile)
in the ESQL/C code the host variables are declared as:
EXEC SQL BEGIN DECLARE SECTION;
...
int myint;
char mychar[8];
char mydate[10+1];
char myvchar[81];
char myblob[1024];
...
EXEC SQL END DECLARE SECTION;
and the FETCH into these is done with:
EXEC SQL FETCH IN c_statename INTO :myint, :mychar, :mydate, :myvchar,
:myblob;
which gives with an ESQL/C test pgm which prints the above host
variables:
$ /usr/local/sisis-pap/pgsql/bin/ecpg embedded.pgc
$ gcc -m64 -o embedded embedded.c -I/usr/local/sisis-pap/pgsql/include
-L/usr/local/sisis-pap/pgsql/lib/ -lpq -lecpg
$ ./embedded
stmt: SELECT myint, mychar, mydate, myvchar, myblob FROM mytypes;
myint [1]
mychar [char]
mydate [08.05.1945]
myvchar [освобождение]
myblob [\xd0bed181d0b2d0bed0b1d0bed0b6d0b4d0b5d0bdd0b8d0b50a]
It seems(!):
- an int appears as binary integer
- all others types (even the column type 'date') appear as C type char*
- 'date', 'char' and 'varchar' are delivered as '\0' terminated strings
- 'bytea' appears as '\0' terminated string coded in hex with "\x" in front
Our DBCALL layer must convert these char strings in the data form the
application layer is expecting, for example a BLOB ('bytea') into a C struct
struct {
long blobLength;
char blobBytes[MAX_EXPECTED_BLOB];
} blob;
For example Sybase handles 'date' and 'bytea' in another way: 'date' is
a struct of two long and for 'bytea' an additional host variable
for the length must be used in FETCH, INSERT, ...
The above is a bit by try and error. Is there any good manual which
describes the ESQL/C details for PostgreSQL.
Thanks
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
"Glaube wenig, hinterfrage alles, denke selbst: Wie man Manipulationen
durchschaut"
"Believe little, scrutinise all, think by your own: How see through
manipulations"
ISBN-10: 386489218X
signature.asc
Description: PGP signature
type SERIAL in C host-struct
Hello,
We're struggling with the following problem (here show in a simplified
case).
We have in a PG 11.4 database a table with two columns: SERIAL, VARCHAR(11).
In the ESQL/C pgm the code is:
EXEC SQL BEGIN DECLARE SECTION;
...
struct {
int ser;
char name [11];
} host_struct;
EXEC SQL END DECLARE SECTION;
an INSERT with
strcpy(host_struct.name, "Sigrid");
host_struct.ser = 0;
EXEC SQL INSERT INTO lina VALUES (:host_struct);
works but, sets the SERIAL column to 0;
an INSERT with
EXEC SQL INSERT INTO lina VALUES (DEFAULT, :host_struct.name);
works correctly and increments the SERIAL on every INSERT:
printf "select * from lina WHERE name = 'Sigrid';\n" | psql -Usisis -d newsisis
lid | name
-+--
28 | Sigrid
29 | Sigrid
0 | Sigrid <*** this was with host_struct.ser = 0;
30 | Sigrid
31 | Sigrid
How the value for host_struct.ser must be given, as we do not want to
name all the struct members in the INSERT statement(s), the real structs
have plenty much columns, some ~30.
Thanks
matttias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
Re: type SERIAL in C host-struct
El día jueves, noviembre 07, 2019 a las 12:39:39p. m. +0100, Matthias Apitz
escribió:
>
> Hello,
>
> We're struggling with the following problem (here show in a simplified
> case).
>
> We have in a PG 11.4 database a table with two columns: SERIAL, VARCHAR(11).
>
> In the ESQL/C pgm the code is:
>
> EXEC SQL BEGIN DECLARE SECTION;
> ...
> struct {
> int ser;
> char name [11];
> } host_struct;
> EXEC SQL END DECLARE SECTION;
>
> an INSERT with
>
> strcpy(host_struct.name, "Sigrid");
> host_struct.ser = 0;
>
> EXEC SQL INSERT INTO lina VALUES (:host_struct);
>
> works but, sets the SERIAL column to 0;
>
> an INSERT with
>
> EXEC SQL INSERT INTO lina VALUES (DEFAULT, :host_struct.name);
>
> works correctly and increments the SERIAL on every INSERT:
At the end of the day we came up with the following solution:
strcpy(host_struct.name, "Sigrid");
EXEC SQL select nextval('lina_lid_seq') into :host_struct.ser;
EXEC SQL INSERT INTO lina VALUES ( :host_struct );
which seems to work fine. Any comments about side effects?
The layout of the table 'lina' is ( serial lid, varchar name )
Thanks,
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
"Glaube wenig, hinterfrage alles, denke selbst: Wie man Manipulationen
durchschaut"
"Believe little, scrutinise all, think by your own: How see through
manipulations"
ISBN-10: 386489218X
signature.asc
Description: PGP signature
Re: type SERIAL in C host-struct
El día sábado, noviembre 09, 2019 a las 04:31:53p. m. +0100, Peter J. Holzer
escribió:
> On 2019-11-07 20:14:47 +0100, Matthias Apitz wrote:
> > At the end of the day we came up with the following solution:
> >
> > strcpy(host_struct.name, "Sigrid");
> > EXEC SQL select nextval('lina_lid_seq') into :host_struct.ser;
> >
> > EXEC SQL INSERT INTO lina VALUES ( :host_struct );
> >
> > which seems to work fine. Any comments about side effects?
>
> You are performing two queries instead of one, so you have to wait for
> one extra round trip. Not a problem if client and server are on the same
> network or you insert a few host_structs per second. May be a problem if
> client and server are in different datacenters and you want to do
> hundreds of inserts per second.
Thanks for the feedback. Client and server run both on the same Linux
host. I found also the debug log of ESQL/C i.e. how to enable it with
ECPGdebug(int on, FILE *stream);
The resulting log is really fine for debugging our code:
[26301]: ECPGdebug: set to 1
[26301]: ECPGconnect: opening database newsisis on localhost port 5432 for
user sisis
[26301]: prepare_common on line 49: name s_statename; query: "SELECT myint,
mychar, mydate, myvchar, myblob FROM mytypes;"
[26301]: ecpg_execute on line 52: query: declare c_statename cursor for SELECT
myint, mychar, mydate, myvchar, myblob FROM mytypes;; with 0 parameter(s) on
connection newsisis
[26301]: ecpg_execute on line 52: using PQexec
[26301]: ecpg_process_output on line 52: OK: DECLARE CURSOR
[26301]: ecpg_execute on line 60: query: fetch in c_statename; with 0
parameter(s) on connection newsisis
[26301]: ecpg_execute on line 60: using PQexec
[26301]: ecpg_process_output on line 60: correctly got 1 tuples with 5 fields
[26301]: ecpg_get_data on line 60: RESULT: 1 offset: 4; array: no
[26301]: ecpg_get_data on line 60: RESULT: char offset: 8; array: no
[26301]: ecpg_get_data on line 60: RESULT: 08.05.1945 offset: 8; array: no
[26301]: ecpg_get_data on line 60: RESULT: освобождение offset: 81; array: no
...
What is missing are timestamps in enough precision. I will add this to
the code in postgresql-11.4/src/interfaces/ecpg/ecpglib/misc.c where the
implementation of ecpg_log() is.
Thanks
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
"Glaube wenig, hinterfrage alles, denke selbst: Wie man Manipulationen
durchschaut"
"Believe little, scrutinise all, think by your own: How see through
manipulations"
ISBN-10: 386489218X
Re: type SERIAL in C host-struct
El día sábado, noviembre 09, 2019 a las 07:45:31p. m. +0100, Matthias Apitz escribió: > Thanks for the feedback. Client and server run both on the same Linux > host. I found also the debug log of ESQL/C i.e. how to enable it with > > ECPGdebug(int on, FILE *stream); > > The resulting log is really fine for debugging our code: > > ... I added time stamps to the logging as: /tmp/ecpg.log: ... [18328] [12.11.2019 18:38:44:637]: ecpg_execute on line 120: query: insert into mytypes values ( $1 , $2 , $3 , $4 , $5 ); with 5 parameter(s) on connection newsisis [18328] [12.11.2019 18:38:46:881]: ecpg_execute on line 120: using PQexecParams [18328] [12.11.2019 18:38:46:881]: ecpg_free_params on line 120: parameter 1 = 99 [18328] [12.11.2019 18:38:46:881]: ecpg_free_params on line 120: parameter 2 = [18328] [12.11.2019 18:38:46:881]: ecpg_free_params on line 120: parameter 3 = 2000-01-01 [18328] [12.11.2019 18:38:46:881]: ecpg_free_params on line 120: parameter 4 = [18328] [12.11.2019 18:38:46:881]: ecpg_free_params on line 120: parameter 5 = \x6c696e6520 [18328] [12.11.2019 18:38:47:084]: ecpg_process_output on line 120: OK: INSERT 0 1 [18328] [12.11.2019 18:38:47:084]: ECPGtrans on line 126: action "commit"; connection "newsisis" [18328] [12.11.2019 18:38:47:260]: deallocate_one on line 0: name s_statename [18328] [12.11.2019 18:38:47:263]: ecpg_finish: connection newsisis closed The above shows an INSERT into a 'bytea' column of ~36 MByte (yes we have such large XML data for billing/accounting processes in the database). It takes ~0.2 sec to insert such a monster row. On INSERT/UPDATE the column data (XML) comes down from the application layer as a 'char *' pointer. I know with strlen(src) how long it is, I double its length and add 3 (for the "\x" in front and the '\0' at the end) and malloc the space for the host variable for INSERT/UPDATE and translate the octets to hex representation. When FETCH-ing such a column I do not know the resulting length of the bytea data for doing a malloc(). I could do before any FETCH a SELECT octet_length(myblob) FROM mytypes; but I do not know how expensive this would be. Any other idea to predict the needed space for the host variable on FETCH? matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub "Glaube wenig, hinterfrage alles, denke selbst: Wie man Manipulationen durchschaut" "Believe little, scrutinise all, think by your own: How see through manipulations" ISBN-10: 386489218X signature.asc Description: PGP signature
deep debug log for psql
Hello, We found and use for ESQL/C the debug feature: ECPGdebug(int on, FILE *stream); with very good results. Is there something similar for the psql interpreter to debug the query of complex SQL statements like this monster here: SELECT ha.hnr, count(*) FROM acq_haushalt ha, acq_booking bo WHERE ha.hjahr = 2000 AND ha.brgroup WHERE bu.bnr = bo.bnr AND bu.band = bo.band AND bu.exemplar = ( SELECT min(bo2.exemplar) FROM acq_booking b WHERE bo2.hnr = bo.hnr AND bo2.bnr = bo.bnr AND bo2.band = bo.band) AND bu.statusrech IN (0,1) ) GROUP BY ha.hnr; The -L logfilename flag of psql is not sufficient enough. It logs only the query and the final result. Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub "Glaube wenig, hinterfrage alles, denke selbst: Wie man Manipulationen durchschaut" "Believe little, scrutinise all, think by your own: How see through manipulations" ISBN-10: 386489218X signature.asc Description: PGP signature
status of CURSORs after DISCONNECT
Hello, When an ESQL/C written process issues a EXEC SQL DISCONNECT [connection]; do the opened CURSOR(s) still survive? We run into the problem that the father process issues DISCONNECT before forking children, the forked child CONNECTs to the same server and database again and "thinks" it has to CLOSE the CURSOR (perhaps a long standing bug in our application we port now from Sybase(...) to PostgreSQL). Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub "Glaube wenig, hinterfrage alles, denke selbst: Wie man Manipulationen durchschaut" "Believe little, scrutinise all, think by your own: How see through manipulations" ISBN-10: 386489218X
SQL operator '*='
Hello, I've here a smaller problem of our porting from Sybase/Oracle/Informix code to PostgreSQL; the code reads for the mentioned DBS: ... #ifdef DBSINF EXEC SQL DECLARE land_cursor CURSOR FOR SELECT stammprio, lkz, landbez, plkz, postbez, karenz1, karenz2, karenz3, land.wkz, webez, we, kurs, land.del FROM land, OUTER devisen WHERE land.wkz = devisen.wkz AND land.brgroup = devisen.brgroup AND land.brgroup = :brgroupHost_for_helpland_cursor ORDER BY stammprio, landbez; #endif #ifdef DBSORA EXEC SQL DECLARE land_cursor CURSOR FOR SELECT stammprio, lkz, landbez, plkz, postbez, karenz1, karenz2, karenz3, land.wkz, webez, we, kurs, land.del FROM land, devisen WHERE land.wkz = devisen.wkz (+) AND land.brgroup = devisen.brgroup (+) AND land.brgroup = :brgroupHost_for_helpland_cursor ORDER BY stammprio, landbez; #endif #ifdef DBSSYB EXEC SQL DECLARE land_cursor CURSOR FOR SELECT stammprio, lkz, landbez, plkz, postbez, karenz1, karenz2, karenz3, land.wkz, webez, we, kurs, land.del FROM land, devisen WHERE land.wkz *= devisen.wkz AND land.brgroup *= devisen.brgroup AND land.brgroup = :brgroupHost_for_helpland_cursor ORDER BY stammprio, landbez; #endif #ifdef DBSPOS EXEC SQL DECLARE land_cursor CURSOR FOR SELECT stammprio, lkz, landbez, plkz, postbez, karenz1, karenz2, karenz3, land.wkz, webez, we, kurs, land.del FROM land, devisen WHERE land.wkz *= devisen.wkz AND land.brgroup *= devisen.brgroup AND land.brgroup = :brgroupHost_for_helpland_cursor ORDER BY stammprio, landbez; #endif (the code for DBSPOS was just copied from Sybase). It compiles fine but raises on execution en error about operator '*=' is not supported... Any ideas about how to express this correctly in PostgreSQL? Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub "Glaube wenig, hinterfrage alles, denke selbst: Wie man Manipulationen durchschaut" "Believe little, scrutinise all, think by your own: How see through manipulations" ISBN-10: 386489218X
testing in ESQL/C if a CUSROR "foo" is open?
Hello,
Is there any way (without looking up the name in table 'pg_cursors') to see if a
cursor "foo" is still open?
Thanks
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
signature.asc
Description: PGP signature
Re: testing in ESQL/C if a CUSROR "foo" is open?
El día viernes, diciembre 27, 2019 a las 06:55:22a. m. +0100, Matthias Apitz escribió: > > Hello, > > Is there any way (without looking up the name in table 'pg_cursors') to see > if a > cursor "foo" is still open? > I found this hint about 'pg_cursors' in stackoverflow: https://stackoverflow.com/questions/51146931/postgres-finding-an-open-cursor-and-closing-it But did no see how to code this in ESQL/C, something like: EXEC SQL IF EXISTS(SELECT * FROM pg_cursors WHERE name = :cur) THEN CLOSE :cur; Which does not work. And, what concerns me as well is how expensive such test is before closing any cursor. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
How to reset a server error '25P02 in_failed_sql_transaction'
Hello, Due to a wrong human input in the GUI of our application our application server, from the point of view of the PostgreSQL server it is the PostgreSQL client, issues a broken ESQL/C command to the PostgreSQL server, here from our own debug logging the command and the error message of the server: posDebug: [27.12.2019 15:20:59:043] stmt: SELECT ctid, * from titel_datum WHERE desk>='*2019' AND feldnr IN ( 2 ) ORDER BY desk ASC , feldnr ASC posDebug: [27.12.2019 15:20:59:043] ESQL: PREPARE sid_titel_datum FROM :select_anw; sqlca sqlcode: -400 sqlerrm.sqlerrml: 61 sqlerrm.sqlerrmc: invalid input syntax for type date: »*2019« on line 918 ... All subsequent correct (SELECT ...) statements get rejected with, for example: ... posDebug: [27.12.2019 15:20:59:044] stmt: SELECT ctid, * from titel_datum WHERE desk>='31.12.1900' AND feldnr IN ( 2 ) ORDER BY desk ASC , feldnr ASC posDebug: [27.12.2019 15:20:59:044] ESQL: PREPARE sid_titel_datum FROM :select_anw; sqlca sqlcode: -400 sqlerrm.sqlerrml: 105 sqlerrm.sqlerrmc: current transaction is aborted, commands ignored until end of transaction block on line 918 sqlerrd: 0 0 0 0 0 0 sqlwarn: 0 0 0 0 0 0 0 0 sqlstate: 25P02 posSqlError=== ... Note: we are not in some kind TRANSACTION block, like 'EXEC SQL BEGIN TRANSACTION;' What is the correct way to abort the "transaction" as requested by the PG server to return to normal operations? Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: How to reset a server error '25P02 in_failed_sql_transaction'
El día martes, diciembre 31, 2019 a las 10:06:22a. m. +, Patrick FICHE escribió: > Hi, > > Which behavior are you looking for ? > If you want the following statements to succeed, I guess that you don't want > to be in a transaction context. > In this case, you should have a look at the following link : > https://www.postgresql.org/docs/11/ecpg-commands.html > By default, the AUTOCOMMIT is OFF but you can set it to ON and it should > solve your issue as only explicit transactions will keep a transaction > context. > > Regards, Hi, To summarize the different hints: - terminate the relevant backend process with pg_terminate_backend() - issue ROLLBACK - set AUTOCOMMIT TO ON Thanks, I will try these hints in the order from bottom to up, and as well to close/re-open the connection to the server, without the GUI client will note this; every GUI client has its own application server: GUI (Java in Windows) --> application server (ESQL/C Linux) --> PGserver Thanks (and btw: Happy New Jear!) matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub "Glaube wenig, hinterfrage alles, denke selbst: Wie man Manipulationen durchschaut" "Believe little, scrutinise all, think by your own: How see through manipulations" ISBN-10: 386489218X
ESQL/C FETCH of CHAR data delivers to much data for UTF-8
Hello,
We encounter the following problem with ESQL/C: Imagine a table with two
columns: CHAR(16) and DATE
The CHAR column can contain not only 16 bytes, but 16 Unicode chars,
which are longer than 16 bytes if one or more of the chars is a UTF-8 multibyte
encoded.
If one provides in C a host structure to FETCH the data as:
EXEC SQL BEGIN DECLARE SECTION;
struct r_d02ben_ec {
charstring[17];
chardate[11];
};
typedef struct r_d02ben_ec t_d02ben_ec;
t_d02ben_ec *hp_d02ben, hrec_d02ben;
EXEC SQL END DECLARE SECTION;
and fetches the data with ESQL/C as:
EXEC SQL FETCH hc_d02ben INTO :hrec_d02ben;
The generated C-code looks like this:
...
ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "fetch hc_d02ben",
ECPGt_EOIT,
ECPGt_char,&(hrec_d02ben.string),(long)17,(long)1,sizeof( struct
r_d02ben_ec ),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_char,&(hrec_d02ben.date),(long)11,(long)1,sizeof( struct
r_d02ben_ec ),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
...
As you can see for the first item the length 17 is sent to the PG server
together with the pointer to where the data should be stored
and for the second element the length 11 is sent (which is big enough to
receive in ASCII MM.DD. and a trailing \0).
What we now see using GDB is that for the first element all UTF-8 data
is returned, lets asume only one multibyte char, which gives 17 bytes,
not only 16, and the trailing NULL is already placed into the element for
the date. Now the function ECPGdo() returns the date as MM.DD.
into the area pointed to for the 2nd element and with this overwrites
the NULL terminator of the string[17] element. Result is later a
SIGSEGV because the expected string in string[17] is not NULL
terminated anymore :-)
I would call it a bug, that ECPGdo() puts more than 17 bytes (16 bytes +
NULL) as return into the place pointed to by the host var pointer when
the column in the database has more (UTF-8) chars as will fit into
16+1 byte.
Comments?
Proposals for a solution?
Thanks
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
ECPG call interface && filename
Hello, Actually, the ecpg pre-compiler resolves ESQL/C statements like EXEC SQL SELECT into a function call to the executer in the ecpglib as: ECPGdo(__LINE__, ...); where __LINE__ is later substituted by the C-precompiler by the current line number as an integer. The purpose is mostly for logging features like: [24304] [14.01.2020 12:05:18:433]: ECPGtrans on line 1108: action "rollback"; connection "sisis71" i.e. to have the line number in the log statement (btw: the date and timestamp is already a local modification we did to bring the log line in sync with other logs our application is writing). In a real world server application written in some hundreds foo.pgc files (a generated application interface with the pgc files based on the 400 tables in the database), it would be nice to have the call done like this: ECPGdo(__LINE__, __FILE__, ...) and the resulting log line like this: [24304] [14.01.2020 12:05:18:433]: ECPGtrans on line 1108 in file D01buch.c: action "rollback"; connection "sisis71" Before hacking this into the ecpglib code by my own, I wanted discuss this here or even file somewhere a structured change request for further development. Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
calculating the MD5 hash of role passwords in C
Hello,
If I look into the database I see:
sisis71=# select rolname, rolpassword from pg_authid where rolname = 'sisis';
rolname | rolpassword
-+-
sisis | md52f128a1fbbecc4b16462e8fc8dda5cd5
I know the clear text password of the role, it is simple 'sisis123', how
could I calculate the above MD5 hash from the clear text password, for
example in C? Which salt is used for the crypt(3) function?
Thanks
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
signature.asc
Description: PGP signature
Re: calculating the MD5 hash of role passwords in C
El día miércoles, enero 22, 2020 a las 07:58:47p. m. +0100, Christoph Moench-Tegeder escribió: > ## Matthias Apitz (g...@unixarea.de): > > > sisis71=# select rolname, rolpassword from pg_authid where rolname = > > 'sisis'; > > rolname | rolpassword > > -+- > > sisis | md52f128a1fbbecc4b16462e8fc8dda5cd5 > > > > I know the clear text password of the role, it is simple 'sisis123', how > > could I calculate the above MD5 hash from the clear text password, for > > example in C? Which salt is used for the crypt(3) function? > > The documentation on pg_authid has the details: > "The MD5 hash will be of the user's password concatenated to their user name." > https://www.postgresql.org/docs/12/catalog-pg-authid.html Thanks to all who replied. This is still not exactly what I was looking for. But has an interesting detail (salting the role password by adding the role name to it). An implementation with UNIX crypt(3) for MD5 would need an additional salt like '$1$salt' to encrypt 'sisis123sisis'. For sure the next place to look is the implementation of the PostgreSQL's md5() function. Thanks again matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub Deutschland raus aus der NATO! NATO raus aus Deutschland! Frieden mit Russland! Germany out of NATO! NATO out of Germany! Peace with Russia! ¡Alemania fuera de OTAN! ¡OTAN fuera de Alemania! ¡Paz con Rusia!
Re: calculating the MD5 hash of role passwords in C
El día miércoles, enero 22, 2020 a las 07:52:51p. m. +, Igor Neyman escribió: > -Original Message- > From: Matthias Apitz [mailto:g...@unixarea.de] > Sent: Wednesday, January 22, 2020 2:41 PM > To: pgsql-general@lists.postgresql.org > Subject: Re: calculating the MD5 hash of role passwords in C > > > -- > Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 > Public GnuPG key: http://www.unixarea.de/key.pub > > Deutschland raus aus der NATO! NATO raus aus Deutschland! Frieden mit > Russland! > Germany out of NATO! NATO out of Germany! Peace with Russia! > ¡Alemania fuera de OTAN! ¡OTAN fuera de Alemania! ¡Paz con Rusia! > > _ > > I don't think that political slogans in your signature are appropriate for > this forum. > This is technical just a signature and normally I delete it when posting to groups, I forgot it in this case. Said that, in any case, you are free to "think" whatever you want, as I am free to write whatever I think. And you are free to just ignore it. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: calculating the MD5 hash of role passwords in C
El día miércoles, enero 22, 2020 a las 03:32:17p. m. -0500, Justin escribió: > Not sure what your after but here is more information regarding how to > store passwords in Postgresql, ... I just want to write a piece of C-code to generate the same string as stored in rolpassword based on the roles password (and as I learned added the rolname), i.e. how to generate md52f128a1fbbecc4b16462e8fc8dda5cd5 from sisis123 (password) and sisis (rolename). And this, not to brute force credentials, but to understand the hash. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: calculating the MD5 hash of role passwords in C
El día jueves, enero 23, 2020 a las 05:15:37p. m. +0100, Christoph
Moench-Tegeder escribió:
> ## Matthias Apitz (g...@unixarea.de):
>
> > > The documentation on pg_authid has the details:
> > > "The MD5 hash will be of the user's password concatenated to their user
> > > name."
> > > https://www.postgresql.org/docs/12/catalog-pg-authid.html
> >
> > This is still not exactly what I was looking for. But has an interesting
> > detail (salting the role password by adding the role name to it). An
> > implementation with UNIX crypt(3) for MD5 would need an additional salt
> > like '$1$salt' to encrypt 'sisis123sisis'.
>
> It's not crypt(3). It's "the MD5 hash of the user's password concatenated
> to their user name".
> Try:
> perl -MDigest::MD5 -e 'print("md5" . Digest::MD5::md5_hex("sisis123" .
> "sisis") . "\n");'
Thanks!
Or one can use:
$ echo -n sisis123sisis | openssl md5 | sed 's/^.* /md5/'
md52f128a1fbbecc4b16462e8fc8dda5cd5
$ perl -MDigest::MD5 -e 'print("md5" . Digest::MD5::md5_hex("sisis123" .
"sisis") . "\n");'
md52f128a1fbbecc4b16462e8fc8dda5cd5
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
performance of loading CSV data with COPY is 50 times faster than Perl::DBI
Hello, Since ages, we transfer data between different DBS (Informix, Sybase, Oracle, and now PostgreSQL) with our own written tool, based on Perl::DBI which produces a CSV like export in a common way, i.e. an export of Oracle can be loaded into Sybase and vice versa. Export and Import is done row by row, for some tables millions of rows. We produced a special version of the tool to export the rows into a format which understands the PostgreSQL's COPY command and got to know that the import into PostgreSQL of the same data with COPY is 50 times faster than with Perl::DBI, 2.5 minutes ./. 140 minutes for around 6 million rows into an empty table without indexes. How can COPY do this so fast? matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: performance of loading CSV data with COPY is 50 times faster than Perl::DBI
El día lunes, febrero 03, 2020 a las 10:01:04a. m. -0600, Steven Lembark escribió: > On Fri, 31 Jan 2020 19:24:41 +0100 > Matthias Apitz wrote: > > > Hello, > > > > Since ages, we transfer data between different DBS (Informix, Sybase, > > Oracle, and now PostgreSQL) with our own written tool, based on > > Perl::DBI which produces a CSV like export in a common way, i.e. an > > export of Oracle can be loaded into Sybase and vice versa. Export and > > Import is done row by row, for some tables millions of rows. > > > > We produced a special version of the tool to export the rows into a > > format which understands the PostgreSQL's COPY command and got to know > > that the import into PostgreSQL of the same data with COPY is 50 times > > faster than with Perl::DBI, 2.5 minutes ./. 140 minutes for around 6 > > million rows into an empty table without indexes. > > > > How can COPY do this so fast? > > DBI is a wonderful tool, but not intended for bulk transfer. It > is useful for post-processing queries that extract specific > data in ways that SQL cannot readily handle. > > One big slowdown is the cycle of pull-a-row, push-a-row involves > signiicant latency due to database connections. That limits the > throughput. I should have mentioned this: the export is done on Linux to file and the import with that tool is read from such files. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
DBI && INSERT
Hello,
I spend today some hours to nail down and insert problem into our
database with DBI like:
my $rc = $my_dbh->do($my_sqlstatement);
which returns 1 in $rc (which the following flow in our script took as an
error). If one fired up the same string in $my_sqlstatement with pgsql
the result was always
INSERT 0 1
At the end of the day I watched with strace the communication on the
network between the DBI client and the PG server:
...
13576 sendto(3,
"6f7374656e20676573636872696562656e3a303a0a726561645f656469666163745f66696c65733a3a6d61696e20456e646520616d2031372e30322e3230323020756d2031363a30303a3234205568720a6c6f67206265656e6465742c206a65747a74207769726420696e7365727420646573206c6f677320696e206163715f7661726461746120766572737563687420286e757220696d207472616365207369636874626172290a',
\n 1)\0", 359, MSG_NOSIGNAL, NULL, 0) = 359
13576 poll([{fd=3, events=POLLIN|POLLERR}], 1, 4294967295) = 1 ([{fd=3,
revents=POLLIN}])
13576 recvfrom(3, "C\0\0\0\17INSERT 0 1\0Z\0\0\0\5I", 32768, 0, NULL, NULL) = 22
13576 write(4, "<- do= ( 1 ) [1 items] at read_edifact_files.pl line
8831\n", 62) = 62
13576 write(4, "-> $DBI::errstr (&) FETCH from lasth=HASH\n", 46) = 46
13576 write(4, "<- $DBI::errstr= undef\n", 27) = 27
...
i.e. the response on the network was (always) also 'INSERT 0 1' and DBI
misinterprets this as an error condition with 'undef' $DBI::errstr
string.
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Perl::DBI and TYPE of column
Hello,
We unload Sybase and Oracle data to migrate the database to PostgreSQL.
The loading is done very fast with PostgreSQL's COPY command.
During unload trailing blanks in all columns are discarded, because they
would cause problems during loading for INT and DATE columns. The
discarding is done like this after fetching the row into the array
@row_ary:
...
# SRP-25024: support for PostgreSQL: we remove on export trailing blanks
foreach my $i (0..$#row_ary) {
$row_ary[$i] =~ s/\s+$//;
# but for CHAR columns we keep one
# print $dba->{'sth'}->{NAME}->[$i] . " " . $dba->{'sth'}->{TYPE}->[$i]
. "\n";
# it seems that VARCHAR in Sybase is TYPE=1 and in Oracle TYPE=12
# see also
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36273.1570/html/sprocs/CIHHGDBC.htm
# and ftp://sqlstandards.org/SC32/SQL_Registry/
#
if ($dba->{'sth'}->{TYPE}->[$i] == 1 || $dba->{'sth'}->{TYPE}->[$i] ==
12) {
$row_ary[$i] =~ s/^$/ /;
}
}
My question here is: How I could get a copy of the document
ftp://sqlstandards.org/SC32/SQL_Registry/
Any copy available here in this list? Thanks
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Re: Perl::DBI and TYPE of column
El día Dienstag, März 03, 2020 a las 09:36:32 -0500, Tom Lane escribió: > Matthias Apitz writes: > > During unload trailing blanks in all columns are discarded, because they > > would cause problems during loading for INT and DATE columns. > > Really? > > regression=# select '123 '::int; > int4 > -- > 123 > (1 row) > > regression=# select '12-02-2019 '::date; > date > > 2019-12-02 > (1 row) The problem occurs when loading CSV data like ...||... into an INT column with COPY. I could make you an exact example. But this wasn't my question, my question is where the document is. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
\COPY to accept non UTF-8 chars in CHAR columns
Hello, We're moving databases from Sybase/Oracle to PostgreSQL 11.4 by unloading them with our own tool into a CSV like format and loading the rows with \COPY. One can imagine that in old databases, in use for a longer time, you will find any sort of dust which should not be there, for example ISO-8859-1 chars while the CHAR column should be in UTF-8. Needless to say, this data should not be there, it is just a fact, in parts caused by foreign data loaded into the Sybase/Oracle database, sometimes our own Java- or Perl-written software, breaks a longer CHAR column into two sequential rows (with an INT column to note the order), but does the break in the middle of an UTF-8 multi-byte, clumsy. :-( In short, it there a way to let \COPY accept such broken ISO bytes, just complaining about, but not stopping the insert of the row? Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
Re: \COPY to accept non UTF-8 chars in CHAR columns
El día sábado, marzo 28, 2020 a las 09:40:30a. m. +1300, Thomas Munro escribió:
> On Sat, Mar 28, 2020 at 4:46 AM Tom Lane wrote:
> > Matthias Apitz writes:
> > > In short, it there a way to let \COPY accept such broken ISO bytes, just
> > > complaining about, but not stopping the insert of the row?
> >
> > No. We don't particularly believe in the utility of invalid data.
> >
> > If you don't actually care about what encoding your data is in,
> > you could use SQL_ASCII as the database "encoding" and thereby
> > disable all UTF8-specific behavior. Otherwise, maybe this conversion
> > is a good time to clean up the mess?
>
> Something like this approach might be useful for fixing the CSV file:
>
> https://codereview.stackexchange.com/questions/185821/convert-a-mix-of-latin-1-and-utf-8-to-proper-utf-8
>
> I haven't tested that program but it looks like the right sort of
> approach; I remember writing similar logic to untangle the strange
> mixtures of Latin 1, Windows 1252, and UTF-8 that late 90s browsers
> used to send. That sort of approach can't fix every theoretical
> problem (some valid Latin1 sequences are also valid UTF-8 sequences)
> but it's doable with text in European languages.
Thomas,
This is a very good finding, thanks for this.
I played around a bit with the original code, and tested some
modifications to fit better in our problem... It works fine:
- it translates any ISO char, for example 0xfc (German Umlaut ü in ISO), into
the
correct UTF-8 coding 0xc3bc:
perl -e 'print pack("H*", "4040fc4040")' | ./convert2properUTF8 | hexdump -C
40 40 c3 bc 40 40 |@@..@@|
0006
- it translates a situation where 0xc3bc (German Umlaut ü in UTF-8
coding) was broken into two columns, one terminating in 0xc3 and the 2nd
row starting with 0xbc; this would give:
perl -e 'print pack("H*", "c3")' | ./convert2properUTF8 | hexdump -C
c3 83 |..|
0002
perl -e 'print pack("H*", "bc40")' | ./convert2properUTF8 | hexdump -C
c2 bc 40 |..@|
0003
i.e. 0xc3 is translated to 0xc383 and the 2nd half, the 0xbc to
0xc2bc, both translations have nothing to do with the original split 0xc3bc,
and
perhaps in this case it would be better to spill out a blank 0x40 for
each of the bytes which formed the 0xc3bc.
But this we will discuss here and align the code to our use cases.
Thanks again
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
pg_basebackup && long time storage of wal_archive/ content
Hello,
The run (as user 'postgres') on the server of the cmd:
pg_basebackup -U ${DBSUSER} -Ft -z -D
/data/postgresql11/backup-wal_archive-20200414-1/
produces correctly the following situtation on the server:
srap32dxr1:/data/postgresql11 # ls -ltrh backup-20200414-1
total 1.7G
-rw--- 1 postgres postgres 1.7G Apr 14 16:20 base.tar.gz
-rw--- 1 postgres postgres 17K Apr 14 16:20 pg_wal.tar.gz
srap32dxr1:/data/postgresql11 # ls -ltrh data/pg_wal
total 81M
-rw--- 1 postgres postgres 16M Apr 14 15:11 0001000C009F
-rw--- 1 postgres postgres 16M Apr 14 15:20 0001000C00A0
-rw--- 1 postgres postgres 16M Apr 14 16:10 0001000C00A1
-rw--- 1 postgres postgres 16M Apr 14 16:20 0001000C00A2
-rw--- 1 postgres postgres 342 Apr 14 16:20
0001000C009D.0028.backup
drwx-- 2 postgres postgres 36K Apr 14 16:25 archive_status
-rw--- 1 postgres postgres 16M Apr 14 16:50 0001000C009E
srap32dxr1:/data/postgresql11 # ls -ltrh wal_archive/
total 49M
-rw--- 1 postgres postgres 16M Apr 14 15:20 0001000C009B
-rw--- 1 postgres postgres 16M Apr 14 16:10 0001000C009C
-rw--- 1 postgres postgres 16M Apr 14 16:20 0001000C009D
-rw--- 1 postgres postgres 342 Apr 14 16:20
0001000C009D.0028.backup
The content of the dir wal_archive/ is growing and growing over the
time.
The pg_wat.tar.gz contains:
srap32dxr1:/data/postgresql11 # tar tzf backup-20200414-1/pg_wal.tar.gz
0001000C009D
i..e. the files 0001000C009B ... 0001000C009D
could be removed in wal_archive/
Correct? Or how is the long time storage of this dir?
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Re: pg_basebackup && long time storage of wal_archive/ content
El día Dienstag, April 14, 2020 a las 08:28:35 -0700, Adrian Klaver escribió:
> On 4/14/20 8:00 AM, Matthias Apitz wrote:
> >
> > Hello,
> >
> > The run (as user 'postgres') on the server of the cmd:
> >
> > pg_basebackup -U ${DBSUSER} -Ft -z -D
> > /data/postgresql11/backup-wal_archive-20200414-1/
> >
> > produces correctly the following situtation on the server:
> >
> > srap32dxr1:/data/postgresql11 # ls -ltrh backup-20200414-1
> > total 1.7G
> > -rw--- 1 postgres postgres 1.7G Apr 14 16:20 base.tar.gz
> > -rw--- 1 postgres postgres 17K Apr 14 16:20 pg_wal.tar.gz
> > srap32dxr1:/data/postgresql11 # ls -ltrh data/pg_wal
> > total 81M
> > -rw--- 1 postgres postgres 16M Apr 14 15:11 0001000C009F
> > -rw--- 1 postgres postgres 16M Apr 14 15:20 0001000C00A0
> > -rw--- 1 postgres postgres 16M Apr 14 16:10 0001000C00A1
> > -rw--- 1 postgres postgres 16M Apr 14 16:20 0001000C00A2
> > -rw--- 1 postgres postgres 342 Apr 14 16:20
> > 0001000C009D.0028.backup
> > drwx-- 2 postgres postgres 36K Apr 14 16:25 archive_status
> > -rw--- 1 postgres postgres 16M Apr 14 16:50 0001000C009E
> > srap32dxr1:/data/postgresql11 # ls -ltrh wal_archive/
> > total 49M
> > -rw--- 1 postgres postgres 16M Apr 14 15:20 0001000C009B
> > -rw--- 1 postgres postgres 16M Apr 14 16:10 0001000C009C
> > -rw--- 1 postgres postgres 16M Apr 14 16:20 0001000C009D
> > -rw--- 1 postgres postgres 342 Apr 14 16:20
> > 0001000C009D.0028.backup
> >
> > The content of the dir wal_archive/ is growing and growing over the
> > time.
> >
> > The pg_wal.tar.gz contains:
> >
> > srap32dxr1:/data/postgresql11 # tar tzf backup-20200414-1/pg_wal.tar.gz
> > 0001000C009D
> >
> > i..e. the files 0001000C009B ... 0001000C009D
> > could be removed in wal_archive/
> >
> > Correct? Or how is the long time storage of this dir?
>
> pg_basebackup is a point in time backup tool.
Adrian,
Thanks for the answer. What do you mean by 'point in time backup tool'?
And, coming back to my question, can older WAL in wal_archive/ be
removed without any risks if the daily
pg_wal.tar.gz
base.tar.gz
are moved to a safe place?
> If you want something that
> continuously archives and prunes as it goes then you probably want to look
> at the tools below:
>
> https://pgbackrest.org/
>
> https://www.pgbarman.org/
>
> https://postgrespro.github.io/pg_probackup/
Thanks, I will have a look into the tools.
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'
Hello, I encounter from time to time that one of our ESQL/C clients hangs with the details described below for ever, while accessing on startup a table 'sisisinst' which has only some 50 rows, all of CHAR columns. Other ESQL/C clients start up fine (they all check this table 'sisisinst' on startup to verify the correct version of our application) Also SQL works fine. What can I do to get to the source of the problem? Thanks matthias ESQL/C statement: EXEC SQL PREPARE sid_sisisinst FROM :select_anw; host variable :select_anw contains: (gdb) p select_anw $2 = "SELECT ctid, * from sisisinst WHERE version = :v1", '\000' process PID 23957 is hanging in poll() on network socket to PostgreSQL server: (Version 11.4 on Linux) (gdb) bt #0 0x7ff9020c2664 in poll () from /lib64/libc.so.6 #1 0x7ff90144c22f in pqSocketPoll () from /usr/local/sisis-pap/pgsql/lib/libpq.so.5 #2 0x7ff90144c0ff in pqSocketCheck () from /usr/local/sisis-pap/pgsql/lib/libpq.so.5 #3 0x7ff90144bf83 in pqWaitTimed () from /usr/local/sisis-pap/pgsql/lib/libpq.so.5 #4 0x7ff90144bf55 in pqWait () from /usr/local/sisis-pap/pgsql/lib/libpq.so.5 #5 0x7ff901447d3a in PQgetResult () from /usr/local/sisis-pap/pgsql/lib/libpq.so.5 #6 0x7ff901448478 in PQexecFinish () from /usr/local/sisis-pap/pgsql/lib/libpq.so.5 #7 0x7ff90144819a in PQprepare () from /usr/local/sisis-pap/pgsql/lib/libpq.so.5 #8 0x7ff901cc788d in prepare_common () from /usr/local/sisis-pap/pgsql/lib/libecpg.so.6 #9 0x7ff901cc7a26 in ECPGprepare () from /usr/local/sisis-pap/pgsql/lib/libecpg.so.6 #10 0x7ff9044a86ee in select_record (scroll=1, lock=0, key=2, sel_anw=0x7ffed86186c0 "SELECT ctid, * from %s", where_anw=0x7ffed8617330 "%s = :v1", p_daten=0x7ff904dd7700 , i_between=0, p_oben=0x7ffed86171d0, order_by=0x0, auf_ab=0x0, group_by=0x0, having=0x0, into_temp=0x0, count=0x7ffed8617328) at sisisinst.pgc:925 #11 0x7ff9044a7a8d in sisisinst (zugriff=1, scroll=1, lock=0, key=2, sto=-2, p_daten=0x7ffed8618c30, sel_anw=0x7ffed86186c0 "SELECT ctid, * from %s", where_anw=0x7ffed8617330 "%s = :v1", p_btw_daten=0x0, order_by=0x0, auf_ab=0x0, group_by=0x0, having=0x0, into_temp=0x0, count=0x7ffed8617328) at sisisinst.pgc:450 #12 0x7ff9043718a5 in DB_rdir (tabmodul=0x7ff9044a7639 , key=2, scroll=1, lock=0, p_daten=0x7ffed8618c30) at dbcall.pgc:1665 #13 0x7ff90436f6c9 in DB_ChkVer () at dbcall.pgc:307 #14 0x7ff9043708d3 in DB_opdbP (mode=1) at dbcall.pgc:840 #15 0x7ff90436fff8 in DB_opdb () at dbcall.pgc:543 #16 0x00405efa in InitIndex (setid=FSTAB_Personen) at ./INDEX.c:1000 #17 0x00405241 in main (argc=2, argv=0x7ffed8619fa8) at ./INDEX.c:412 running PostgreSQL processes: # ps ax | grep postgres: 329 pts/13 S+ 0:00 grep --color=auto postgres: 2354 ?Ss 0:17 postgres: checkpointer 2355 ?Ss 0:09 postgres: background writer 2356 ?Ss 0:10 postgres: walwriter 2357 ?Ss 0:04 postgres: autovacuum launcher 2358 ?Ss 0:00 postgres: archiver last was 0001000F0062 2359 ?Ss 0:31 postgres: stats collector 2360 ?Ss 0:00 postgres: logical replication launcher 14044 ?Ss 0:00 postgres: sisis sisis ::1(53426) idle 14100 ?Ss 0:00 postgres: sisis sisis ::1(53432) idle 16852 ?Ss 0:00 postgres: sisis sisis ::1(53486) idle 17575 ?Ss 0:00 postgres: sisis sisis ::1(48736) idle 17577 ?Ss 0:00 postgres: sisis sisis ::1(48742) idle 17583 ?Ss 0:00 postgres: sisis sisis 10.23.33.19(49630) idle 17584 ?Ss 0:00 postgres: sisis sisis 10.23.33.19(49632) idle 17585 ?Ss 0:00 postgres: sisis sisis 10.23.33.19(49634) idle 17587 ?Ss 0:00 postgres: sisis sisis ::1(48762) idle 17590 ?Ss 0:00 postgres: sisis sisis ::1(48770) idle 17593 ?Ss 0:00 postgres: sisis sisis ::1(48778) idle 17609 ?Ss 0:04 postgres: sisis sisis ::1(48794) idle 17612 ?Ss 0:00 postgres: sisis sisis ::1(48802) idle 17615 ?Ss 0:04 postgres: sisis sisis ::1(48810) idle 17618 ?Ss 0:00 postgres: sisis sisis ::1(48818) idle 17621 ?Ss 0:08 postgres: sisis sisis ::1(48826) idle 17866 ?Ss 0:00 postgres: sisis sisis 10.23.33.19(49758) idle 17868 ?Ss 0:00 postgres: sisis sisis ::1(48886) idle 17907 ?Ss 0:00 postgres: sisis sisis ::1(48900) idle 17948 ?Ss 0:00 postgres: sisis sisis ::1(48914) idle 21457 ?Ss 0:00 postgres: sisis sisis ::1(53664) idle 23958 ?Ss 0:00 postgres: sisis sisis ::1(53742) idle ^ lsof -P -p 23957 | grep 5432 INDEX 23957 sisis2u IPv6 117475261 0t0
Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'
El día Montag, April 27, 2020 a las 08:40:04 -0400, Tom Lane escribió: > Matthias Apitz writes: > > I encounter from time to time that one of our ESQL/C clients hangs with > > the details described below for ever, while accessing on startup a table > > 'sisisinst' which has only some 50 rows, all of CHAR columns. Other ESQL/C > > clients > > start up fine (they all check this table 'sisisinst' on startup to > > verify the correct version of our application) Also SQL works > > fine. > > > What can I do to get to the source of the problem? Thanks > > Can you get a stack trace from the connected backend? Or even just look > at what it's doing according to pg_stat_activity? (If it's a reasonably > modern server, the wait-condition fields would be interesting.) srap32dxr1:/opt/lib/sisis/catserver/bin # lsof -P | grep 53742 INDEX 23957 sisis2u IPv6 117475261 0t0TCP localhost:53742->localhost:5432 (ESTABLISHED) postmaste 23958 postgres9u IPv6 117478789 0t0TCP localhost:5432->localhost:53742 (ESTABLISHED) 'INDEX' is the hanging proc; PID 23958 its server; gdb shows: # gdb /usr/local/sisis-pap/pgsql/bin/postgres 23958 ... (gdb) bt #0 0x7fd567776000 in epoll_pwait () from /lib64/libc.so.6 #1 0x0084476c in WaitEventSetWaitBlock () #2 0x00844647 in WaitEventSetWait () #3 0x006f89d2 in secure_read () #4 0x00707425 in pq_recvbuf () #5 0x00707709 in pq_discardbytes () #6 0x00707aba in pq_getmessage () #7 0x0086b478 in SocketBackend () #8 0x0086b4c4 in ReadCommand () #9 0x0086fda9 in PostgresMain () #10 0x007dde15 in BackendRun () #11 0x007dd595 in BackendStartup () #12 0x007d9c9b in ServerLoop () #13 0x007d9556 in PostmasterMain () #14 0x0070e2a5 in main () sisis=# select * from pg_stat_activity where client_port = 53742; datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type 93831 | sisis | 23958 |16384 | sisis | | ::1 | | 53742 | 27.04.2020 12:37:46.941441 CEST | | | 27.04.2020 12:37:46.943073 CEST | Client | ClientRead | idle | | | | client backend (1 Zeile) HIH && Kinde Regards matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'
As the client seems to be waiting on the server and the server on the client... could it have been caused by this in /etc/hosts (what our IT department configured): /etc/hosts: 127.0.0.1 localhost # special IPv6 addresses ::1 localhost ipv6-localhost ipv6-loopback i.e. the client has sent its request on IPv4 and the server answered on IPv6? I will remove the word 'localhost' from the IPv6 addr and restart everything, or do you want to see any additional inforation? Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'
I've added the proposed log line to the server code in
src/backend/libpq/pqcomm.c
and restarted the server with this. In the log file
/data/postgresql11/data/serverlog
are other messages about length of packages, like:
2020-04-28 09:49:49.877 CEST [16229] LOG: invalid length of startup packet
2020-04-28 09:49:50.117 CEST [16231] LOG: invalid length of startup packet
They're coming from:
grep 'invalid length of startup packet' src/backend/*/*.c
src/backend/postmaster/postmaster.c:
errmsg("invalid length of startup packet")));
I will change the code there to to get the 'len' printed with the
message
...
if (len < (int32) sizeof(ProtocolVersion) ||
len > MAX_STARTUP_PACKET_LENGTH)
{
ereport(COMMERROR,
(errcode(ERRCODE_PROTOCOL_VIOLATION),
errmsg("invalid length %d of startup packet",
len)));
return STATUS_ERROR;
}
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
How to move a 11.4 cluster to another Linux host, but empty?
Hello,
I've a 11.4 cluster on a Linux host with 3 production database, all
below /data/postgresql11.4/ (i.e. the cluster itself is
/data/postgresql11.4/data) fully configured an running fine. I want to
move it "cold" (i.e. when it is shutdown) by tar to another host, BUT
without the database (due to PII and GDPR relevant data). Between the
source and target host there is no network.
Of course I could, after moving all the PostgreSQL software (all is below a top
level directory /usr/local/sisis-pap/pgsql, compiled by me) and setup the
cluster from scratch on the other host, but I wanted to have it all in one
shoot by tar.
Is there a way to exclude the databases from the move by tar, or by some
sophisticated pg_* command?
Thanks
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Re: How to move a 11.4 cluster to another Linux host, but empty?
Hi Paul,
El día viernes, mayo 01, 2020 a las 10:23:37a. m. +0200, Paul Förster escribió:
>
> you may have a look at the tar man page. tar in Linux has the
> --exclude=PATTERN option.
I do know the man page(s) of tar ant the --exclude=PATTERN.
> You may specify the PGDATA directory there.
This is, what I wanted to know: that the cluster can be moved without
the PGDATA dir and still works fine.
> Also, why do you have the PGDATA directory inside the software home
> directory? That makes no sense and only leads to problems when updating or,
> in your case, moving.
>
I don't. The software is below
/usr/local/sisis-pap/pgsql
and the cluster is below
/data/postgres11.4/data.
Thanks
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Re: How to move a 11.4 cluster to another Linux host, but empty?
El día viernes, mayo 01, 2020 a las 10:31:48a. m. +0200, Matthias Apitz escribió: > Hi Paul, > > El día viernes, mayo 01, 2020 a las 10:23:37a. m. +0200, Paul Förster > escribió: > > > > > you may have a look at the tar man page. tar in Linux has the > > --exclude=PATTERN option. > > I do know the man page(s) of tar ant the --exclude=PATTERN. > > > You may specify the PGDATA directory there. > > This is, what I wanted to know: that the cluster can be moved without > the PGDATA dir and still works fine. > This will not work! The cluster was created with: $ /usr/local/sisis-pap/pgsql/bin/pg_ctl -D /data/prosgresql11/data initdb and consequently, PGDATA is /data/prosgresql11/data. This directory contains a lot of subirs and config files: srap32dxr1:/home/sisis/guru # du -s /data/postgresql11/data/* | sort -n 4 /data/postgresql11/data/PG_VERSION 4 /data/postgresql11/data/pg_commit_ts 4 /data/postgresql11/data/pg_dynshmem 4 /data/postgresql11/data/pg_ident.conf 4 /data/postgresql11/data/pg_replslot 4 /data/postgresql11/data/pg_serial 4 /data/postgresql11/data/pg_snapshots 4 /data/postgresql11/data/pg_stat 4 /data/postgresql11/data/pg_tblspc 4 /data/postgresql11/data/pg_twophase 4 /data/postgresql11/data/postgresql.auto.conf 4 /data/postgresql11/data/postmaster.opts 4 /data/postgresql11/data/postmaster.pid 8 /data/postgresql11/data/pg_hba.conf 12 /data/postgresql11/data/pg_notify 16 /data/postgresql11/data/pg_logical 24 /data/postgresql11/data/postgresql.conf 28 /data/postgresql11/data/pg_multixact 196 /data/postgresql11/data/pg_subtrans 732 /data/postgresql11/data/pg_stat_tmp 940 /data/postgresql11/data/global 1328/data/postgresql11/data/pg_xact 76076 /data/postgresql11/data/serverlog.20200414 81988 /data/postgresql11/data/pg_wal 90268 /data/postgresql11/data/serverlog 16672568/data/postgresql11/data/base The databases (or it least their content) are below /data/postgresql11/data/base (because of the size of ~81 GByte). So the question remains: What I have to move by tar to get the cluster setup on the new host, but without the databases in it? If this is not possible, I will only copy over the software from /usr/local/sisis-pap/pgsql/ and create the cluster from scratch. Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: How to move a 11.4 cluster to another Linux host, but empty?
El día sábado, mayo 02, 2020 a las 03:23:52p. m. +0200, Paul Förster escribió: > /usr/local/sisis-pap/pgsql <= software with bin, lib64, share, etc... > /data/postgresql11/data <= PGDATA Exactly, his is the setup above. I will move /usr/local/sisis-pap/pgsql by tar; and I want to move some parts(!) of the cluster /data/postgresql11/data (configuration etc.), but not the databases included in /data/postgresql11/data, like the cluster(!) in PGDATA was before having created any database(!) in it. As I said from the beginning: moving the cluster but without the databases in it. Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: How to move a 11.4 cluster to another Linux host, but empty?
El día sábado, mayo 02, 2020 a las 08:23:52a. m. -0700, Adrian Klaver escribió: > On 5/2/20 8:02 AM, Paul Förster wrote: > > Hi Adrian, > > > >> On 02. May, 2020, at 16:59, Adrian Klaver > >> wrote: > >> 2) To create a new base/ you will need to initdb data/ and that is not > >> possible as initdb will not work on a populated data/. > > > > you do the initdb and then copy the *.conf files from the tar over the > > newly created ones. After that, launch the cluster. > > Yeah I know, I do that on a fairly regular basis. The intent was to let > the OP know that ripping base/ out of cluster and transplanting a new > one in is not possible. OK, lesson learned: Next time I will do a tar backup of PGDATA after the configuration of the cluster and before creating any database in it. Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'
El día lunes, abril 27, 2020 a las 09:40:39a. m. -0400, Tom Lane escribió:
> Matthias Apitz writes:
> > El día Montag, April 27, 2020 a las 08:40:04 -0400, Tom Lane escribió:
> >> Can you get a stack trace from the connected backend?
>
> > (gdb) bt
> > #0 0x7fd567776000 in epoll_pwait () from /lib64/libc.so.6
> > #1 0x0084476c in WaitEventSetWaitBlock ()
> > #2 0x00844647 in WaitEventSetWait ()
> > #3 0x006f89d2 in secure_read ()
> > #4 0x00707425 in pq_recvbuf ()
> > #5 0x00707709 in pq_discardbytes ()
> > #6 0x00707aba in pq_getmessage ()
> > #7 0x0086b478 in SocketBackend ()
> > #8 0x0086b4c4 in ReadCommand ()
> > #9 0x0086fda9 in PostgresMain ()
>
> Oh, that is *very* interesting, because there is only one caller of
> pq_discardbytes:
>
> /*
> * Allocate space for message. If we run out of room (ridiculously
> * large message), we will elog(ERROR), but we want to discard the
> * message body so as not to lose communication sync.
> */
> PG_TRY();
> {
> enlargeStringInfo(s, len);
> }
> PG_CATCH();
> {
> if (pq_discardbytes(len) == EOF)
> ereport(COMMERROR,
> (errcode(ERRCODE_PROTOCOL_VIOLATION),
> errmsg("incomplete message from client")));
> ...
>
> What this code intends to handle is the case where the client has sent a
> message that is so long that the backend hasn't enough memory to buffer
> it. What's actually happened, more likely, is that the received message
> length is corrupt and just appears to be large, since the client-side
> trace shows that libpq has sent what it has to send and is now waiting for
> a reply. If the received length were correct then the pq_discardbytes
> call would have completed after eating the message.
>
> So what it looks like is that something is corrupting data on its
> way from the client to the server. Flaky firewall maybe? If you're
> using SSL, maybe an SSL library bug? I'm reduced to speculation at
> this point. It's hard even to say what to do to gather more info.
> If you could reproduce it then I'd suggest watching the connection
> with wireshark or the like to see what data is actually going across
> the wire ... but IIUC it's pretty random, so that approach seems
> unlikely to work.
>
> If you're in a position to run a modified server, you could try
> inserting a debug log message:
>
> }
> PG_CATCH();
> {
> + elog(COMMERROR, "bogus received message length: %d", len);
> if (pq_discardbytes(len) == EOF)
> ereport(COMMERROR,
>
> (This is in src/backend/libpq/pqcomm.c, around line 1300 as of HEAD.)
> While this seems unlikely to teach us a huge amount, perhaps the
> value of the incorrect length would be informative.
>
> Are you always seeing this error at the exact same place so far as
> the client side is concerned? It's hard to see why a transport-level
> problem would preferentially affect one particular query ...
I've added the printout of the length in this case and another one, and
see this in the server's log file:
2020-05-04 10:05:49.977 CEST [32092] LOG: invalid length 33554940 of startup
packet
2020-05-04 10:05:50.207 CEST [32094] LOG: invalid length 33554940 of startup
packet
2020-05-04 12:32:50.781 CEST [20334] LOG: bogus received message length:
1650422894
2020-05-04 12:36:41.293 CEST [20380] LOG: bogus received message length:
1650422894
2020-05-04 12:39:39.461 CEST [20441] LOG: bogus received message length:
1650422894
2020-05-04 13:01:50.566 CEST [24222] LOG: bogus received message length:
1650422894
Any comments? Thanks
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'
El día martes, mayo 05, 2020 a las 04:18:02p. m. -0400, Tom Lane escribió: > Matthias Apitz writes: > > El día lunes, abril 27, 2020 a las 09:40:39a. m. -0400, Tom Lane escribió: > >> If you're in a position to run a modified server, you could try > >> inserting a debug log message: > > > I've added the printout of the length in this case and another one, and > > see this in the server's log file: > > > 2020-05-04 10:05:49.977 CEST [32092] LOG: invalid length 33554940 of > > startup packet > > 2020-05-04 10:05:50.207 CEST [32094] LOG: invalid length 33554940 of > > startup packet > > 2020-05-04 12:32:50.781 CEST [20334] LOG: bogus received message length: > > 1650422894 > > 2020-05-04 12:36:41.293 CEST [20380] LOG: bogus received message length: > > 1650422894 > > 2020-05-04 12:39:39.461 CEST [20441] LOG: bogus received message length: > > 1650422894 > > 2020-05-04 13:01:50.566 CEST [24222] LOG: bogus received message length: > > 1650422894 > > Hmph. That confirms that we're getting a bogus message length, but not > much more. It's quite interesting though that the bogus value is always > the same. According to my calculator 1650422894 corresponds to ASCII > "b_tn", or possibly "nt_b" depending on what you want to assume about > endianness, which looks tantalizingly like it could be a fragment of a > SQL query. So I'm still leaning to the idea that the client has sent > a malformed query stream --- but how? Or if the data got corrupted in > transit, how did that happen? > > Can you work backwards to what the client was doing just before the > point at which it hangs? It's likely that the particular PQprepare > call it's stuck on is just the victim of prior misfeasance. If you > can find "b_tn" or "nt_b" in any strings the client should have been > sending up to this point, that might shed light too. $ printf "0x%x\n" 1650422894 0x625f746e $ printf "0x%x\n" 1650422894 | xxd -r b_tn I will try to add in the case of the 'bogus received message length...' more logging of the content of the message. The client in question is written in ESQL/C and I will enable the (very good) ESQL/C logging feature of this interface to get an understanding what the client was sending before this connection gets stuck. We will produce tons of log files, but apart of Linux strace or tcpdump with even more tons, I don't see any way at the moment. The situation is relatively seldom. Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'
El día Dienstag, April 28, 2020 a las 10:10:18 +0200, Matthias Apitz escribió:
>
> I've added the proposed log line to the server code in
> src/backend/libpq/pqcomm.c
> and restarted the server with this. In the log file
> /data/postgresql11/data/serverlog
> are other messages about length of packages, like:
>
> 2020-04-28 09:49:49.877 CEST [16229] LOG: invalid length of startup packet
> 2020-04-28 09:49:50.117 CEST [16231] LOG: invalid length of startup packet
>
> They're coming from:
>
> grep 'invalid length of startup packet' src/backend/*/*.c
> src/backend/postmaster/postmaster.c:
> errmsg("invalid length of startup packet")));
>
> I will change the code there to to get the 'len' printed with the
> message
>
> ...
> if (len < (int32) sizeof(ProtocolVersion) ||
> len > MAX_STARTUP_PACKET_LENGTH)
> {
> ereport(COMMERROR,
> (errcode(ERRCODE_PROTOCOL_VIOLATION),
> errmsg("invalid length %d of startup
> packet", len)));
> return STATUS_ERROR;
> }
I'm still trying to catch from where (i.e. from which client connection) the
above
message are originated. Over the actual weeken(!), I see the following
picture:
# egrep 'bogus|incomplete message from|invalid length'
/data/postgresql11/data/serverlog
...
2020-05-09 05:35:20.124 CEST [6905] LOG: invalid length 33554940 of startup
packet
2020-05-09 05:35:20.368 CEST [6907] LOG: invalid length 33554940 of startup
packet
2020-05-09 05:38:14.381 CEST [7186] LOG: invalid length 33554940 of startup
packet
2020-05-09 05:38:14.669 CEST [7246] LOG: invalid length 33554940 of startup
packet
2020-05-10 05:34:56.716 CEST [13241] LOG: invalid length 33554940 of startup
packet
2020-05-10 05:34:56.942 CEST [13243] LOG: invalid length 33554940 of startup
packet
2020-05-10 05:37:46.599 CEST [13921] LOG: invalid length 33554940 of startup
packet
2020-05-10 05:37:49.577 CEST [14017] LOG: invalid length 33554940 of startup
packet
i.e. the messages only appear short after 5:3x am; this is exactly the
time when (based on cronjobs) our software is deployed to this server
and all servers (the clients for PostgreSQL) are started. Following
questions:
1. In addition to the above code, can I get somehow the connecting client's
PID? If
so, I could get from its PID its command line from the /proc file
system. This would help to instrument the client side with logging.
There are some hundred clients of different server types booting up
at this time frame after the deployment.
2. Can I get something out of the buffer of the startup packet? For
example, with adding to the above change something like:
{
char buf[1024+1];
memset (buf, 0, sizeof(buf));
pq_getbytes(buf, 1024);
buf[1024] = '\0';
ereport(COMMERROR, (errcode(ERRCODE_PROTOCOL_VIOLATION),
errmsg("first 1024 bytes of startup packet [%s]", buf)));
}
Thanks
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'
El día Sonntag, Mai 10, 2020 a las 11:33:41 -0400, Tom Lane escribió:
> Matthias Apitz writes:
> > 1. In addition to the above code, can I get somehow the connecting client's
> > PID? If
>
> Not in any portable way. If you believe that the offending client is on
> the local system, you could try building something involving netstat,
> lsof, etc. getpeereid() might be useful too.
I launched a shell script to get some more information in this case:
...
if (len < (int32) sizeof(ProtocolVersion) ||
len > MAX_STARTUP_PACKET_LENGTH)
{
int pid = getpid();
char cmd[80];
sprintf(cmd, "/home/sisis/guru/getClient.sh %d", pid);
system(cmd);
ereport(COMMERROR,
(errcode(ERRCODE_PROTOCOL_VIOLATION),
errmsg("invalid length %d of startup packet",
len)));
return STATUS_ERROR;
}
...
The process with the PID in question is 'postmaster':
PS: 14622 ? Ss 0:00 /usr/local/sisis-pap/pgsql/bin/postmaster -D
/data/postgresql11/data
lsof -P -p 14622
COMMAND PID USER FD TYPEDEVICE SIZE/OFF NODE NAME
postmaste 14622 postgres cwd DIR 254,0 4096 5373968
/data/postgresql11/data
postmaste 14622 postgres rtd DIR 254,0 4096 2 /
postmaste 14622 postgres txt REG 254,0 9959488 5636120
/usr/local/sisis-pap/pgsql/bin/postgres
postmaste 14622 postgres DEL REG 0,5 131559958 /dev/zero
...
postmaste 14622 postgres9u IPv4 131903453 0t0 TCP
srap32dxr1.dev.oclc.org:5432->srap32dxr1.dev.oclc.org:44836 (ESTABLISHED)
...
Interestingly, the other side of this connection of the port 5432 is not
visible, i.e. a 'lsof -P | grep 5432' does not show it in this moment.
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'
El día Montag, Mai 11, 2020 a las 09:50:12 -0400, Tom Lane escribió: > > > postmaste 14622 postgres9u IPv4 131903453 0t0 TCP > > srap32dxr1.dev.oclc.org:5432->srap32dxr1.dev.oclc.org:44836 (ESTABLISHED) > > OK, so it's gotten a TCP not Unix-socket connection; that's already > going to narrow things a little bit. > > > Interestingly, the other side of this connection of the port 5432 is not > > visible, i.e. a 'lsof -P | grep 5432' does not show it in this moment. > > Permissions problem maybe? I'm not sure that lsof will tell you much > about non-postgres-owned processes, unless you run it as root. Ah, yes. As this is just a dev host only, I set 'chmod 4755 /usr/bin/lsof' and now I see, the connecting client is one of our Java tools and I can look at the problem from this side. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'
El día Montag, Mai 11, 2020 a las 06:24:52 +0200, Matthias Apitz escribió: > Ah, yes. As this is just a dev host only, I set 'chmod 4755 /usr/bin/lsof' > and now I see, the connecting client is one of our Java tools and I can > look at the problem from this side. > With the knowledge which Java process it is causing, I'm able to simple reproduce the message and with knowing the 4-tuple of the TCP connection to identify the exchange in a capture with TCPDUMP sniffing on localhost port 5432. Below is the exchange. The visible strings are: 'sisis' (the PG user), 'sisis123' (its password in PG, a dummy password used for testing) and 'srap32dxr1' the hostname. The server is terminating the connection with a F-pkg: 19:54:02.940205 IP 10.23.33.19.48438 > 10.23.33.19.5432: Flags [S], seq 3950072774, win 43690, options [mss 65495,sackOK,TS val 3334863612 ecr 0,nop,wscale 7], length 0 0x: 4500 003c e98b 4000 4006 fadc 0a17 2113 E..<..@.@.!. 0x0010: 0a17 2113 bd36 1538 eb71 53c6 ..!..6.8.qS. 0x0020: a002 5682 0204 ffd7 0402 080a V... 0x0030: c6c5 fafc 0103 0307 19:54:02.940217 IP 10.23.33.19.5432 > 10.23.33.19.48438: Flags [S.], seq 749639996, ack 3950072775, win 43690, options [mss 65495,sackOK,TS val 3334863612 ecr 3334863612,nop,wscale 7], length 0 0x: 4500 003c 4000 4006 e468 0a17 2113 E..<..@.@..h..!. 0x0010: 0a17 2113 1538 bd36 2cae 993c eb71 53c7 ..!..8.6,..<.qS. 0x0020: a012 5682 0204 ffd7 0402 080a V... 0x0030: c6c5 fafc c6c5 fafc 0103 0307 19:54:02.940226 IP 10.23.33.19.48438 > 10.23.33.19.5432: Flags [.], ack 1, win 342, options [nop,nop,TS val 3334863612 ecr 3334863612], length 0 0x: 4500 0034 e98c 4000 4006 fae3 0a17 2113 E..4..@.@.!. 0x0010: 0a17 2113 bd36 1538 eb71 53c7 2cae 993d ..!..6.8.qS.,..= 0x0020: 8010 0156 567a 0101 080a c6c5 fafc ...VVz.. 0x0030: c6c5 fafc 19:54:02.948877 IP 10.23.33.19.48438 > 10.23.33.19.5432: Flags [P.], seq 1:513, ack 1, win 342, options [nop,nop,TS val 3334863621 ecr 3334863612], length 512 0x: 4500 0234 e98d 4000 4006 f8e2 0a17 2113 E..4..@.@.!. 0x0010: 0a17 2113 bd36 1538 eb71 53c7 2cae 993d ..!..6.8.qS.,..= 0x0020: 8018 0156 587a 0101 080a c6c5 fb05 ...VXz.. 0x0030: c6c5 fafc 0200 0200 7372 6170 srap 0x0040: 3332 6478 7231 32dxr1.. 0x0050: 0a73 6973 6973 ...sisis 0x0060: 0x0070: 0005 7369 7369 7331 ..sisis1 0x0080: 3233 23.. 0x0090: 0800 0x00a0: 0x00b0: 0001 0200 0604 0801 0x00c0: 0002 0x00d0: 0x00e0: 0x00f0: .. 19:54:02.948886 IP 10.23.33.19.5432 > 10.23.33.19.48438: Flags [.], ack 513, win 350, options [nop,nop,TS val 3334863621 ecr 3334863621], length 0 0x: 4500 0034 6004 4000 4006 846c 0a17 2113 E..4`.@.@..l..!. 0x0010: 0a17 2113 1538 bd36 2cae 993d eb71 55c7 ..!..8.6,..=.qU. 0x0020: 8010 015e 567a 0101 080a c6c5 fb05 ...^Vz.. 0x0030: c6c5 fb05 19:54:02.948970 IP 10.23.33.19.48438 > 10.23.33.19.5432: Flags [P.], seq 513:612, ack 1, win 342, options [nop,nop,TS val 3334863621 ecr 3334863621], length 99 0x: 4500 0097 e98e 4000 4006 fa7e 0a17 2113 E.@.@..~..!. 0x0010: 0a17 2113 bd36 1538 eb71 55c7 2cae 993d ..!..6.8.qU.,..= 0x0020: 8018 0156 56dd 0101 080a c6c5 fb05 ...VV... 0x0030: c6c5 fb05 0201 0063 ...c 0x0040: 0600 0800 0x0050: 0x0060: 0x0070: 0135 3132 0003 e200 1801 .512 0x0080: 0c07 cdff 85ee ef65 7fff d602 0800 ...e 0x0090: 0680 0648 00...H... 19:54:02.948974 IP 10.23.33.19.5432 > 10.23.33.19.48438: Flags [.], ack 612, win 350, options [nop,nop,TS
Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'
El día Montag, Mai 11, 2020 a las 02:41:29 -0400, Tom Lane escribió: > Matthias Apitz writes: > > Below is the exchange. The visible strings are: 'sisis' (the PG user), > > 'sisis123' (its password in PG, a dummy password used for testing) and > > 'srap32dxr1' the hostname. The server is terminating the connection with > > a F-pkg: > > [ squint... ] That looks nothing like a Postgres-protocol exchange > to me. If it weren't for the runs of zeroes, I'd wonder if the > connection had SSL encryption turned on. Perhaps you captured the > wrong session? I don't think so. It is exactly the problematic connection started by the Java process against the port 5432; look the SYN pkg: 19:54:02.940205 IP 10.23.33.19.48438 > 10.23.33.19.5432: Flags [S], seq 3950072774, win 43690, options [mss 65495,sackOK,TS val 3334863612 ecr 0,nop,wscale 7], length 0 As you say, that this is not any Postgres-protocol exchange, I will check the configuration of this part of our software. It can be some kind of misconfiguration, some part is talking TCP/IP to the wrong server. Thanks for the hint. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'
El día Montag, Mai 11, 2020 a las 02:41:29 -0400, Tom Lane escribió: > Matthias Apitz writes: > > Below is the exchange. The visible strings are: 'sisis' (the PG user), > > 'sisis123' (its password in PG, a dummy password used for testing) and > > 'srap32dxr1' the hostname. The server is terminating the connection with > > a F-pkg: > > [ squint... ] That looks nothing like a Postgres-protocol exchange > to me. If it weren't for the runs of zeroes, I'd wonder if the > connection had SSL encryption turned on. Perhaps you captured the > wrong session? It turned out, that the messages: 2020-05-12 05:33:06.828 CEST [6271] LOG: invalid length 33554940 of startup packet 2020-05-12 05:33:08.305 CEST [6291] LOG: invalid length 33554940 of startup packet 2020-05-12 05:34:07.495 CEST [6596] LOG: invalid length 33554940 of startup packet 2020-05-12 05:34:09.710 CEST [6927] LOG: invalid length 33554940 of startup packet have been caused by a misconfiguration of the component in question: the application used the Sybase JDBC driver to talk to the PostgreSQL server :-( This part of the thread is hereby solved. Remaining is still: 2020-04-30 15:14:03.367 CEST [3843] LOG: bogus received message length: 1650422894 2020-04-30 15:57:48.926 CEST [10380] LOG: bogus received message length: 1650422894 2020-04-30 15:58:31.851 CEST [10380] LOG: incomplete message from client 2020-04-30 15:58:53.578 CEST [10439] LOG: bogus received message length: 1650422894 2020-04-30 16:00:13.500 CEST [10439] LOG: incomplete message from client matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
ESQL/C: a ROLLBACK rolls back a COMMITED transaction
Hello, We're facing in our ESQL/C written application a situation where a commit'ed INSERT into a table is rolled back. I have here the ESQL/C logging of the problem: ... [1471] [12.05.2020 15:48:50:476]: ecpg_execute on line 1744: query: insert into swd_daten ( katkey , aktion , reserv , id , ansetzung , nettodaten ) values ( $1 , $2 , $3 , $4 , $5 , $6 ); with 6 parameter(s) on connection sisis [1471] [12.05.2020 15:48:50:476]: ecpg_process_output on line 1744: OK: INSERT 0 1 [1471] [12.05.2020 15:48:50:477]: ECPGtrans on line 6716: action "commit"; connection "sisis" ^^^ [1471] [12.05.2020 15:48:50:478]: ecpg_execute on line 1637: query: insert into swd_auftrag ( setnr , aufnum , katkey , userid , seqcount ) values ( $1 , $2 , $3 , $4 , $5 ); with 5 parameter(s) on connection sisis [1471] [12.05.2020 15:48:50:478]: ecpg_process_output on line 1637: OK: INSERT 0 1 [1471] [12.05.2020 15:48:50:478]: ECPGtrans on line 1124: action "commit"; connection "sisis" [1471] [12.05.2020 15:48:51:500]: ECPGtrans on line 6716: action "commit"; connection "sisis" [1471] [12.05.2020 15:48:51:501]: ECPGtrans on line 1222: action "rollback"; connection "sisis" ^^^ ... The INSERT of 1 row into table swd_daten was OK and commit'ed (marked line) and a later rollback (last line) seems to roll it back, at least the row isn't in the table. Any ideas? The connection is not set to AUTOCOMMIT. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: ESQL/C: a ROLLBACK rolls back a COMMITED transaction
To answer also the question of Adrian Klaver: The database in question has ~400 tables and the ESQL/C application has for each table its own ESQL/C source file. It would be possible but a nightmare to share the code and it's better to discuss the problem based on the ESQL/c log file or I have to write some small example code to reproduce the problem. The line numbers are relative to those source files (and it's a pitty that the name of the source file is not logged, onle the line numbers). El día Dienstag, Mai 12, 2020 a las 11:54:40 -0400, Tom Lane escribió: > Matthias Apitz writes: > > We're facing in our ESQL/C written application a situation where a > > commit'ed INSERT into a table is rolled back. > > Kind of hard to believe ... is there any sign of distress in the > postmaster log? No. Nothing which points to this. > > > I have here the ESQL/C > > logging of the problem: > > > ... > > [1471] [12.05.2020 15:48:50:476]: ecpg_execute on line 1744: query: insert > > into swd_daten ( katkey , aktion , reserv , id , ansetzung , nettodaten ) > > values ( $1 , $2 , $3 , $4 , $5 , $6 ); with 6 parameter(s) on > > connection sisis > > [1471] [12.05.2020 15:48:50:476]: ecpg_process_output on line 1744: OK: > > INSERT 0 1 > > [1471] [12.05.2020 15:48:50:477]: ECPGtrans on line 6716: action "commit"; > > connection "sisis" > > > > ^^^ > > It's striking that this log shows a server ack of the INSERT, but no server > ack of the COMMIT. Maybe that's just an oversight in the ESQL/C logging > logic, but I wonder what's actually getting to the server. You might try I looked into the source and the ECPGtrans ... is logged before its execution in the source interfaces/ecpg/ecpglib/misc.c and after the execution only an error condition would be logged. As there is only the line of the COMMIT w/o anything else, it seems to be executed fine. > enabling log_statement = all so you can get a trace of what the server > thinks is happening. I will do so, but would have to find a time window for this to not be swamped by the logs. Thanks for your reply to both, Adiran and Tom. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: ESQL/C: a ROLLBACK rolls back a COMMITED transaction
El día Dienstag, Mai 12, 2020 a las 12:30:17 -0700, Adrian Klaver escribió: > On 5/12/20 12:14 PM, Matthias Apitz wrote: > > > > To answer also the question of Adrian Klaver: > > > > The database in question has ~400 tables and the ESQL/C application has > > for each table its own ESQL/C source file. It would be possible but a > > nightmare to share the code and it's better to discuss the problem based > > on the ESQL/c log file or I have to write some small example code to > > reproduce the problem. The line numbers are relative to those source > > files (and it's a pitty that the name of the source file is not logged, > > onle the line numbers). > > Yeah, but there is a one:one mapping of table:source file and you know the > table, so you should be able to find the source at the line number. Or am I > missing something? Yes. The table here is swd_daten, the corresponding .pgc file is swd_daten.pgc. And in the log the line logged is: [1471] [12.05.2020 15:48:50:476]: ecpg_process_output on line 1744: OK: INSERT 0 1 What I wanted to have is: [1471] [12.05.2020 15:48:50:476]: ecpg_process_output on line 1744 of swd_daten.pgc: OK: INSERT 0 1 i.e. have added the file name to the line number as "on line 1744 of swd_daten.pgc" to not always have to think, hey in which table we're with this at the moment. > Also, from OP did: > > insert into swd_auftrag .. > > COMMIT? This question (if it was a question) I don't understand. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: ESQL/C: a ROLLBACK rolls back a COMMITED transaction
El día Dienstag, Mai 12, 2020 a las 08:01:15 -0400, Tom Lane escribió:
> Matthias Apitz writes:
> > And in the log the line logged is:
> > [1471] [12.05.2020 15:48:50:476]: ecpg_process_output on line 1744: OK:
> > INSERT 0 1
> > What I wanted to have is:
> > [1471] [12.05.2020 15:48:50:476]: ecpg_process_output on line 1744 of
> > swd_daten.pgc: OK: INSERT 0 1
> > i.e. have added the file name to the line number as "on line 1744 of
> > swd_daten.pgc" to not always have to think, hey in which table we're
> > with this at the moment.
>
> Not an unreasonable suggestion, but it'd be more likely to happen if
> you send in a patch ;-).
I was already thinking somemonths ago in a change (and patch proposal).
The problem is, that the generated C-code for an ESQL/C statement looks
today like this:
{ ECPGdo(__LINE__, 0, 0, NULL, 0, ECPGst_normal, "insert into swd_daten (
katkey , aktion , reserv , id , ansetzung , nettodaten ) values ( $1 , $2 ,
$3 , $4 , $5 , $6 )",
ECPGt_long,&(new_value),(long)1,(long)1,sizeof(long),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_short,&(hrec_swd_daten.aktion),(long)1,(long)1,sizeof(short),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_char,(hrec_swd_daten.reserv),(long)2,(long)1,(2)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
...
ECPGt_char,(hrec_swd_daten.nettodaten),(long)SWD_DATEN_BUF_MAX,(long)1,(SWD_DATEN_BUF_MAX)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
#line 1745 "swd_daten.pgc"
if (sqlca.sqlcode == ECPG_NOT_FOUND) posSqlNotFound ( );
#line 1745 "swd_daten.pgc"
if (sqlca.sqlwarn[0] == 'W') posSqlWarning ( );
#line 1745 "swd_daten.pgc"
if (sqlca.sqlcode < 0) posSqlError ( );}
and should be expanded to:
{ ECPGdo(__LINE__, __FILE__, 0, 0, NULL, 0, ECPGst_normal, "insert ...
as the first argument to ECPGdo() is of type int we can not do a hack
like "__LINE__:"__FILE__ (i.e. concatenating line number and filename
into one string. We have to change the call interface function ECPGdo()
and add a string argument. This would make fail all older compiled
applications when the new shared lib having this call is installed.
Here I'm stuck with the idea.
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Re: ESQL/C: a ROLLBACK rolls back a COMMITED transaction
El día Dienstag, Mai 12, 2020 a las 05:17:33 -0700, Adrian Klaver escribió: > > > insert into swd_auftrag .. > > > > > > COMMIT? > > > > This question (if it was a question) I don't understand. > > From your original message: > > "The INSERT of 1 row into table swd_daten was OK and commit'ed (marked line) > and a later rollback (last line) seems to roll it back, at least the row > isn't in the table." > > It was not clear to me whether: > > "[1471] [12.05.2020 15:48:50:478]: ecpg_execute on line 1637: query: insert > into swd_auftrag ( setnr , aufnum , katkey , userid , seqcount ) values ( $1 > , $2 , $3 , $4 , $5 ); with 5 parameter(s) on connection sisis > [1471] [12.05.2020 15:48:50:478]: ecpg_process_output on line 1637: OK: > INSERT 0 1" > > also COMMITT(ed) or not? As I said in the original post of this thread: [1471] [12.05.2020 15:48:50:476]: ecpg_execute on line 1744: query: insert into swd_daten ( katkey , aktion , reserv , id , ansetzung , nettodaten ) values ( $1 , $2 , $3 , $4 , $5 , $6 ); with 6 parameter(s) on connection sisis [1471] [12.05.2020 15:48:50:476]: ecpg_process_output on line 1744: OK: INSERT 0 1 [1471] [12.05.2020 15:48:50:477]: ECPGtrans on line 6716: action "commit"; connection "sisis" ... i.e. the COMMIT is done in the same connection(!) right after the INSERT. There is no other ESQL/C call logged (and done) between. There is only no ACK from the ESQL/C layer about the COMMIT of ECPGtrans, but this is a fault in the ESQL/C layer code, because the ECPGtrans is logged before executing it and afterward if it's done OK no message is generated. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: ESQL/C: a ROLLBACK rolls back a COMMITED transaction
I finally can reproduce the issue with a small ESQL/C written program for that purpose. I could attach here the source, but even seeing its printouts, all is perhaps clear: the pgm does an INSERT, after this the row is there and can be seen with SELECT; than I CLOSE a non existing cursor, which rolls back the INSERTed data: ./embedded tstint: 11073 INSERT done SELECT done SELECT: tstint: 11073 tstchar25: [hello] CLOSE "foo_bar" done SQL error: cursor "foo_bar" does not exist on line 57 SQL error: current transaction is aborted, commands ignored until end of transaction block on line 61 SELECT done SELECT: tstint: 0 tstchar25: [] COMMIT done SELECT done SELECT: tstint: 0 tstchar25: [] ROLLBACK done SELECT done SELECT: tstint: 0 tstchar25: [] i.e. not the ROLLBACK removes the data, but the CLOSE of non existing CURSOR. We have in our huge application server and its DB-layer places where we close in advance a CURSOR to be sure that its CREATE will not cause any problem because it is existing. Until yesterday we thought that the raised -400 error, like [1471] [12.05.2020 15:48:50:477]: ecpg_check_PQresult on line 939: bad response - ERROR: cursor "adm_partab_seq" does not exist [1471] [12.05.2020 15:48:50:477]: raising sqlstate 34000 (sqlcode -400): cursor "adm_partab_seq" does not exist on line 939 could be overcome with the COMMIT without loosing the inserted data. Main question: How can we ask the PostgreSQL server if a CURSOR 'foo_bar' (still) does exist or not? Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: ESQL/C: a ROLLBACK rolls back a COMMITED transaction
El día Mittwoch, Mai 13, 2020 a las 08:15:40 -0700, Adrian Klaver escribió: > In your original post you had: > > "We're facing in our ESQL/C written application a situation where a > commit'ed INSERT into a table is rolled back. I have here the ESQL/C > logging of the problem:" > ... > > "The INSERT of 1 row into table swd_daten was OK and commit'ed (marked line) > and a later rollback (last line) seems to roll it back, at least the row > isn't in the table. > > Any ideas? The connection is not set to AUTOCOMMIT." > > You then included a sequence of log messages that ended with a "rollback". > Within that sequence was the INSERT to swd_auftrag. It seemed reasonable to > ask whether that INSERT rolled back also. That is if the intent of this > thread is to figure out why the INSERT was rolled back. If the thread has > changed to fixing ESQL/C logging then ignore the above. The intention of my original post was to understand why the INSERT was rolled back. I do know this now: because I overlooked that the cancel of the transaction was done after the INSERT by CLOSE of a non open CURSOR. We're fixing this now already by checking in pg_cursors if the CURSOR is still open before issue the CLOSE. I don't know how expensive this is, but it seems that there is no other option to check this. The side step about fixing ESQL/C logging should be handled in another thread. Thanks all for your help matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
lib/libecpg.so.6.11 && valgrind
Hello, We're getting to the finish line in the port of our servers to PostgreSQL 11.4. and started valgrind'ing the code, as the PostgreSQL is new in the servers. Of course we have our own homework to do, but there are also complaints of valgrind pointing to the lib/libecpg.so.6.11, for example: ==9773== 9 bytes in 1 blocks are still reachable in loss record 3 of 52 ==9773==at 0x4C2E2E3: malloc (vg_replace_malloc.c:299) ==9773==by 0x1439F749: strdup (in /lib64/libc-2.26.so) ==9773==by 0x146DF448: ecpg_strdup (in /usr/local/sisis-pap/pgsql/lib/libecpg.so.6.11) ==9773==by 0x146E048E: ECPGconnect (in /usr/local/sisis-pap/pgsql/lib/libecpg.so.6.11) ==9773==by 0xB03E846: DB_opdbP (dbcall.pgc:794) ==9773==by 0xB03DFDB: DB_opdb (dbcall.pgc:556) ==9773==by 0x5CC22F2: EC_General::OpenDatabase(_SQL_EXCL) (eC_general.C:121) ==9773==by 0x40AEB8: SlnpInitDaemon (ACQDaemon.C:644) ==9773==by 0x40A878: main (ACQDaemon.C:213) How the PostgreSQL community likes us to deal with this? matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
checking existence of a table before updating its SERIAL
Hello, We're updating the SERIAL of a bunch of tables with a SQL script which does for any table: /* table: idm_tasktab */ DO $$ DECLARE max_id int; BEGIN SELECT INTO max_id GREATEST(COALESCE(max(taskid), 0),0) + 1 FROM idm_tasktab; RAISE NOTICE '% % %', 'idm_tasktab', 'taskid', max_id ; EXECUTE 'ALTER SEQUENCE idm_tasktab_taskid_seq RESTART ' || max_id::text; END $$ LANGUAGE plpgsql; Can some kind soul help me with doing a test for the existence of the table to avoid the error message about non existing relation? Thanks in advance. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
ESQL/C no indicator variables ./. error -213
Hello, We encountered that if our ESQL/C written servers see on SELECT or FETCH in a row a NULL value, it will raise correctly the error -213 as written and explained in https://www.postgresql.org/docs/11/ecpg-variables.html#ECPG-INDICATORS We catch this error -213 and deal with in. What we did not knew and discovered today is something very fatal: In such a situation on a FETCH of a row of some 55 columns, the transfer of the column elements into their hostvariables stops on first NULL value, as here to be seen in the log: [29217] [17.06.2020 15:49:16:499]: ecpg_execute on line 69: query: select * from acq_ffleit where bnr = $1 ; with 1 parameter(s) on connection sisis [29217] [17.06.2020 15:49:16:500]: ecpg_execute on line 69: using PQexecParams [29217] [17.06.2020 15:49:16:500]: ecpg_free_params on line 69: parameter 1 = 742 [29217] [17.06.2020 15:49:16:500]: ecpg_process_output on line 69: correctly got 1 tuples with 55 fields [29217] [17.06.2020 15:49:16:500]: ecpg_get_data on line 69: RESULT: 742 offset: 752; array: no ... [29217] [17.06.2020 15:49:16:500]: ecpg_get_data on line 69: RESULT: 49 offset: 752; array: no [29217] [17.06.2020 15:49:16:500]: ecpg_get_data on line 69: RESULT: offset: 752; array: no [29217] [17.06.2020 15:49:16:500]: raising sqlcode -213 on line 69: null value without indicator on line 69 In the above examples the transfer stopped after 47 RESULTs; the hostvariables after this have been untouched in our program, i.e. only part of the row has been read. One could say, "so what, you have error -213 and you deserve it". But at least this behaviour should be documented clearly in the above mentioned page. I would expect, that NULL values would not be transfered in such case but all other hostvariables yes, i.e. for me this is perhaps a bug in the ESQL/C implementation. Comments? Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
Re: ESQL/C no indicator variables ./. error -213
El día Mittwoch, Juni 17, 2020 a las 01:39:53 -0400, Tom Lane escribió:
> Matthias Apitz writes:
> > We encountered that if our ESQL/C written servers see on SELECT or FETCH
> > in a row a NULL value, it will raise correctly the error -213 as written
> > and explained in
> > https://www.postgresql.org/docs/11/ecpg-variables.html#ECPG-INDICATORS
> > We catch this error -213 and deal with in.
>
> > What we did not knew and discovered today is something very fatal: In
> > such a situation on a FETCH of a row of some 55 columns, the transfer of
> > the column elements into their hostvariables stops on first NULL value,
> > as here to be seen in the log:
>
> Could you provide a self-contained test case for this? It's hard to
> guess at what the problem might be.
Hello,
attached is a simple ESQL/C code; if it is not passing the mailing-list,
the code is also here: http://www.unixarea.de/embedded.pgc and this is
its output together with the ESQL/C error log file and SQL examples:
psql -Usisis -dsisis
psql (11.4)
Geben Sie »help« für Hilfe ein.
sisis=# \d dbctest
Tabelle »public.dbctest«
Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert
---+---+--+---+-
tstchar25 | character(25) | | |
tstint| integer | | |
Indexe:
"i_tstint" UNIQUE, btree (tstint)
sisis=# select * from dbctest where tstint = 1;
tstchar25 | tstint
---+
| 1
(1 Zeile)
./embedded
hostvariable 'tstint' before SELECT tstchar25, tstint INTO :tstchar25, :tstint
FROM dbctest: 99
hostvariable 'tstint' after SELECT tstchar25, tstint INTO :tstchar25, :tstint
FROM dbctest with -213: 99
cat esqlc.6485
[6485] [18.06.2020 08:26:38:433]: ECPGdebug: set to 1
[6485] [18.06.2020 08:26:38:433]: ECPGdebug: proc argv0 is embedded
[6485] [18.06.2020 08:26:38:433]: ECPGconnect: opening database sisis on
localhost port 5432 with options application_name=SunRise DBCALL V7.1
(pid=6485) for user sisis
[6485] [18.06.2020 08:26:38:436]: ecpg_execute on line 36: query: select
tstchar25 , tstint from dbctest where tstint = 1; with 0 parameter(s) on
connection sisis
[6485] [18.06.2020 08:26:38:437]: ecpg_execute on line 36: using PQexec
[6485] [18.06.2020 08:26:38:437]: ecpg_process_output on line 36: correctly got
1 tuples with 2 fields
[6485] [18.06.2020 08:26:38:437]: ecpg_get_data on line 36: RESULT: offset:
80; array: no
[6485] [18.06.2020 08:26:38:437]: raising sqlcode -213 on line 36: null value
without indicator on line 36
[6485] [18.06.2020 08:26:38:438]: ecpg_finish: connection sisis closed
Thanks
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
#include
#include
EXEC SQL INCLUDE sqlca;
EXEC SQL WHENEVER SQLERROR sqlprint;
void posSqlError() { return; }
main()
{
FILE *fp;
char file[80];
sprintf(file, "esqlc.%d", getpid());
fp = fopen(file, "w");
ECPGdebug(1, fp);
EXEC SQL BEGIN DECLARE SECTION;
char connection[1024];
char user[80];
char pass[80];
char tstchar25[80];
int tstint = 99;
EXEC SQL END DECLARE SECTION;
sprintf(connection,
"tcp:postgresql://localhost:5432/sisis?application_name=SunRise DBCALL V7.1
(pid=%d)", getpid());
strcpy(user, "sisis");
strcpy(pass, "sisis123");
EXEC SQL WHENEVER SQLERROR call posSqlError();
EXEC SQL CONNECT TO :connection USER :user USING :pass ;
printf("hostvariable 'tstint' before SELECT tstchar25, tstint INTO
:tstchar25, :tstint FROM dbctest: %d\n", tstint);
EXEC SQL SELECT tstchar25, tstint INTO :tstchar25, :tstint FROM dbctest
WHERE tstint = 1;
printf("hostvariable 'tstint' after SELECT tstchar25, tstint INTO
:tstchar25, :tstint FROM dbctest with -213: %d\n", tstint);
EXEC SQL DISCONNECT;
return 0;
}
error messages (autovaccum canceled and syntax errors) while loading a DUMP
Hello A PG dump file was produced on a 11.4. server the usual ways. When it is loaded with $ gzip -dc newanna_export.dmp | /usr/local/sisis-pap/pgsql/bin/psql -U sisis sisis it gives lots of error messages in the server log as: 2020-06-25 12:00:50.333 CEST [31295] ERROR: canceling autovacuum task 2020-06-25 12:00:50.333 CEST [31295] CONTEXT: automatic analyze of table "sisis.public.idm_targetlog" 2020-06-25 12:00:53.658 CEST [31295] ERROR: canceling autovacuum task 2020-06-25 12:00:53.658 CEST [31295] CONTEXT: automatic analyze of table "sisis.public.idm_targettab" 2020-06-25 12:00:55.375 CEST [31295] ERROR: canceling autovacuum task 2020-06-25 12:00:55.375 CEST [31295] CONTEXT: automatic analyze of table "sisis.public.idm_tasktab" and also syntax errors like: 2020-06-25 12:02:15.509 CEST [32719] ERROR: syntax error at or near "order" at character 652 2020-06-25 12:02:15.509 CEST [32719] STATEMENT: select id, brgroup, ins_time, upd_time, task_1, task_2, task_3, int_sel_1, int_sel_2, int_sel_3, int_sel_4, int_sel_5, str_sel_1, str_sel_2, str_sel_3, dat_sel_1, dat_sel_2, sel_seq_1, sel_seq_2, int_val_1, int_val_2, int_val_3, int_val_4, int_val_5, str_val_1, str_val_2, str_val_3, str_val_4, str_val_5, valtype_1, valtype_2, valtype_3, valtype_4, valtype_5, valtype_6, valtype_7, valmode_1, valmode_2, valmode_3, valmode_4, valmode_5, valmode_6, valmode_7, valname_1, valname_2, valname_3, valname_4, valname_5, valname_6, valname_7, valcont_1, valcont_2, valcont_3, valcont_4, valcont_5, valcont_6, valcont_7, sel_seq_3 from acq_vardata where id= order by sel_seq_3 2020-06-25 12:06:42.098 CEST [306] ERROR: syntax error at or near "q" at character 1 2020-06-25 12:06:42.098 CEST [306] STATEMENT: q select count(*) from ig_bigstrings ; What could be the reason for this? matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: error messages (autovaccum canceled and syntax errors) while loading a DUMP
El día jueves, junio 25, 2020 a las 01:11:37p. m. +0200, Matthias Apitz
escribió:
>
> Hello
>
> A PG dump file was produced on a 11.4. server the usual ways.
> When it is loaded with
>
> $ gzip -dc newanna_export.dmp | /usr/local/sisis-pap/pgsql/bin/psql -U sisis
> sisis
>
> it gives lots of error messages in the server log as:
>
> ...
>
> and also syntax errors like:
>
> 2020-06-25 12:02:15.509 CEST [32719] ERROR: syntax error at or near "order"
> at character 652
> 2020-06-25 12:02:15.509 CEST [32719] STATEMENT: select id, brgroup,
> ins_time, upd_time, task_1, task_2, task_3, int_sel_1, int_sel_2, int_sel_3,
> int_sel_4, int_sel_5, str_sel_1, str_sel_2, str_sel_3, dat_sel_1, dat_sel_2,
> sel_seq_1, sel_seq_2, int_val_1, int_val_2, int_val_3, int_val_4, int_val_5,
> str_val_1, str_val_2, str_val_3, str_val_4, str_val_5, valtype_1, valtype_2,
> valtype_3, valtype_4, valtype_5, valtype_6, valtype_7, valmode_1, valmode_2,
> valmode_3, valmode_4, valmode_5, valmode_6, valmode_7, valname_1, valname_2,
> valname_3, valname_4, valname_5, valname_6, valname_7, valcont_1, valcont_2,
> valcont_3, valcont_4, valcont_5, valcont_6, valcont_7, sel_seq_3 from
> acq_vardata where id= order by sel_seq_3
> 2020-06-25 12:06:42.098 CEST [306] ERROR: syntax error at or near "q" at
> character 1
> 2020-06-25 12:06:42.098 CEST [306] STATEMENT: q
> select count(*) from ig_bigstrings ;
>
> What could be the reason for this?
I looked in the dump file after uncompressing it. The 'syntax error'
comes from: one large table contains in a bytea column Perl code wich
our software reads from the table and executes it with Perl. But, why
the psql tryes to understand this code when it should INSERT it into the
table? This is a bit part of the content of the table:
# function: readAcqVarData\n#\n# parameter: where condition (string)\n#
returns: array of records\n# $recordList->[0]->{'int_val_1'} =
"20";\n# $recordList->[0]->{'str_val_1'} = "string";\n#
$recordList->[1]->{'int_val_1'} = "99";\n#
$recordList->[1]->{'str_val_1'} = "Dummy";\n#\nsub readAcqVarData {\n\tmy
($where) = @_;\n\tmy $recordList = undef;\n\tmy $ipos_valcont_7 = 56; #
position valcont_7-Spalte in select-statement - 1\n\n\t$debug .=
"readAcqVarData() \\n" if $debugOn;\n\n\t$erg = selectData(\n\t\t
"select id, brgroup, ins_time, upd_time, task_1, task_2, task_3, "\n\t\t .
"int_sel_1, int_sel_2, int_sel_3, int_sel_4, int_sel_5, "\n\t\t . "str_sel_1,
str_sel_2, str_sel_3, dat_sel_1, dat_sel_2, "\n\t\t . "sel_seq_1, sel_seq_2,
"\n\t\t . "int_val_1, int_val_2, int_val_3, int_val_4, int_val_5, "\n\t\t .
"str_val_1, str_val_2, str_val_3, str_val_4, str_val_5, "\n\t\t . "valtype_1,
valtype_2, valtype_3, valtype_4, valtype_5, valtype_6, valtype_7, "\n\t\t .
"valmode_1, valmode_2, valmode_3, valmode_4, valmode_5, valmode_6, valmode_7,
"\n\t\t .
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
Re: error messages (autovaccum canceled and syntax errors) while loading a DUMP
El día Donnerstag, Juni 25, 2020 a las 08:59:49 -0700, Adrian Klaver escribió: > On 6/25/20 5:54 AM, Matthias Apitz wrote: > > > > > > El día jueves, junio 25, 2020 a las 01:11:37p. m. +0200, Matthias Apitz > > escribió: > > > > > > > > Hello > > > > > > A PG dump file was produced on a 11.4. server the usual ways. > > > When it is loaded with > > > > > > $ gzip -dc newanna_export.dmp | /usr/local/sisis-pap/pgsql/bin/psql -U > > > sisis sisis > > What was the command to produce the dump? $ /usr/local/sisis-pap/pgsql/bin/pg_dump --file=newanna_export.dmp --compress=9 --user=sisis newanna > > > > > > > it gives lots of error messages in the server log as: > > > > > > ... > > > > > > and also syntax errors like: > > > > > > > > 2020-06-25 12:02:15.509 CEST [32719] ERROR: syntax error at or near > > > "order" at character 652 > > > 2020-06-25 12:02:15.509 CEST [32719] STATEMENT: select id, brgroup, > > > ins_time, upd_time, task_1, task_2, task_3, int_sel_1, int_sel_2, > > > int_sel_3, int_sel_4, int_sel_5, str_sel_1, str_sel_2, str_sel_3, > > > dat_sel_1, dat_sel_2, sel_seq_1, sel_seq_2, int_val_1, int_val_2, > > > int_val_3, int_val_4, int_val_5, str_val_1, str_val_2, str_val_3, > > > str_val_4, str_val_5, valtype_1, valtype_2, valtype_3, valtype_4, > > > valtype_5, valtype_6, valtype_7, valmode_1, valmode_2, valmode_3, > > > valmode_4, valmode_5, valmode_6, valmode_7, valname_1, valname_2, > > > valname_3, valname_4, valname_5, valname_6, valname_7, valcont_1, > > > valcont_2, valcont_3, valcont_4, valcont_5, valcont_6, valcont_7, > > > sel_seq_3 from acq_vardata where id= order by sel_seq_3 > > > 2020-06-25 12:06:42.098 CEST [306] ERROR: syntax error at or near "q" at > > > character 1 > > > 2020-06-25 12:06:42.098 CEST [306] STATEMENT: q > > > select count(*) from ig_bigstrings ; > > > > > > What could be the reason for this? > > > What was logged before?: > > 2020-06-25 12:02:15.509 CEST [32719] ERROR: syntax error at or near "order" > at character 652 2020-06-25 11:58:42.909 CEST [11004] LOG: checkpoints are occurring too frequently (24 seconds apart) 2020-06-25 11:58:42.909 CEST [11004] HINT: Consider increasing the configuration parameter "max_wal_size". 2020-06-25 11:59:30.776 CEST [31295] ERROR: canceling autovacuum task 2020-06-25 11:59:30.776 CEST [31295] CONTEXT: automatic analyze of table "sisis.public.d01buch" 2020-06-25 11:59:41.289 CEST [31282] ERROR: canceling autovacuum task 2020-06-25 11:59:41.289 CEST [31282] CONTEXT: automatic analyze of table "sisis.public.d03geb" 2020-06-25 11:59:43.515 CEST [31282] ERROR: canceling autovacuum task 2020-06-25 11:59:43.515 CEST [31282] CONTEXT: automatic analyze of table "sisis.public.d11loesch" 2020-06-25 11:59:45.838 CEST [31295] ERROR: canceling autovacuum task 2020-06-25 11:59:45.838 CEST [31295] CONTEXT: automatic analyze of table "sisis.public.d11rueck" 2020-06-25 12:00:17.007 CEST [31295] ERROR: canceling autovacuum task 2020-06-25 12:00:17.007 CEST [31295] CONTEXT: automatic analyze of table "sisis.public.idm_statistic" 2020-06-25 12:00:50.333 CEST [31295] ERROR: canceling autovacuum task 2020-06-25 12:00:50.333 CEST [31295] CONTEXT: automatic analyze of table "sisis.public.idm_targetlog" 2020-06-25 12:00:53.658 CEST [31295] ERROR: canceling autovacuum task 2020-06-25 12:00:53.658 CEST [31295] CONTEXT: automatic analyze of table "sisis.public.idm_targettab" 2020-06-25 12:00:55.375 CEST [31295] ERROR: canceling autovacuum task 2020-06-25 12:00:55.375 CEST [31295] CONTEXT: automatic analyze of table "sisis.public.idm_tasktab" 2020-06-25 12:02:15.509 CEST [32719] ERROR: syntax error at or near "order" at character 652 2020-06-25 12:02:15.509 CEST [32719] STATEMENT: select id, brgroup, ins_time, upd_time, task_1, task_2, task_3, int_sel_1, int_sel_2, int_sel_3, int_sel_4, int_sel_5, str_sel_1, str_sel_2, str_sel_3, dat_sel_1, dat_sel_2, sel_seq_1, sel_seq_2, int_val_1, int_val_2, int_val_3, int_val_4, int_val_5, str_val_1, str_val_2, str_val_3, str_val_4, str_val_5, valtype_1, valtype_2, valtype_3, valtype_4, valtype_5, valtype_6, valtype_7, valmode_1, valmode_2, valmode_3, valmode_4, valmode_5, valmode_6, valmode_7, valname_1, valname_2, valname_3, valname_4, valname_5, valname_6, valname_7, valcont_1, valcont_2, valcont_3, valcont_4, valcont_5, valcont_6, valcont_7, sel_seq_3 from acq_vardata where id= order by sel_seq_3 2020-06-25 12:06:42.098 CEST [306] ER
Re: error messages (autovaccum canceled and syntax errors) while loading a DUMP
On Thursday, 25 June 2020 21:12:50 CEST, Adrian Klaver wrote: acq_vardata where id= order by sel_seq_3 2020-06-25 12:06:42.098 CEST [306] ERROR: syntax error at or near "q" at character 1 ... If this was coming from the restore of the dump file I would expect to see a COPY line just prior to the error. To me it looks like something other then the restore is running that query and that the query itself is flawed. I will rerun it on a clean empty database and with no other proc on the server accessing the PG server. What happens if you do as a test something like?: CREATE TABLE test_table AS SELECT * FROM large_table WHERE row_id = row_below; I do not understand this request, what is 'large_table' and what is 'row_below'? The "large_table" is whatever table the Perl scripts are stored in. 'row_below' is whatever row identifier there is for the row that stores this particular script. Just trying to confirm that the script will run when the row it is part of is INSERTed into a table. I'm guessing not. You could also just manually run the script to see if the query is valid. I'm guessing it is not. Ok, I will figure it out. Thanks for the clarification. matthias -- Sent from my Ubuntu phone http://www.unixarea.de/ NO to the EU! NEIN zur EU!
Re: error messages (autovaccum canceled and syntax errors) while loading a DUMP
On Thursday, 25 June 2020 21:41:54 CEST, Tom Lane wrote: Adrian Klaver writes: On 6/25/20 11:03 AM, Matthias Apitz wrote: I looked in the dump file after uncompressing it. The 'syntax error' comes from: one large table contains in a bytea column Perl code wich our software reads from the table and executes it with Perl. But, why the psql tryes to understand this code when it should INSERT it into the table? The perl fragment you show is similar to what's reported in the error message, but it doesn't quite match, so I'm not 100% sold on the theory that that's somehow not been quoted correctly. However, regardless of the exact details, it seems like the most likely theory about what is happening is that the dump file is corrupt and the corruption is causing the de-gzipped output to be missing or duplicating chunks of text. (Given the way gzip compression works, that wouldn't be a very surprising symptom.) So that leads me to wonder what was done to the dump file in between creation and use. One idea, seeing that the dump file was not given a ".gz" extension that would reflect its compressed state, is that something thought it was plain text and tried to do a newline conversion on it. If it was passed through email that would be a really plausible mechanism... I have to check if the dump file was moved from one host to another, but even if so, it was moved by scp. I think, that a gzip -dc foo will work, regardless of the file names extensiom not beeing .gz I think also, if the file have been corrupted on transport, gzip -dc ... would complain about. matthias -- Sent from my Ubuntu phone http://www.unixarea.de/ NO to the EU! NEIN zur EU!
Connecting Powerbuilder / EAserver to PostgreSQL server 11 via ODBC or JDBC
Hello, After the very successful migration of our Library Management System (which uses ESQL/C, DBI, JDBC) together with PostgreSQL 11.4, we want to do the same with another LMS written in Powerbuild, running in an EAServer and currently using Sybase ASE as DBS. There is an error situation already on the connection phase, the ODBC debug logs show that the connection establishment and authentication to the PostgreSQL server is fine (also the disconnect), but the EAServer makes an error out of this and returns to the Powerbuilder software that the connection is invalid, raising some error 999 without saying much in the log files what this could mean or is caused from. I know this is difficult to analyze with all this proprietary software stack, but my first question here is: anybody out here who could manage such an architecture successful working? And please do not send hints of the type, rewrite everything in Java or Visual Basic, as this is not an option :-) Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: error messages (autovaccum canceled and syntax errors) while loading a DUMP
El día Donnerstag, Juni 25, 2020 a las 04:25:00 -0400, Tom Lane escribió: > Matthias Apitz writes: > > On Thursday, 25 June 2020 21:41:54 CEST, Tom Lane > > wrote: > >> regardless of the exact details, it seems like the most likely theory > >> about what is happening is that the dump file is corrupt and the > >> corruption is causing the de-gzipped output to be missing or > >> duplicating chunks of text. > > > I think, that a > > gzip -dc foo > > will work, regardless of the file names extensiom not beeing .gz > > Agreed, that gzip command won't care. I was concerned about what > might've happened in between. > > ... I restarted the server, created a new database with: $ /usr/local/sisis-pap/pgsql/bin/createdb -U sisis -T template0 testdump and loaded the DUMP while no other processes were connected to the server with: $ gzip -dc newanna_export.dmp | /usr/local/sisis-pap/pgsql/bin/psql -U sisis -dtestdump all went fine (table creations, COPYs into them, creating indexs) without any error message in the terminal. In the serverlog only these messages came out: 2020-06-27 10:16:38.778 CEST [18094] LOG: database system is ready to accept connections 2020-06-27 10:21:26.062 CEST [18096] LOG: checkpoints are occurring too frequently (25 seconds apart) 2020-06-27 10:21:26.062 CEST [18096] HINT: Consider increasing the configuration parameter "max_wal_size". 2020-06-27 10:21:52.436 CEST [18096] LOG: checkpoints are occurring too frequently (26 seconds apart) 2020-06-27 10:21:52.436 CEST [18096] HINT: Consider increasing the configuration parameter "max_wal_size". 2020-06-27 10:22:07.424 CEST [18302] ERROR: canceling autovacuum task 2020-06-27 10:22:07.424 CEST [18302] CONTEXT: automatic analyze of table "testdump.public.d01buch" 2020-06-27 10:22:14.545 CEST [18302] ERROR: canceling autovacuum task 2020-06-27 10:22:14.545 CEST [18302] CONTEXT: automatic analyze of table "testdump.public.d02zus" 2020-06-27 10:22:16.051 CEST [18249] ERROR: canceling autovacuum task 2020-06-27 10:22:16.051 CEST [18249] CONTEXT: automatic analyze of table "testdump.public.d03geb" 2020-06-27 10:22:18.331 CEST [18249] ERROR: canceling autovacuum task 2020-06-27 10:22:18.331 CEST [18249] CONTEXT: automatic analyze of table "testdump.public.d11loesch" 2020-06-27 10:22:19.718 CEST [18302] ERROR: canceling autovacuum task 2020-06-27 10:22:19.718 CEST [18302] CONTEXT: automatic analyze of table "testdump.public.d11rueck" 2020-06-27 10:22:23.034 CEST [18249] ERROR: canceling autovacuum task 2020-06-27 10:22:23.034 CEST [18249] CONTEXT: automatic analyze of table "testdump.public.d35buchnotiz" 2020-06-27 10:22:38.902 CEST [18302] ERROR: canceling autovacuum task 2020-06-27 10:22:38.902 CEST [18302] CONTEXT: automatic analyze of table "testdump.public.idm_statistic" 2020-06-27 10:22:59.332 CEST [18302] ERROR: canceling autovacuum task 2020-06-27 10:22:59.332 CEST [18302] CONTEXT: automatic analyze of table "testdump.public.idm_targetlog" 2020-06-27 10:23:01.493 CEST [18302] ERROR: canceling autovacuum task 2020-06-27 10:23:01.493 CEST [18302] CONTEXT: automatic analyze of table "testdump.public.idm_targettab" 2020-06-27 10:23:02.631 CEST [18302] ERROR: canceling autovacuum task 2020-06-27 10:23:02.631 CEST [18302] CONTEXT: automatic analyze of table "testdump.public.idm_tasktab" The messages about 'ERROR: canceling autovacuum task' appeared while the indexes were created. The database has around 400 tables and the uncompressed dump is around 3.6 GByte: $ gzip -dc newanna_export.dmp | wc -c 3643850597 So, the syntax error messages were caused by some other process (and we have already an idea which cron job it was). What must be done re/ the messages about 'ERROR: canceling autovacuum task', or can we just ignore them? Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
Re: error messages (autovaccum canceled and syntax errors) while loading a DUMP
El día Samstag, Juni 27, 2020 a las 07:21:21 -0700, Adrian Klaver escribió: > > 2020-06-27 10:23:02.631 CEST [18302] ERROR: canceling autovacuum task > > 2020-06-27 10:23:02.631 CEST [18302] CONTEXT: automatic analyze of table > > "testdump.public.idm_tasktab" > > > From what I understand they are occurring because the machine is to busy > doing the restore to get to the autovacuum task in a timely manner. So I > would say ignore and check back later to see that the autovacuum is working. > Given that it is ANALYZE that is being cancelled I would run a manual > ANALYZE after the restore is done to update the database statistics. The machine is a development server and no one was working on it (today is Saturday) apart of me. It has 4 modern and fast CPU, Running ANALYZE VERBOSE does not give any unusual output. Only for each table lines like: ... INFO: analyzing "public.z39t_term" INFO: "z39t_term": scanned 2 of 2 pages, containing 135 live rows and 0 dead rows; 135 rows in sample, 135 estimated total rows INFO: analyzing "public.z39t_trunc" INFO: "z39t_trunc": scanned 1 of 1 pages, containing 135 live rows and 0 dead rows; 135 rows in sample, 135 estimated total rows ... How could I check that the autovacuum is working? Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
PostgreSQL server does not increment a SERIAL internally
Hello,
Me and my team passed a full weekend hunting a bug in our Perl written
software were rows have been inserted with the same id 'acq_haushalt.hnr'
which should not have been the case because any budget year in that
table has a single internal number 'hnr'
The table in the 11.4 server is created as:
create table acq_haushalt (
hnr serialnot NULL , /* internal budget year number primary key */
hjahr smallint not NULL , /* budget year */
stufe smallint not NULL , /* level 0,1,2,3*/
kurzname char (16) , /* short name for ... */
...
);
We update the serial 'acq_haushalt_hnr_seq' with this statement after loading:
/* table: acq_haushalt */
DO $$
DECLARE
max_id int;
BEGIN
if to_regclass('acq_haushalt') is not null then
SELECT INTO max_id GREATEST(COALESCE(max(hnr), 0),0) + 1 FROM acq_haushalt;
RAISE NOTICE '% % %', 'acq_haushalt', 'hnr', max_id ;
EXECUTE 'ALTER SEQUENCE acq_haushalt_hnr_seq RESTART ' || max_id::text;
end if;
END $$ LANGUAGE plpgsql;
Usage in Perl DBI to get the next value for acq_haushalt.hnr:
if ( &getDBDriverName eq 'Pg') {
$erg = &selectData("SELECT NEXTVAL('acq_haushalt_hnr_seq')",[]);
if ($erg->{'CountData'} == 0) {
$newhnr=1;
}else{
$newhnr=$erg->{'Data'}->[0]->{$erg->{'Fields'}->[0]->{'Name'}};
}
} else { code block for Sybase ...
}
But the serial was not incremented internally as we could see with
'psql' and so more than one row was build and inserted with the same
number in $newhnr.
What helped was using:
$erg = &selectData("SELECT max(hnr) FROM acq_haushalt",[]);
if ($erg->{'CountData'} == 0) {
$newhnr=1;
}else{
$newhnr=$erg->{'Data'}->[0]->{$erg->{'Fields'}->[0]->{'Name'}}+1;
}
What we are doing wrong?
Thanks
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
Re: PostgreSQL server does not increment a SERIAL internally
El día Montag, Juli 06, 2020 a las 01:58:04 +0200, Sándor Daku escribió:
> > We update the serial 'acq_haushalt_hnr_seq' with this statement after
> > loading:
> >
>
> What does "loading" mean, and why do you reset the sequence after loading?
> (And as I can see you setting it to the value it most likely already has.)
> My guess is that your problem lurks somewhere here as in certain
> circumstances you reset it to an incorrect(previous) value.
Hello Sándor,
All the tables (~400) are loaded from an export in CSV like format done
from the same Sybase ASE database with this commands for any table:
08:08:00 TRUNCATE TABLE acq_haushalt ;
08:08:00 TRUNCATE TABLE
08:08:01 \COPY acq_haushalt FROM
'/home/sisis/guru/sisisDBsrap14/CC-acq_haushalt.load' WITH ( NULL '' ,
DELIMITER '|' )
08:08:01 COPY 2862
and afterwards we have to adjust the serials to the highest used value
with the shown command:
>
>
> > /* table: acq_haushalt */
> > DO $$
> > DECLARE
> > max_id int;
> > BEGIN
> > if to_regclass('acq_haushalt') is not null then
> > SELECT INTO max_id GREATEST(COALESCE(max(hnr), 0),0) + 1 FROM
> > acq_haushalt;
> > RAISE NOTICE '% % %', 'acq_haushalt', 'hnr', max_id ;
> > EXECUTE 'ALTER SEQUENCE acq_haushalt_hnr_seq RESTART ' || max_id::text;
> > end if;
> > END $$ LANGUAGE plpgsql;
It's output (for this table) was:
NOTICE: acq_haushalt hnr 3183
which is correct because it matches the highest value +1 of 'acq_haushalt.hnr'.
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
compatibility matrix between client and server
Hello, Is there some matrix about which PG client versions, esp. the shared libs of ESQL/C can work with which PG server versions. We have a case where a customer wants to use his own PG server, perhaps even on Windows, with our 11.4 client version (compiled by us) and used via ESQL/C. Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Why SELECT COUNT(*) takes so long?
Hello,
I've setup a relatively big database on RH 8.2 with PG 11.4. The table
in question has aroung 38 millions of rows. Why a SELECT COUNT(*) of the
full table takes around 1 minute:
# date ; printf "select count(*) from d01buch ;\n" |
/usr/local/sisis-pap/pgsql/bin/psql -Usisis -dsisis ; date
Mo 14. Sep 07:48:36 CEST 2020
count
--
37982555
(1 Zeile)
Mo 14. Sep 07:49:38 CEST 2020
while a SELECT using an indexed row does not take significant time:
# date ; printf "select count(*) from d01buch where d01gsi='F-2014-30663189X' ;
\n" | /usr/local/sisis-pap/pgsql/bin/psql -Usisis -dsisis ; date
Mo 14. Sep 07:50:23 CEST 2020
count
---
1
(1 Zeile)
I now such longish SELECT COUNT(*) from Sybase when UPDATE STATISTICS is
not done for the tables. Is there something similar for PostgreSQL?
Thanks
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
how to switch off: WARNING: psql major version 11, server major version 13
Hello, Maybe it's a FAQ, but I haven't seen the answer. I want to switch of the warning (because I know the fact of version not matching): $ psql -Usisis -dsisis SET psql (11.4, server 13.0) WARNING: psql major version 11, server major version 13. Some psql features might not work. Type "help" for help. sisis=# I tried to do it with the ~/.psqlrc file: $ cat ~/.psqlrc SET client_min_messages = 'error' The command gets executed (as the 'SET' shows), but does not silent the warning. Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub Без книги нет знания, без знания нет коммунизма (Влaдимир Ильич Ленин) Without books no knowledge - without knowledge no communism (Vladimir Ilyich Lenin) Sin libros no hay saber - sin saber no hay comunismo. (Vladimir Ilich Lenin)
Re: how to switch off: WARNING: psql major version 11, server major version 13
El día lunes, septiembre 28, 2020 a las 12:17:26p. m. +0200, Paul Förster escribió: > > $ psql -Usisis -dsisis > > SET > > psql (11.4, server 13.0) > > WARNING: psql major version 11, server major version 13. > > Some psql features might not work. > > Type "help" for help. > > > > sisis=# > > > > try the -q switch to psql: > > $ psql -q > > That should do. Yes, I know this flag. But this removes also the lines psql (11.4, server 13.0) Type "help" for help. I only want remove the WARNING lines. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub Без книги нет знания, без знания нет коммунизма (Влaдимир Ильич Ленин) Without books no knowledge - without knowledge no communism (Vladimir Ilyich Lenin) Sin libros no hay saber - sin saber no hay comunismo. (Vladimir Ilich Lenin)
Re: Problem close curser after rollback
El día miércoles, septiembre 30, 2020 a las 01:32:41p. m. +, Wiltsch,Sigrid escribió: > > Hi, > > we use the following statements in our applications, as described on the > site: > > https://www.postgresql.org/docs/11/ecpg-commands.html#ECPG-TRANSACTIONS > > EXEC SQL PREPARE stmt1 FROM "SELECT oid,datname FROM pg_database WHERE oid > > ?"; > EXEC SQL DECLARE foo_bar CURSOR FOR stmt1; > > ... I forgot to mention that the problem is with PostgreSQL 11.4 on SuSE Linux Enterprise. If the problem is not clear, we could rewrite this as a ten-liner in ESQL/C to reproduce it. The OP (Sigrid) and me we're working in the same team. Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub Без книги нет знания, без знания нет коммунизма (Влaдимир Ильич Ленин) Without books no knowledge - without knowledge no communism (Vladimir Ilyich Lenin) Sin libros no hay saber - sin saber no hay comunismo. (Vladimir Ilich Lenin)
Re: Problem close curser after rollback
El día miércoles, septiembre 30, 2020 a las 05:26:39p. m. +0200, Laurenz Albe
escribió:
> On Wed, 2020-09-30 at 13:32 +, Wiltsch,Sigrid wrote:
> > we use the following statements in our applications, as described on the
> > site:
> >
> > https://www.postgresql.org/docs/11/ecpg-commands.html#ECPG-TRANSACTIONS
> >
> > EXEC SQL PREPARE stmt1 FROM "SELECT oid,datname FROM pg_database WHERE oid
> > > ?";
> > EXEC SQL DECLARE foo_bar CURSOR FOR stmt1;
> >
> > /* when end of result set reached, break out of while loop */
> > EXEC SQL WHENEVER NOT FOUND DO BREAK;
> >
> > EXEC SQL OPEN foo_bar USING 100;
> > ...
> > while (1)
> > {
> > EXEC SQL FETCH NEXT FROM foo_bar INTO :dboid, :dbname;
> > ...
> > }
> >
> > EXEC SQL CLOSE foo_bar;
> >
> > After every fetch we open a transaction which is terminated with a rollback
> > in the event of an error.
> >
> > The problem we now have with this is, that the cursor is obviously closed
> > with the rollback,
> > so the next fetch ends with the error that the cursor no longer exists
> > (sqlcode -400
> >
> > What can I do so that the cursor is retained despite rollback?
> >
> > The procedure described is very often found in our applications, which have
> > been ported from sybase to postgreSQL.
>
> You cannot start a transaction while you are reading a cursor; you probably
> get a warning "there is already a transaction in progress".
Sigrid was not fully correct saying 'After every fetch we open a transaction...'
Our layer between application and ESQL/C has a function call DB_strT()
to start a transaction. But this does nothing for PostgreSQL because in
PostgreSQL' ESQL/C there is no method "START TRANSACTION", see
https://www.postgresql.org/docs/11/ecpg-commands.html#ECPG-TRANSACTIONS
There are only COMMIT and ROLLBACK.
I think we will prepare the ten-liner in ESQL/C for further discussion.
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Без книги нет знания, без знания нет коммунизма (Влaдимир Ильич Ленин)
Without books no knowledge - without knowledge no communism (Vladimir Ilyich
Lenin)
Sin libros no hay saber - sin saber no hay comunismo. (Vladimir Ilich Lenin)
Re: Problem close curser after rollback
On Wednesday, 30 September 2020 20:37:23 CEST, Tom Lane wrote: Matthias Apitz writes: El día miércoles, septiembre 30, 2020 a las 05:26:39p. m. +0200, Laurenz Albe escribió: On Wed, 2020-09-30 at 13:32 +, Wiltsch,Sigrid wrote: What can I do so that the cursor is retained despite rollback? You cannot start a transaction while you are reading a cursor; you probably get a warning "there is already a transaction in progress". I think we will prepare the ten-liner in ESQL/C for further discussion. I don't think you really need to: the point seems clear enough. I don't especially like the idea you are suggesting though. The general principle in SQL is that a rolled-back transaction should have no effect after it's been rolled back. Allowing a cursor it creates to survive the rollback would fly in the face of that principle. The general idea of transactions is that you START one at a moment, go ahead and a ROLLBACK rolls back everything what was done *after* the point of starting it. We have here the case of not beeing able to define the start of the transaction. The application wants to set it *after* the first (and again after each next) fetch. You say, the transaction starts already with that fetch. We have to think how to deal with that missing feature in PostgreSQL ESQL/C to define the point of where the transaction starts. Btw: In all of the other DBS (Informix, Sybase, Oracle) we could define that point with START TRANSACTION. Thanks matthias -- Sent from my Ubuntu phone http://www.unixarea.de/ NO to the EU! NEIN zur EU!
Re: Problem close curser after rollback
El día miércoles, septiembre 30, 2020 a las 02:37:23p. m. -0400, Tom Lane
escribió:
> Matthias Apitz writes:
> > El día miércoles, septiembre 30, 2020 a las 05:26:39p. m. +0200, Laurenz
> > Albe escribió:
> >> On Wed, 2020-09-30 at 13:32 +, Wiltsch,Sigrid wrote:
> >>> What can I do so that the cursor is retained despite rollback?
>
> >> You cannot start a transaction while you are reading a cursor; you probably
> >> get a warning "there is already a transaction in progress".
>
> > I think we will prepare the ten-liner in ESQL/C for further discussion.
>
> I don't think you really need to: the point seems clear enough.
I did wrote the ten-liner to play around with. Interestingly, there exists an
undocumented ESQL/C statement 'EXEC SQL START TRANSACTION' which gives
in the esqlc log:
ECPGtrans on line 48: action "start transaction"; connection "sisis"
What as well does work is the following sequence:
EXEC SQL PREPARE stmt1 FROM "SELECT tstchar25, tstint FROM dbctest
WHERE tstint > ?";
EXEC SQL DECLARE foo_bar CURSOR WITH HOLD FOR stmt1 ;
/* when end of result set reached, break out of while loop */
EXEC SQL WHENEVER NOT FOUND DO BREAK;
EXEC SQL OPEN foo_bar USING 1;
while (1)
{
EXEC SQL FETCH NEXT FROM foo_bar INTO :tstchar25, :tstint;
EXEC SQL COMMIT ;
EXEC SQL START TRANSACTION ;
printf("fetched: [%s] [%d] \n", tstchar25, tstint);
// ... do something with the fetched data and because
// it went wrong, we issue a ROLLBACK
EXEC SQL ROLLBACK ;
}
This fetches nicely through the table in the while-loop; without the
additional COMMIT, the START TRANSACTION gives
ECPGtrans on line 48: action "start transaction"; connection "sisis"
ECPGnoticeReceiver: there is already a transaction in progress
We will think now about what we have learned and how to repair our
application.
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Без книги нет знания, без знания нет коммунизма (Влaдимир Ильич Ленин)
Without books no knowledge - without knowledge no communism (Vladimir Ilyich
Lenin)
Sin libros no hay saber - sin saber no hay comunismo. (Vladimir Ilich Lenin)
Re: dup(0) fails on Ubuntu 20.04 and macOS 10.15 with 13.0
El día lunes, octubre 05, 2020 a las 04:49:27p. m. +0200, Mario Emmenlauer
escribió:
> On 05.10.20 13:22, Mario Emmenlauer wrote:
> > I've used PostgreSQL since version 9.x successfully on Linux, macOS
> > and Windows. Today I've upgraded from 12.3 to 13.0 and suddenly I can
> > not start the server any more on Ubuntu 20.04 (inside Docker on Ubuntu
> > 18.04) and on macOS 10.15.
> >
> > I get reproducibly the error:
> > 2020-10-05 11:48:19.720 CEST [84731] WARNING: dup(0) failed after 0
> > successes: Bad file descriptor
> > 2020-10-05 11:48:19.720 CEST [84731] FATAL: insufficient file descriptors
> > available to start server process
> > 2020-10-05 11:48:19.720 CEST [84731] DETAIL: System allows 0, we need at
> > least 58.
> > 2020-10-05 11:48:19.720 CEST [84731] LOG: database system is shut down
> >
> > ...
Can you try to catch the situation starting the server under strace,
perhaps with '-f' to follow childs. And look into the output ('-o outfile')
for the failing syscall.
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Без книги нет знания, без знания нет коммунизма (Влaдимир Ильич Ленин)
Without books no knowledge - without knowledge no communism (Vladimir Ilyich
Lenin)
Sin libros no hay saber - sin saber no hay comunismo. (Vladimir Ilich Lenin)
Re: print formated special characteres
El día sábado, octubre 17, 2020 a las 03:37:46p. m. -0300, Celso Lorenzetti escribió: > Somebody help me, please. > > How to make the texts are aligned with 10 characters? > > > > elog(INFO, "\n%-10s Fim\n%-10s Fim\n", "Variável", "Variavel"); Hola Celso, You can reproduce the same on the UNIX shell with: $ printf "\n%-10s Fim\n%-10s Fim\n" "Variável" "Variavel" Variável Fim Variavel Fim $ printf "\n%-10s Fim\n%-10s Fim\n" "VariXvel" "Variavel" VariXvel Fim Variavel Fim The second test (changing the accented char 'á' by 'X'), shows that the problem/bug is a) more generic, not only in PostgreSQL and b) has todo with being the UTF-8 char 'á' a two byte char, while 'X' is only one byte. I have no solution, though at the moment... Obrigado matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub Без книги нет знания, без знания нет коммунизма (Влaдимир Ильич Ленин) Without books no knowledge - without knowledge no communism (Vladimir Ilyich Lenin) Sin libros no hay saber - sin saber no hay comunismo. (Vladimir Ilich Lenin)
using psql 11.4 with a server 13.0 && meta commands
Hello, I've found the following problem using psql 11.4 against a server 13.0: $ export LANG=en_US.UTF-8 $ psql -Usisis -dsisis psql (11.4, server 13.0) WARNING: psql major version 11, server major version 13. Some psql features might not work. Type "help" for help. sisis=# \d dbctest FEHLER: Spalte c.relhasoids existiert nicht LINE 1: ...riggers, c.relrowsecurity, c.relforcerowsecurity, c.relhasoi... ^ sisis=# Is this expected? Are there other bigger issues with commands. I see the message "Some psql features might not work.", but that this affects also "normal" commands of daily use... Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub Без книги нет знания, без знания нет коммунизма (Влaдимир Ильич Ленин) Without books no knowledge - without knowledge no communism (Vladimir Ilyich Lenin) Sin libros no hay saber - sin saber no hay comunismo. (Vladimir Ilich Lenin)
Re: facing problem in outparameters in c
El día miércoles, octubre 28, 2020 a las 04:57:12a. m. +, Mahesh Bodepati escribió: > Actually i am calling a postgres function by using c language which > description is defined in database side .when iam calling a postgres function > by passing input and output parameters after excution of function in database > side i need output data in correcsponding output parameters but iam not > getting output like that. i am getting total output data in response > pointer.if iam getting total data in response pointer it is hard to figure > out which data is for which output parameter. can you please check once > attached two text files. > > I'm hoping that you're not writing C as you write emails, top posted and all in one single line. You should wrap mail around column 72. Punchcards are your friend. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub Без книги нет знания, без знания нет коммунизма (Влaдимир Ильич Ленин) Without books no knowledge - without knowledge no communism (Vladimir Ilyich Lenin) Sin libros no hay saber - sin saber no hay comunismo. (Vladimir Ilich Lenin)
ECPG sqlca error handling
Hello, The documentation explains well how to use the 'sqlca' for error handling: https://www.postgresql.org/docs/13/ecpg-errors.html#ECPG-SQLCA An error message is stored in sqlca.sqlerrm.char sqlerrmc[SQLERRMC_LEN]. My question is: Is there some way to get the exact failing ESQL/C statement, because only the line number does not help in our case. Thanks in advance matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub Без книги нет знания, без знания нет коммунизма (Влaдимир Ильич Ленин) Without books no knowledge - without knowledge no communism (Vladimir Ilyich Lenin) Sin libros no hay saber - sin saber no hay comunismo. (Vladimir Ilich Lenin)
\COPY command and indexes in tables
Hello,
We load large tables in some customer installation (some millions of rows)
from file with:
TRUNCATE TABLE tableName ;
\COPY tableName FROM 'fileName' WITH ( DELIMITER '|' )
and got to know that the loading nearly stops (without any real CPU
consumption) in the middle. The wild guess is that we forgot to DROP the
indexes on
the tables. The doc https://www.postgresql.org/docs/13/sql-copy.html
does not give any hints related indexes.
There seems to be even tools available which address this issue on the
flight, like https://www.californiacivicdata.org/2018/01/25/index-drop-and-copy/
Any comments on this?
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Без книги нет знания, без знания нет коммунизма (Влaдимир Ильич Ленин)
Without books no knowledge - without knowledge no communism (Vladimir Ilyich
Lenin)
Sin libros no hay saber - sin saber no hay comunismo. (Vladimir Ilich Lenin)
Re: Do we need a way to moderate mailing lists?
El día sábado, enero 16, 2021 a las 02:50:58p. m. -0300, Alvaro Herrera escribió: > One way you could help, is by learning what top-posting is, learning not > to do it, and teaching others the same. Same with not quoting entire > messages on reply. +1 matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub ¡Con Cuba no te metas! «» Don't mess with Cuba! «» Leg Dich nicht mit Kuba an! http://www.cubadebate.cu/noticias/2020/12/25/en-video-con-cuba-no-te-metas/
Re: Do we need a way to moderate mailing lists?
Hello, I disagree in some of the points: El día domingo, enero 17, 2021 a las 10:10:28a. m. -0700, David G. Johnston escribió: > Neither images nor non-plain-text means that the content is unreadable, not > useful, or problematic. Dealing with these on an email-by-email basis > through the community seems fine. Mails to a mailing list should be text (or even ASCII) because not all subscribers can read HTML or images and they're not needed to describe a problem. Images have the risk to have malicious content with the intention to use bugs in the image viewers. HTML can "phone home" with one pixel href's to check if you opened the HTML page. > As we seem to be compiling a list for people to review upfront (the major > points in this thread should make it to the website and be linked to by > community responders when encountering said problematic posts). > > Always reply-to-all. Why? Why I (and other subscribers) have to have the same mail twice in the mbox? > ... matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub ¡Con Cuba no te metas! «» Don't mess with Cuba! «» Leg Dich nicht mit Kuba an! http://www.cubadebate.cu/noticias/2020/12/25/en-video-con-cuba-no-te-metas/
