Re: Time zone offset in to_char()
> set_config ( setting_name text, new_value text, is_local boolean ) → text
>
> Sets the parameter setting_name to new_value, and returns that value. If
> is_local is true, the new value will only apply during the current
> transaction. If you want the new value to apply for the rest of the current
> session, use false instead. This function corresponds to the SQL command SET.
>
> set_config('log_statement_stats', 'off', false) → off"
> "
I tried this like so:
select ToDatetimeOffset(current_timestamp, 'Europe/Amsterdam'),
to_char(current_timestamp, ‘-MM-DD HH24:MI:SS.SU0 TZH:TZM’).
The result of the second call was based on time zone ‘Europe/Amsterdam’, where
it wasn’t when called outside the transaction (when it was based on UTC
corresponding to the server time zone).
So the time zone set with set_config(…, …, true) appeared to leak out of
function scope and applied to transaction scope (as described in the quoted
text).
For brevity I could run that query tomorrow when I’m back at work.
Following up on my own mail from yesterday evening, here's the output that
shows the function using set_config 'leaking' the timezone change to outside
the function (the first select vs. the 2nd select) into the (same) transaction,
whereas the function with the time zone bound to the header does not (the 3rd
select).
These are all from a single session, ran consecutively - a straight copy-paste
from psql.
ciscache=> select ToDatetimeOffset(current_timestamp, 'Europe/Amsterdam'),
to_char(current_timestamp, '-MM-DD HH24:MI:SS.US0 TZH:TZM');
todatetimeoffset | to_char
+
2024-01-12 09:41:44.7019350 +01:00 | 2024-01-12 09:41:44.7019350 +01:00
(1 row)
ciscache=> select to_char(current_timestamp, '-MM-DD HH24:MI:SS.US0
TZH:TZM');
to_char
2024-01-12 08:41:54.4739800 +00:00
(1 row)
ciscache=> select ToDatetimeOffsetNL(current_timestamp),
to_char(current_timestamp, '-MM-DD HH24:MI:SS.US0 TZH:TZM');
todatetimeoffsetnl | to_char
+
2024-01-12 09:42:44.1989210 +01:00 | 2024-01-12 08:42:44.1989210 +00:00
(1 row)
Regards,
Alban Hertroijs.
Re: Time zone offset in to_char()
From: Adrian Klaver
> I didn’t succeed in calling SET LOCAL TIMEZONE from within the function.
> Could be I missed something, then Google (stackoverflow) pointed me to
> set_config().
CREATE OR REPLACE FUNCTION public.tz_fnc()
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
SET LOCAL TIMEZONE = 'UTC';
RAISE NOTICE '%', to_char(now(), 'OF');
END;
$function$
Turns out that the reason I cannot get this to work is that in my case the time
zone value comes from a text parameter:
ciscache=> create or replace function ciscache.ToDatetimeOffset(ts_
timestamptz, tz_ text)
returns varchar(34)
language plpgsql
as $$
begin
--perform set_config('timezone', tz_, true /* local */);
set local timezone to tz_;
return to_char(ts_, '-MM-DD HH24:MI:SS.US0 TZH:TZM');
end;
$$;
CREATE FUNCTION
ciscache=> select ToDatetimeOffset(current_timestamp, 'Europe/Amsterdam'),
to_char(current_timestamp, '-MM-DD HH24:MI:SS.US0 TZH:TZM');
ERROR: invalid value for parameter "TimeZone": "tz_"
CONTEXT: SQL statement "set local timezone to tz_"
PL/pgSQL function todatetimeoffset(timestamp with time zone,text) line 4 at SQL
statement
But set_config() doesn't have that issue.
Re: [EXTERNAL]Re: Refresh Materialized View Issue
That'd be a band-aid at best, because we know that the query used to define the materialized view runs in a reasonable amount of time on it's own, as does a CTAS. So either the REFRESH is doing something odd when writing into the new relation (which looking at the code seems very unlikely), or REFRESH is getting a different query plan for some reason. Unfortunately, I don't know of any easy way to get the query plan for the REFRESH (it might be possible via gdb, but I'm not sure). We do at least know that the REFRESH is using parallel workers. Can you post the output of EXPLAIN ANALYZE for the SELECT? That might provide some clues. Sure, here is the explain analyze for the select: HashAggregate (cost=123986470.20..129486707.63 rows=164493082 width=24) (actual time=697250.385..741548.965 rows=59015171 loops=1) Group Key: id1, id2, id3 Planned Partitions: 64 Batches: 65 Memory Usage: 328209kB Disk Usage: 6750176kB -> Gather (cost=64653301.50..107228737.47 rows=328986164 width=24) (actual time=357598.331..594226.355 rows=161151623 loops=1) Workers Planned: 2 Workers Launched: 2 -> HashAggregate (cost=64652301.50..74329121.07 rows=164493082 width=24) (actual time=357572.082..578038.457 rows=53717208 loops=3) Group Key: id1, id2, id3 Planned Partitions: 64 Batches: 65 Memory Usage: 328209kB Disk Usage: 25774088kB Worker 0: Batches: 65 Memory Usage: 328209kB Disk Usage: 25375784kB Worker 1: Batches: 65 Memory Usage: 328209kB Disk Usage: 25382936kB -> Parallel Seq Scan on large_table (cost=0.00..29740358.40 rows=685387840 width=24) (actual time=12.954..99596.289 rows=548310252 loops=3) Planning Time: 5.380 ms Execution Time: 745750.371 ms -- Jeremiah CONFIDENTIALITY NOTICE: The information contained in this email (and any attachments) is privileged and confidential and protected from disclosure. If you are not the intended recipient of this email or the attachments, be aware that any disclosure, copying, distribution or use of this email or any attachment is strictly prohibited and you should not read the message or read or open any attachment. If you have received this email by mistake, please immediately notify the sender and delete it permanently from your system. Agri Stats, Inc. and its subsidiaries will not be held liable to any person or entity resulting from the unintended or unauthorized use of any information contained in this email.
How much size saved by updating column to NULL ?
Hello, in my company, some columns rarely used in a PG database 14.8 have been set to NULL in order to save disk space (datecreation & acteurcreation in following table) . create table example ( id varchar(25) not null, datecreationtimestamp(6), acteurcreation varchar(50), valeurunit smallint ) I am wondering if it is really useful for every type of column. Intuitively, i would say that it does not save space for fixed field datecreation as it is a fixed size column. Do we save 8 bytes by timestamp column updated to NULL or not ? Have a good day Sébastien TANIERE
Re: How much size saved by updating column to NULL ?
On Friday, January 12, 2024, Sébastien TANIERE wrote: > Hello, > in my company, some columns rarely used in a PG database 14.8 have been > set to NULL in order to save disk space (datecreation & acteurcreation in > following table) . > > create table example > ( > id varchar(25) not null, > datecreationtimestamp(6), > acteurcreation varchar(50), > valeurunit smallint > ) > > I am wondering if it is really useful for every type of column. > Intuitively, i would say that it does not save space for fixed field > datecreation as it is a fixed size column. > > Do we save 8 bytes by timestamp column updated to NULL or not ? > > You probably should just measure it yourself. But yes, the representation of null in a tuple is the absence of data and a bit in the nulls bitmap. So there is overhead but also savings. The net effect is case specific. David J.
Re: How much size saved by updating column to NULL ?
On Fri, Jan 12, 2024 at 7:45 AM Sébastien TANIERE wrote: > Hello, > in my company, some columns rarely used in a PG database 14.8 have been > set to NULL in order to save disk space (datecreation & acteurcreation in > following table) . > > create table example > ( > id varchar(25) not null, > datecreationtimestamp(6), > acteurcreation varchar(50), > valeurunit smallint > ) > > I am wondering if it is really useful for every type of column. > Intuitively, i would say that it does not save space for fixed field > datecreation as it is a fixed size column. > > Do we save 8 bytes by timestamp column updated to NULL or not ? > I don't think the record on the page is rewritten in a more compact form. Even if it were, the page would just have a small hole in it. A different version of this question might be whether the tuple is deleted and then the hopefully smaller row inserted some where else when "UPDATE example SET datecreation = NULL;" is executed. But of course then you'd use *more* disk space, since now the file(s) will be full of holes. Vacuum will clean them up so that new data can be inserted there, but the files would not be shrunk.
Re: How much size saved by updating column to NULL ?
Ron Johnson writes: > On Fri, Jan 12, 2024 at 7:45 AM Sébastien TANIERE > wrote: >> Do we save 8 bytes by timestamp column updated to NULL or not ? > I don't think the record on the page is rewritten in a more compact form. > Even if it were, the page would just have a small hole in it. > A different version of this question might be whether the tuple is deleted > and then the hopefully smaller row inserted some where else when > "UPDATE example SET datecreation = NULL;" is executed. But of course then > you'd use *more* disk space, since now the file(s) will be full of holes. > Vacuum will clean them up so that new data can be inserted there, but the > files would not be shrunk. Yes, that's exactly what happens. As to whether there is space savings: as David mentioned, if a tuple has any null columns then it includes a "nulls bitmap", costing 1 bit per column. Therefore, if you rewrite a row that had been all not null with one null column, it could actually get wider, especially when there are many columns. But once the bitmap is present, setting additional columns to null is pure savings (and yes, there is savings -- we simply don't store anything for a null column). There's more detail at https://www.postgresql.org/docs/current/storage-page-layout.html regards, tom lane
Issue with loading unicode characters with copy command
Hi, I have a UTF8 database and simple table with two columns (integer and varchar). Created a csv file with some multibyte characters and trying to perform load operation using the copy command. Database info: Postgresql database details: Name| Owner | Encoding | Collate | Ctype | Access privileges ---+--+--+++--- postgres | postgres | UTF8 | English_India.1252 | English_India.1252 | (Note: I also tried with collate utf8 and no luck) postgres=# set client_encoding='UTF8'; SET Table: create table public.test ( PKCOL integer not null, STR1 character varying(64) null, primary key( PKCOL )) csv contents: 1|"àáâãäåæçèéêëìíîï" After data loading, actual data is becoming à áâãäåæçèéêëìÃîï hex of this is - c2a1c2a2c2a3c2a4c2a5c2a6c2a7c2a8c2a9c2aac2abc2acc2aec2af The hex values are indeed the UTF-8 encodings of the characters in your expected string, and the presence of `C2` before each character is indicative of how UTF-8 represents certain characters. In UTF-8, characters from the extended Latin set (like `à`, `á`, `â`, etc.) are represented as two bytes. The first byte `C2` or `C3` indicates that this is a two-byte character, and the second byte specifies the character. For example: - `à` is represented as `C3 A0` - `á` is `C3 A1` - `â` is `C3 A2`, and so on. In this case, the `C2` byte is getting interpreted as a separate character and that is the likely reason that an `Â` (which corresponds to `C2`) is seen before each intended character. Looks like UTF-8 encoded data is mistakenly interpreted as Latin-1 (ISO-8859-1) or Windows-1252, where each byte is treated as a separate character. Please advise. Thank you very much. Regards, Kiran
Software Bill of Materials (SBOM)
Good afternoon. I'm looking for a way to build sbom files for assembly postgresql, to perform software composition analysis (SCA). Please, tell me how can I do this? Thank you.
Support for arm64 windows is absent - Would be nice to have!
Hi I’m trying to use PosgreSQL from a Qt application but libpq and ODBC binaries are currently unavailable so I can only use x64 applications at present! It would be good to have proper support for Windows arm64 architecture. At the very least ODBC connectivity, better still to have libpq available. As it stands native arm64 applications running on windows simply CANNOT USE PostgreSQL. Arm64 ports of existing x64 applications cannot be released (or even built) until required 3rd party products become available. BACKGROUND While it is true that Microsoft don't publish any official sales figures for Windows 11 arm64 architecture it's pretty clear that since its origins back in 2017 the usage has increased steadily and is now increasing much more rapidly. Its not just a market for Parallels VM users (many of whom are software developers) as today there are an increasing number of laptops and desktop arm64 PC's from manufacturers including HP and Lenovo. Porting software to windows arm64 is in most cases just a matter of recompiling with a different toolchain and it's only awkward little things like the use of SIMD intrinsics or assembler code that can complicate matters. For some reason however database connectivity seems to be one of the few thing that has been overlooked by some 3rd party providers. As it happens, There are other DBMS providers (beginning with M) who have so far failed to release arm64 versions. PostgreSQL currently lacks arm64 connectivity and native binaries. At the end of the day it means that users of arm64 Windows will likely not be able to use these 3rd party products! I wonder which of these vendors will be the first? NB I would have just commented on one of the existing tickets relating to this BUT instead I get a message about a cooling off period so instead I'll just use this form! (FAILED!!!) PS tried to file as bug report but was rejected as considered as not a bug so instead am posting to this mailing list!
Re: Time zone offset in to_char()
On 1/12/24 00:51, Alban Hertroijs wrote: Following up on my own mail from yesterday evening, here's the output that shows the function using set_config 'leaking' the timezone change to outside the function (the first select vs. the 2^nd select) into the (same) transaction, whereas the function with the time zone bound to the header does not (the 3^rd select). These are all from a single session, ran consecutively - a straight copy-paste from psql. Alright I see what you are talking about. ciscache=> select ToDatetimeOffset(current_timestamp, 'Europe/Amsterdam'), to_char(current_timestamp, '-MM-DD HH24:MI:SS.US0 TZH:TZM'); todatetimeoffset | to_char + 2024-01-12 09:41:44.7019350 +01:00 | 2024-01-12 09:41:44.7019350 +01:00 (1 row) ciscache=> select to_char(current_timestamp, '-MM-DD HH24:MI:SS.US0 TZH:TZM'); to_char 2024-01-12 08:41:54.4739800 +00:00 (1 row) ciscache=> select ToDatetimeOffsetNL(current_timestamp), to_char(current_timestamp, '-MM-DD HH24:MI:SS.US0 TZH:TZM'); todatetimeoffsetnl | to_char + 2024-01-12 09:42:44.1989210 +01:00 | 2024-01-12 08:42:44.1989210 +00:00 (1 row) Regards, Alban Hertroijs. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Issue with loading unicode characters with copy command
On 1/12/24 07:23, Kiran K V wrote: Hi, I have a UTF8 database and simple table with two columns (integer and varchar). Created a csv file with some multibyte characters and trying to perform load operation using the copy command. The multibyte characters come from what character set? __ __ Database info: Postgresql database details: Name | Owner | Encoding | Collate | Ctype | Access privileges ---+--+--+++--- postgres | postgres | UTF8 | English_India.1252 | English_India.1252 | __ __ (Note: I also tried with collate utf8 and no luck) postgres=# set client_encoding='UTF8'; SET __ __ Table: create table public.test ( PKCOL integer not null, STR1 character varying(64) null, primary key( PKCOL )) csv contents: 1|"àáâãäåæçèéêëìíîï" __ __ After data loading, actual data is becoming à áâãäåæçèéêëìÃîï hex of this is - c2a1c2a2c2a3c2a4c2a5c2a6c2a7c2a8c2a9c2aac2abc2acc2aec2af __ __ The hex values are indeed the UTF-8 encodings of the characters in your expected string, and the presence of `C2` before each character is indicative of how UTF-8 represents certain characters. In UTF-8, characters from the extended Latin set (like `à`, `á`, `â`, etc.) are represented as two bytes. The first byte `C2` or `C3` indicates that this is a two-byte character, and the second byte specifies the character. For example: - `à` is represented as `C3 A0` - `á` is `C3 A1` - `â` is `C3 A2`, and so on. In this case, the `C2` byte is getting interpreted as a separate character and that is the likely reason that an `Â` (which corresponds to `C2`) is seen before each intended character. Looks like UTF-8 encoded data is mistakenly interpreted as Latin-1 (ISO-8859-1) or Windows-1252, where each byte is treated as a separate character. Please advise. Thank you very much. Regards, Kiran -- Adrian Klaver adrian.kla...@aklaver.com
Re: Support for arm64 windows is absent - Would be nice to have!
On Fri, Jan 12, 2024 at 6:13 PM David Hussey wrote: > It would be good to have proper support for Windows arm64 architecture. > Hi. Have you seen: https://www.linaro.org/windows-on-arm/postgres/ ? https://linaro.atlassian.net/wiki/spaces/WOAR/pages/28707291320/PostgreSQL ? I just did a quick search, FWIW. --DD
Re: Time zone offset in to_char()
On 1/12/24 02:01, Alban Hertroijs wrote:
ciscache=> select ToDatetimeOffset(current_timestamp,
'Europe/Amsterdam'), to_char(current_timestamp, '-MM-DD
HH24:MI:SS.US0 TZH:TZM');
ERROR: invalid value for parameter "TimeZone": "tz_"
CONTEXT: SQL statement "set local timezone to tz_"
PL/pgSQL function todatetimeoffset(timestamp with time zone,text) line 4
at SQL statement
But set_config() doesn't have that issue.
An example of how to deal with this:
CREATE OR REPLACE FUNCTION public.todatetimeoffset(ts_ timestamp with
time zone, tz_ text)
RETURNS character varying
LANGUAGE plpgsql
AS $function$
begin
--perform set_config('timezone', tz_, true /* local */);
EXECUTE 'set local timezone to ' || quote_literal(tz_);
return to_char(ts_, '-MM-DD HH24:MI:SS.US0 TZH:TZM');
end;
$function$
select ToDatetimeOffset(now(), 'Europe/Amsterdam');
todatetimeoffset
2024-01-12 18:32:02.5486820 +01:00
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: [EXTERNAL]Re: Refresh Materialized View Issue
On 1/12/24 6:34 AM, Jeremiah Bauer wrote: That'd be a band-aid at best, because we know that the query used to define the materialized view runs in a reasonable amount of time on it's own, as does a CTAS. So either the REFRESH is doing something odd when writing into the new relation (which looking at the code seems very unlikely), or REFRESH is getting a different query plan for some reason. Unfortunately, I don't know of any easy way to get the query plan for the REFRESH (it might be possible via gdb, but I'm not sure). We do at least know that the REFRESH is using parallel workers. Can you post the output of EXPLAIN ANALYZE for the SELECT? That might provide some clues. Sure, here is the explain analyze for the select: HashAggregate (cost=123986470.20..129486707.63 rows=164493082 width=24) (actual time=697250.385..741548.965 rows=59015171 loops=1) Group Key: id1, id2, id3 Planned Partitions: 64 Batches: 65 Memory Usage: 328209kB Disk Usage: 6750176kB -> Gather (cost=64653301.50..107228737.47 rows=328986164 width=24) (actual time=357598.331..594226.355 rows=161151623 loops=1) Workers Planned: 2 Workers Launched: 2 -> HashAggregate (cost=64652301.50..74329121.07 rows=164493082 width=24) (actual time=357572.082..578038.457 rows=53717208 loops=3) Group Key: id1, id2, id3 Planned Partitions: 64 Batches: 65 Memory Usage: 328209kB Disk Usage: 25774088kB Worker 0: Batches: 65 Memory Usage: 328209kB Disk Usage: 25375784kB Worker 1: Batches: 65 Memory Usage: 328209kB Disk Usage: 25382936kB -> Parallel Seq Scan on large_table (cost=0.00..29740358.40 rows=685387840 width=24) (actual time=12.954..99596.289 rows=548310252 loops=3) Planning Time: 5.380 ms Execution Time: 745750.371 ms Ok, so that's using a parallel query as well, just like REFRESH, so no help there. At this point I think you'd need to do some OS-level investigation using a tool like perf to determine what the parent process is doing once the workers finish. BTW, I did just come across a script[1] that will print the query plan for a running query. Based on what you've said I suspect it wouldn't tell us much here, but I wanted to mention it. 1: https://github.com/StarfishStorage/explain-running-query -- Jim Nasby, Data Architect, Austin TX
How to redirect output from PostgreSQL pg_recvlogical to a file or a pipe?
Hello! How do I redirect logical decoding output from the PostgreSQL CLI tool pg_recvlogical either to a file or to another command via a pipe? I ask because when I try the obvious, no output is recorded or sent: pg_recvlogical -d postgres --slot test --start -f - >> sample.jsonl Lest there be any confusion, I already created the slot in an earlier step. Moreover, I can verify that if I omit the output redirection >> sample then it does work, insofar as it emits the expected change events when I perform DML in another terminal window. When I include the redirection (or alternatively, set up a pipeline), then nothing happens. Note that I am aware of the option to pass a filename to the -f switch to write to a file. That works, but it's not what I'm after because it doesn't help update my mental model of how this is supposed to work. Based on my current (flawed) mental model built up from command line experience with other tools, this *should* work. I should be able to send the output to stdout and then redirect it to a file. It surprises me that I cannot. Anyway, thanks! Best, David
Re: How to redirect output from PostgreSQL pg_recvlogical to a file or a pipe?
On 1/12/24 11:34, David Ventimiglia wrote: Hello! How do I redirect logical decoding output from the PostgreSQL CLI tool |pg_recvlogical| either to a file or to another command via a pipe? I ask because when I try the obvious, no output is recorded or sent: |pg_recvlogical -d postgres --slot test --start -f - >> sample.jsonl | Lest there be any confusion, I already created the slot in an earlier step. Moreover, I can verify that if I omit the output redirection |>> sample| then it does work, insofar as it emits the expected change events when I perform DML in another terminal window. When I include the redirection (or alternatively, set up a pipeline), then nothing happens. You left out the important part of the SO question: pg_recvlogical -d postgres --slot test --create-slot -P wal2json I can get: pg_recvlogical -d postgres --slot test --start -f - >> sample.jsonl to work when I do: pg_recvlogical -d postgres --slot test --create-slot wal2json seems to be the issue. Note that I am aware of the option to pass a filename to the -f switch to write to a file. That works, but it's not what I'm after because it doesn't help update my mental model of how this is supposed to work. Based on my current (flawed) mental model built up from command line experience with other tools, this /should/ work. I should be able to send the output to stdout and then redirect it to a file. It surprises me that I cannot. Anyway, thanks! Best, David -- Adrian Klaver adrian.kla...@aklaver.com
Re: How to redirect output from PostgreSQL pg_recvlogical to a file or a pipe?
David: On Fri, 12 Jan 2024 at 20:35, David Ventimiglia wrote: > pg_recvlogical -d postgres --slot test --start -f - >> sample.jsonl > > Lest there be any confusion, I already created the slot in an earlier step. > Moreover, I can verify that if I omit the output redirection >> sample then > it does work, insofar as it emits the expected change events when I perform > DML in another terminal window. When I include the redirection (or > alternatively, set up a pipeline), then nothing happens. Have you tested the command as printed ( with -f - ) but without redirection? Have you ruled out the usual suspect, stdout is line buffered when going to a tty, full buffered when not ? ( by killing pg_revlogical and/or insuring a long enough output is generated ) Francisco Olarte.
Re: How to redirect output from PostgreSQL pg_recvlogical to a file or a pipe?
On 1/12/24 14:03, David Ventimiglia wrote:
Reply to list also.
Ccing list
Hi Adrian,
I left out the creation of the slot both from the SO question and from
this mailing list question, because I believe it's a red herring. I run
into the same problem with the default output plugin as I do with the
wal2json plugin. However, the problem is a little different from what I
described. It turns out it's not output redirection from pg_recvlogical
sending to stdout that fails. Rather, it's output redirection from
pg_recvlogical sending to stdout and then passed through a pipeline that
fails. Or something like that.
This works. The sample.txt file is non-empty.
pg_recvlogical -d postgres --slot=test --create-slot
pg_recvlogical -d postgres -n --slot=test --start -f - > sample.txt
This does not work. The sample.txt file is empty.
pg_recvlogical -d postgres --slot=test --create-slot
pg_recvlogical -d postgres -n --slot=test --start -f - | awk
'{print}' > sample.txt
Weirdly, this works. The sample.txt file is non-empty.
pg_recvlogical -d postgres --slot=test --create-slot
pg_recvlogical -d postgres -n --slot=test --start -f - | cat >
sample.txt
FWIW, this is demonstrated in this screen-cast
. I suspect it has something to do with
the flushing of buffers, as other people have suggested.
Thanks!
David
On Fri, Jan 12, 2024 at 1:48 PM Adrian Klaver > wrote:
On 1/12/24 11:34, David Ventimiglia wrote:
> Hello! How do I redirect logical decoding output from the
PostgreSQL CLI
> tool |pg_recvlogical| either to a file or to another command via
a pipe?
> I ask because when I try the obvious, no output is recorded or sent:
>
> |pg_recvlogical -d postgres --slot test --start -f - >>
sample.jsonl |
>
> Lest there be any confusion, I already created the slot in an
earlier
> step. Moreover, I can verify that if I omit the output
redirection |>>
> sample| then it does work, insofar as it emits the expected change
> events when I perform DML in another terminal window. When I
include the
> redirection (or alternatively, set up a pipeline), then nothing
happens.
You left out the important part of the SO question:
pg_recvlogical -d postgres --slot test --create-slot -P wal2json
I can get:
pg_recvlogical -d postgres --slot test --start -f - >> sample.jsonl
to work when I do:
pg_recvlogical -d postgres --slot test --create-slot
wal2json seems to be the issue.
>
> Note that I am aware of the option to pass a filename to the -f
switch
> to write to a file. That works, but it's not what I'm after
because it
> doesn't help update my mental model of how this is supposed to work.
> Based on my current (flawed) mental model built up from command line
> experience with other tools, this /should/ work. I should be
able to
> send the output to stdout and then redirect it to a file. It
surprises
> me that I cannot.
>
> Anyway, thanks!
>
> Best,
>
> David
>
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: How to redirect output from PostgreSQL pg_recvlogical to a file or a pipe?
Let me just lay my cards on the table. What I'm really trying to do is capture change events with logical decoding and then send them back into the database into a database table. To do that, I believe I need to process the event records into SQL insert statements somehow. xargs is one option. jq is another. My idea was to pipe the pg_recvlogical output through a jq transform into psql, but that didn't work (neither did earlier experiments with xargs). Redirecting the output to an intermediate file via stdout was just an attempt to reduce the problem to a simpler problem. I had *thought* (incorrectly, as it turns out) that I was unable even to redirect it to a file, but evidently that's not the case. I can redirect it to a file. What I cannot seem to do is run it through a jq filter and pipe it back into psql. I can run it through a jq filter and redirect it to a file, no problem. But the minute I change it to pipe to psql, it ceases to produce the desired result. I tried illustrating this in this screencast: https://asciinema.org/a/npzgcTN8DDjUdkaZlVyYJhZ5y Perhaps another way to put this is, how *does* one capture output from pg_recvlogical and pipe it back into the database (or if you like, some other database) with psql. When I set out to do this I didn't think bash pipes and redirection would be the hard part, and yet here I am. Maybe there's some other way, because I'm fresh out of ideas. Best, David On Fri, Jan 12, 2024 at 8:42 PM Juan Rodrigo Alejandro Burgos Mella < rodrigoburgosme...@gmail.com> wrote: > > try use the following syntax (yes, with a 2 before the greater sign) > > pg_recvlogical -d postgres --slot test --start -f - 2>> sample.jsonl > > Atte > JRBM > > El vie, 12 ene 2024 a las 16:35, David Ventimiglia (< > davidaventimig...@hasura.io>) escribió: > >> Hello! How do I redirect logical decoding output from the PostgreSQL CLI >> tool pg_recvlogical either to a file or to another command via a pipe? I >> ask because when I try the obvious, no output is recorded or sent: >> >> >> pg_recvlogical -d postgres --slot test --start -f - >> sample.jsonl >> >> >> Lest there be any confusion, I already created the slot in an earlier >> step. Moreover, I can verify that if I omit the output redirection >> >> sample then it does work, insofar as it emits the expected change events >> when I perform DML in another terminal window. When I include the >> redirection (or alternatively, set up a pipeline), then nothing happens. >> >> Note that I am aware of the option to pass a filename to the -f switch to >> write to a file. That works, but it's not what I'm after because it >> doesn't help update my mental model of how this is supposed to work. Based >> on my current (flawed) mental model built up from command line experience >> with other tools, this *should* work. I should be able to send the >> output to stdout and then redirect it to a file. It surprises me that I >> cannot. >> >> Anyway, thanks! >> >> Best, >> >> David >> >
COBOL PRECOMPILER for PostGreSQL
Hello, I'm working with the opensource GNUCOBOL project. One of our efforts is to provide a migration path for organizations to move from commercial COBOL compilers to GNUCOBOL. One of the impediments to this migration is to move from an Oracle or IBM RDBMS solution to PostGreSQL. Most organizations are using one of these RDBMS solutions with COBOL precompilers supplied by the vendor and have embedded SQL in their source code. This represents a significant challenge to any migration as there is not a COBOL precompiler for PostGreSQL. I would like to propose that the ECPG precompiler for C, to be modified to accept either C or COBOL. Another option would be to clone ECPG to work with the COBOL language. For the most of the COBOL variable types it should not be too difficult to convert them to C variables which would reduce the effort. Both of these RDBMS use the syntax similar to C, EXEC SQL . END-EXEC, where text between them is the actual SQL statement or variables. Thanks for your consideration, -- Chuck Haatvedt chuck.haatv...@gmail.com
Re: COBOL PRECOMPILER for PostGreSQL
Chuck Haatvedt writes: > I would like to propose that the ECPG precompiler for C, to be > modified to accept either C or COBOL. Another option would be to > clone ECPG to work with the COBOL language. The latter option seems much more likely to succeed than the former. Either way, however, I'd guess that the amount of COBOL expertise in the existing Postgres community is not distinguishable from zero. So "proposing" this is not going to make it happen. If you want to step up with a patch, then maybe, but even then I'm not sure we'd want to commit to maintaining it as part of the core PG project. The most likely path to success is to plan on creating an ECPG fork maintained separately by interested people. FWIW, the only reason ECPG is part of our core distro is that years ago it used to require significant manual effort to keep it up to date with core grammar changes. Since we invented a process for auto-generating the ECPG grammar from the core grammar, it's seemed like maybe ECPG could be kept at arm's length after all, though we've not seriously tried to push it out as a separate project. We could talk about what additional stuff might be needed in that infrastructure to make it work for a COBOL wrapper ... but I think somebody else has to take point on making that happen. regards, tom lane
