restore and recovery using WAL: unkown messages in serverlog

ng 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

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 Apit

Re: PG SQL and LIKE clause

ompiled 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 wi

Re: Installed PostgreSQL-11 in Ubuntu 18.04.02 Server Edition: No existing local cluster is suitable as a default target

e 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 (cha

Re: Installed PostgreSQL-11 in Ubuntu 18.04.02 Server Edition: No existing local cluster is suitable as a default target

d 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

s/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

b 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 G

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

updating sequence value for column 'serial'

$; 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

Re: updating sequence value for column 'serial'

LARE 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::t

Perl DBI converts UTF-8 again to UTF-8 before sending it to the server

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 -- Mat

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 n

SELECT d02name::bytea FROM ... && DBI::Pg

02name\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 -- Matthia

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 d02be

Re: Perl DBI converts UTF-8 again to UTF-8 before sending it to the server

20202020202020202020\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? > >

PostgreSQL && data types in ESQL/C

ariable 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: htt

type SERIAL in C host-struct

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. Thank

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). >

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"); >

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); > > Th

deep debug log for psql

-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,

status of CURSORs after DISCONNECT

s" 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 wen

SQL operator '*='

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 durchsch

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

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 ab

How to reset a server error '25P02 in_failed_sql_transaction'

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?

Re: How to reset a server error '25P02 in_failed_sql_transaction'

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.unixar

ESQL/C FETCH of CHAR data delivers to much data for UTF-8

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

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

f 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 Pu

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'; > >

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 M

Re: calculating the MD5 hash of role passwords in C

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

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 th

performance of loading CSV data with COPY is 50 times faster than Perl::DBI

nutes ./. 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 Post

DBI && INSERT

-> $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::errs

Perl::DBI and TYPE of column

et 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? > > regre

\COPY to accept non UTF-8 chars in CHAR columns

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

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

pg_basebackup && long time storage of wal_archive/ content

stgresql11 # 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...@unixare

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

PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

::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 TCP localhost:53742->localhost:5432 (ESTABLISHED) -- Matthias Apitz, ✉ g...@unixarea.de, http://www.un

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&#x

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

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.d

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

(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

How to move a 11.4 cluster to another Linux host, but empty?

t, 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?

ing. > 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 > > --

Re: How to move a 11.4 cluster to another Linux host, but empty?

abases 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?

gular 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

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

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

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 m

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 >

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

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 pr

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 th

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 > >

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 > >

ESQL/C: a ROLLBACK rolls back a COMMITED transaction

ble 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

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

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

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: > >

Re: ESQL/C: a ROLLBACK rolls back a COMMITED transaction

r 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

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

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 t

lib/libecpg.so.6.11 && valgrind

InitDaemon (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

checking existence of a table before updating its SERIAL

b', '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 advanc

ESQL/C no indicator variables ./. error -213

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 ex

error messages (autovaccum canceled and syntax errors) while loading a DUMP

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

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ó: > > > > > > > >

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 pr

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 read

Connecting Powerbuilder / EAserver to PostgreSQL server 11 via ODBC or JDBC

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 h

Re: error messages (autovaccum canceled and syntax errors) while loading a DUMP

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

$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

; 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; >

compatibility matrix between client and server

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?

/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 -- Matth

how to switch off: WARNING: psql major version 11, server major version 13

t 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...@

Re: how to switch off: WARNING: psql major version 11, server major version 13

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 WARN

Re: Problem close curser after rollback

ewrite 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 Без книги нет знания, без знан

Re: Problem close curser after rollback

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 thi

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

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: >

Re: dup(0) fails on Ubuntu 20.04 and macOS 10.15 with 13.0

tem 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, htt

Re: print formated special characteres

l 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, thoug

using psql 11.4 with a server 13.0 && meta commands

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 Без книги нет знания, без знания н

Re: facing problem in outparameters in c

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 --

ECPG sqlca error handling

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димир И

\COPY command and indexes in tables

018/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

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 A

Re: Do we need a way to moderate mailing lists?

matic 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 n

  1   2   3   >