varchar::bytea fails when varchar contains backslash

[Matthias Apitz]

Hello,

I wanted to look into a column of type varchar converting the content
with ::bytea to a hex string, but this fails when the column contains a
backslash:

psql -Usisis -dtestdb
psql (14.1)
Geben Sie »help« für Hilfe ein.

testdb=# select trenn from sik_fstab where name='EdvSelKenn';
 trenn
---
 ; :,.-!@%&/()=?'*+#<>[\]{|}&"
 ; :,.-!@%&/()=?'*+#<>[\]{|}&"
(2 Zeilen)

testdb=# select trenn::bytea from sik_fstab where name='EdvSelKenn';
ERROR:  invalid input syntax for type bytea

Why is this?

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Re: pg_upgrade from Postgresql-12 to Postgresql-13 fails with "Creating dump of database schemas postgres *failure*"

[liam saffioti]

Hi Chow,

Can you answer the below questions, please?

- What is the pgaudit extension version?
- What is the PostGIS and postgis_raster extensions version?
- In which databases are PostGIS or postgis_raster extensions installed?
- Can you add postgresql.auto.conf file?

I suppose that the problem is caused by the extensions bugs.

Best,
Liam

Yoong S. Chow , 19 Şub 2022 Cmt, 19:02 tarihinde şunu
yazdı:

> Hi Jeff,
>
> Could you please clarify what core files are? I am truly a newbie to
> postgresql.
>
> The pg_dump on both `postgres` and `postgis_raster` databases ran without
> error.  Attached is the posgtresql log.
>
> Thank you,
> Chow
>
>
> On Sat, Feb 19, 2022 at 12:07 AM Jeff Janes  wrote:
>
>>
>>
>> On Fri, Feb 18, 2022 at 8:35 AM Yoong S. Chow 
>> wrote:
>>
>>>
>>> Here are the logs: pg_upgrade_server.log 
>>> , pg_upgrade_dump_13427.log  and
>>> tail_postgresql-12-2022-02-16_190344.log 
>>> .
>>>
>>
>> From that last log file we have:
>>
>>
>>1. 2022-02-16 19:04:35 +08 [303982]: [4957-1] session=620cda42.4a36e,
>>user=postgres,db=postgis_raster,app=pg_dump,client=[local] | LOG:
>> disconnection: session time: 0:00:01.101 user=postgres 
>> database=postgis_raster
>>host=[local]
>>2. free(): invalid pointer
>>
>>
>> and then a little later:
>>
>>
>>1. 2022-02-16 19:04:35 +08 [303791]: [7-1] session=620cda0f.4a2af,user
>>=,db=,app=,client= | LOG:  server process (PID 303982) was terminated
>>by signal 6: Aborted
>>
>>
>> Do you get any core files for 303982?  Do you have core dumps enabled?
>> What do you get if you run a manual pg_dump -s on db postgis_raster?
>>
>> Cheers,
>>
>> Jeff
>>
>>>

Re: Detecting schema changes via WAL logs

[Tomas Pospisek]

On 19.02.22 17:42, Cal Mitchell wrote:

Hello everyone,

I’m building an open-source data integration tool (SQLpipe) and am now 
trying to incorporate CDC into the product.


Is it possible to detect when schema changes happen via the WAL? The WAL 
doesn’t have to tell me /what/ was changed, it just needs to tell me 
/when/ something changed.


For example, anytime I run create table or some other DDL command, there 
is a begin and commit message in the log, with nothing in between. If 
there aren’t any other operations that create such “empty” logs, it 
seems possible to just re-scan the tables that are being synced for 
schema changes whenever one of these logs is encountered.


I remember having seen a similar question recently here - that is 
detecting schema changes.


Also I think having seen WAL to SQL scripts somewhere.

I might be wrong on both accounts, but maybe you want to review the last 
few months of list archives if you haven't done so already and maybe 
check postgres' Debian package repository if there aren't WAL to SQL 
scripts somewhere.


As I said, this is what my fuzzy in-brain SQL returns so please don't 
SIGKILL me if my query was too fuzzy indeen.

*t

Re: varchar::bytea fails when varchar contains backslash

[Daniel Verite]

Matthias Apitz wrote:

> I wanted to look into a column of type varchar converting the content
> with ::bytea to a hex string, but this fails when the column contains a
> backslash:

Yes, casting from text to bytea cannot be used for that.
The convert_to() function must be used instead.

From the doc at
https://www.postgresql.org/docs/current/functions-binarystring.html

convert_to ( string text, dest_encoding name ) → bytea

Converts a text string (in the database encoding) to a binary string encoded
in encoding dest_encoding (see Section 24.3.4 for available conversions).

convert_to('some_text', 'UTF8') → \x736f6d655f74657874



Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: https://www.manitou-mail.org
Twitter: @DanielVerite