Re: function signature allow "default" keyword

[Peter Eisentraut]

On 15.05.23 10:33, jian he wrote:

function idea.
allow function calling using the default keyword for any of the input 
arguments.


example: https://dbfiddle.uk/FQwnfdmm 
So something like this "SELECT * FROM customer_orders(2579927, 
'order_placed_on DESC', default, 2);"

should work.


This is currently not supported by PostgreSQL.  I have half a patch for 
it, though, so maybe we'll get to it one day.

Re: "PANIC: could not open critical system index 2662" - twice

[Evgeny Morozov]

On 9/05/2023 3:32 am, Andres Freund wrote:
> Attached is a rough prototype of that idea (only using datconnlimit ==
> -2 for now).
> I guess we need to move this to -hackers. Perhaps I'll post subsequent
> versions below
> https://www.postgresql.org/message-id/20230314174521.74jl6ffqsee5mtug%40awork3.anarazel.de
>  ?
So now that a patch is in the works, can I drop the two corrupted
databases? Is there a workaround I can use to reduce the risk of running
into this issue again until a patch is released? (Which I guess would be
in August?)

How can I change replication slot's restart_lsn from SQL?

[hubert depesz lubaczewski]

Hi,
I'm working on a workaround for a bug in Pg
(https://www.postgresql.org/message-id/flat/Yz2hivgyjS1RfMKs%40depesz.com)
I want to create replication slot, and advance is manually, keeping it
always a bit lagging behind real replication slot.

I can create slot, no problem:
select pg_create_logical_replication_slot('depesz', 'test_decoding');
and then I can, theoretically, advance it to whatever position with
select * from pg_replication_slot_advance('depesz', '...');

*BUT* - it changes only confirmed_flush_lsn, leaving restart_lsn as it
was.

How can I advance restart_lsn of a slot?

Generally my idea is to get lsn from real replication slot, subtract,
let's say 1GB from it, and advance my "fake slot" to this value, this
keeping always buffer of 1GB in case the bug with removed wal happened
again.

I will be doing this on Pg12, which can limit my options, but perhaps
there is some way to do it via plain(ish) SQL ?

Best regards,

depesz

Packed raster data in postgresql?

[Elstermann, Mike]

Hello all,

is it possible to store also packed raster data (e.g. jpg, lzw tiff, ...) in 
PostgreSQL?

Thanks & best regards mikeE.

Re:

[Fabrice Chapuis]

I works, thank you

postgres [429007]=# select pg_xact_commit_timestamp('53013547');
┌───┐
│   pg_xact_commit_timestamp│
├───┤
│ 2023-05-15 16:10:00.150823+02 │
└───┘
(1 row)


On Tue, May 16, 2023 at 5:25 AM Kirk Wolak  wrote:

> On Mon, May 15, 2023 at 11:42 AM Fabrice Chapuis 
> wrote:
>
>> I am using postgres v.14 on rhel8
>> I enabled the track_commit_timestamp parameter.
>>
>> postgres [379418]=# show track_commit_timestamp ;
>> ┌┐
>> │ track_commit_timestamp │
>> ├┤
>> │ on │
>> └┘
>> (1 row)
>>
>> I performed a recover with the_recovery_target_time parameter. In the
>> postgres log the following informations are present:
>>
>> statement: alter system set recovery_target_time = '2023-05-15 16:10:00'
>>
>> recovery stopping before commit of transaction 53013547, time 2023-05-15
>> 16:10:00.150823+02
>>
>> I would like to get the xid related timestamp with the following query:
>>
>> postgres[379418]=#select pg_xact_commit_timestamp(53013547);
>> ERROR: function pg_xact_commit_timestamp(integer) does not exist
>> LINE 1: select pg_xact_commit_timestamp(53013547);
>> ^
>> HINT: No function matches the given name and argument types. You might
>> need to add explicit type casts.
>>
>> What is the xid type and how can I cast integer value to make
>> pg_xact_commit_timestamp to work?
>>
>> Regards
>>
>> Fabrice Chapuis
>>
>>
>>
> This is not obvious, but QUOTE that value:
> select pg_xact_commit_timestamp('53013547');
>
> And it figures it out!
>

Re: Packed raster data in postgresql?

[Ron]

On 5/16/23 10:03, Elstermann, Mike wrote:


Hello all,

is it possible to store also packed raster data (e.g. jpg, lzw tiff, ...) 
in PostgreSQL?




You can store any binary data you want in columns of type "bytea".

--
Born in Arizona, moved to Babylonia.

Re: How can I change replication slot's restart_lsn from SQL?

[hubert depesz lubaczewski]

On Tue, May 16, 2023 at 04:23:02PM +0200, hubert depesz lubaczewski wrote:
> Hi,
> I'm working on a workaround for a bug in Pg
> (https://www.postgresql.org/message-id/flat/Yz2hivgyjS1RfMKs%40depesz.com)
> I want to create replication slot, and advance is manually, keeping it
> always a bit lagging behind real replication slot.
> 
> I can create slot, no problem:
> select pg_create_logical_replication_slot('depesz', 'test_decoding');
> and then I can, theoretically, advance it to whatever position with
> select * from pg_replication_slot_advance('depesz', '...');
> 
> *BUT* - it changes only confirmed_flush_lsn, leaving restart_lsn as it
> was.
> 
> How can I advance restart_lsn of a slot?
> 
> Generally my idea is to get lsn from real replication slot, subtract,
> let's say 1GB from it, and advance my "fake slot" to this value, this
> keeping always buffer of 1GB in case the bug with removed wal happened
> again.
> 
> I will be doing this on Pg12, which can limit my options, but perhaps
> there is some way to do it via plain(ish) SQL ?

Hi,
if doing it via SQL is not an option, is there any way to have
replication slot and modify it's restart_lsn, regardless of whether it
will b e physical, or logical, and what tool shoudl I use.

I tried with pg_recvlogical, and read
https://www.postgresql.org/docs/current/protocol-replication.html but
I don't see how I can make restart_lsn advance to anything.

Best regards,

depesz

Re: Packed raster data in postgresql?

[Pavel Stehule]

Hi

út 16. 5. 2023 v 19:09 odesílatel Ron  napsal:

> On 5/16/23 10:03, Elstermann, Mike wrote:
>
> Hello all,
>
>
>
> is it possible to store also packed raster data (e.g. jpg, lzw tiff, ...)
> in PostgreSQL?
>
>
> You can store any binary data you want in columns of type "bytea".
>

or you can use some specialized extensions - there is some support in
PostGIS or in https://en.wikipedia.org/wiki/Rasdaman

Regards

Pavel


>
> --
> Born in Arizona, moved to Babylonia.
>

Re: "PANIC: could not open critical system index 2662" - twice

[Kirk Wolak]

On Tue, May 16, 2023 at 10:20 AM Evgeny Morozov <
postgres...@realityexists.net> wrote:

> On 9/05/2023 3:32 am, Andres Freund wrote:
> > Attached is a rough prototype of that idea (only using datconnlimit ==
> > -2 for now).
> > I guess we need to move this to -hackers. Perhaps I'll post subsequent
> > versions below
> >
> https://www.postgresql.org/message-id/20230314174521.74jl6ffqsee5mtug%40awork3.anarazel.de
> ?
> So now that a patch is in the works, can I drop the two corrupted
> databases? Is there a workaround I can use to reduce the risk of running
> into this issue again until a patch is released? (Which I guess would be
> in August?)
>

The only work around to avoid losing data that I know of are backups and
WAL backups.
Plus "hard core testing/validation" that they work.  We settled on
pg_backrest and are happy with it.

Technically, based on what I understand of this bug.  It did not corrupt
the WAL.  If that's true, then if
you had a basebackup and all the wall files, you could have played back and
recovered the data.
At least to some degree.  Assuming I am right.

HTH

Postresql HA 2 nodes

[Marcello Lorenzi]

Hi everyone,
we're looking for the best solution for a 2-nodes cluster in HA with
Postegresql 15. after some checks we are noticed about pgpool for the
management of balancing and automatic failover. Can it be considered a
viable and manageable solution for production?

Thanks,
Marcello

Re: Postresql HA 2 nodes

[Ron]

On 5/16/23 16:30, Marcello Lorenzi wrote:

Hi everyone,
we're looking for the best solution for a 2-nodes cluster in HA with 
Postegresql 15. after some checks we are noticed about pgpool for the 
management of balancing and automatic failover. Can it be considered a 
viable and manageable solution for production?


I implemented a 2/3-node HA system with Postgresql 13 and PgPool. Not in 
production yet, but seems stable.


By 2/3, I mean that there are 2 Pg nodes, and PgPool is installed on them 
plus a 3rd (smaller) VM.  You really need 3 nodes for quorum voting.


--
Born in Arizona, moved to Babylonia.

Re: "PANIC: could not open critical system index 2662" - twice

[Andres Freund]

Hi,

On 2023-05-16 14:20:46 +, Evgeny Morozov wrote:
> On 9/05/2023 3:32 am, Andres Freund wrote:
> > Attached is a rough prototype of that idea (only using datconnlimit ==
> > -2 for now).
> > I guess we need to move this to -hackers. Perhaps I'll post subsequent
> > versions below
> > https://www.postgresql.org/message-id/20230314174521.74jl6ffqsee5mtug%40awork3.anarazel.de
> >  ?
> So now that a patch is in the works, can I drop the two corrupted
> databases?

Yes.


> Is there a workaround I can use to reduce the risk of running into this
> issue again until a patch is released? (Which I guess would be in August?)

Try to prevent the DROP DATABASE from getting cancelled :/. If you want to go
a bit further, you could rename the database to *_dropped before dropping it,
and then try to do the DROP DATABASE. That way you'd at least know that it's
corrupt because of a failed DROP database.

Greetings,

Andres Freund

Postgres Incompatibility

[Avi Weinberg]

Hi Experts

I noticed this problem when creating a backup on Postgres 13 and restoring it 
on Postgres 15.

CREATE FUNCTION sync.show_pg_subscription1() RETURNS SETOF 
pg_subscription
LANGUAGE sql
AS $$
SELECT * from pg_subscription;
$$;

The Postgres backup creates the view syntax with " FROM 
sync.show_pg_subscription1()  show_pg_subscription1(oid, subdbid, subname, 
subowner, subenabled, subconninfo, subslotname, subsynccommit, 
subpublications);" The original syntax just had FROM 
sync.show_pg_subscription1() .

CREATE OR REPLACE VIEW sync.pg_subscription_view1
AS
SELECT show_pg_subscription1.oid,
show_pg_subscription1.subdbid,
show_pg_subscription1.subname,
show_pg_subscription1.subowner,
show_pg_subscription1.subenabled,
show_pg_subscription1.subconninfo,
show_pg_subscription1.subslotname,
show_pg_subscription1.subsynccommit,
show_pg_subscription1.subpublications
   FROM sync.show_pg_subscription1() show_pg_subscription1(oid, subdbid, 
subname, subowner, subenabled, subconninfo, subslotname, subsynccommit, 
subpublications);
We get an error:
ERROR: column reference "subconninfo" is ambiguous LINE 8: 
show_pg_subscription1.subconninfo,

If we remove the part generated by Postgres backup " show_pg_subscription1(oid, 
subdbid, subname, subowner, subenabled, subconninfo, subslotname, 
subsynccommit, subpublications);" it works.

What can be done so backups will be able to restore correctly? I have a not so 
elegant solution, but I hope to hear a better solution than creating additional 
view:

create view abc as
SELECT
oid,
subdbid,
subname,
subowner,
subenabled,
subconninfo,
subslotname,
subsynccommit,
subpublications
from pg_subscription p;

CREATE FUNCTION sync.show_pg_subscription1() RETURNS SETOF abc
LANGUAGE sql
AS $$
SELECT * from abc;
$$;

SELECT show_pg_subscription1.oid,
show_pg_subscription1.subdbid,
show_pg_subscription1.subname,
show_pg_subscription1.subowner,
show_pg_subscription1.subenabled,
show_pg_subscription1.subconninfo,
show_pg_subscription1.subslotname,
show_pg_subscription1.subsynccommit,
show_pg_subscription1.subpublications
   FROM sync.show_pg_subscription1() show_pg_subscription1(oid, subdbid, 
subname, subowner, subenabled, subconninfo, subslotname, subsynccommit, 
subpublications);

This works also with the additional part generated by Postgres backup

Thanks!
IMPORTANT - This email and any attachments is intended for the above named 
addressee(s), and may contain information which is confidential or privileged. 
If you are not the intended recipient, please inform the sender immediately and 
delete this email: you should not copy or use this e-mail for any purpose nor 
disclose its contents to any person.

Re: How can I change replication slot's restart_lsn from SQL?

[Laurenz Albe]

On Tue, 2023-05-16 at 19:35 +0200, hubert depesz lubaczewski wrote:
> > I'm working on a workaround for a bug in Pg
> > (https://www.postgresql.org/message-id/flat/Yz2hivgyjS1RfMKs%40depesz.com)
> > I want to create replication slot, and advance is manually, keeping it
> > always a bit lagging behind real replication slot.
> > 
> > I can create slot, no problem:
> > select pg_create_logical_replication_slot('depesz', 'test_decoding');
> > and then I can, theoretically, advance it to whatever position with
> > select * from pg_replication_slot_advance('depesz', '...');
> > 
> > *BUT* - it changes only confirmed_flush_lsn, leaving restart_lsn as it
> > was.
> > 
> > How can I advance restart_lsn of a slot?
> > 
> > Generally my idea is to get lsn from real replication slot, subtract,
> > let's say 1GB from it, and advance my "fake slot" to this value, this
> > keeping always buffer of 1GB in case the bug with removed wal happened
> > again.
> > 
> > I will be doing this on Pg12, which can limit my options, but perhaps
> > there is some way to do it via plain(ish) SQL ?
> 
> if doing it via SQL is not an option, is there any way to have
> replication slot and modify it's restart_lsn, regardless of whether it
> will b e physical, or logical, and what tool shoudl I use.
> 
> I tried with pg_recvlogical, and read
> https://www.postgresql.org/docs/current/protocol-replication.html but
> I don't see how I can make restart_lsn advance to anything.

You could shutdown the server and edit the file in "pg_replslot" with
a hex editor.  Not very convenient, and you'd have to study the source
to understand the format of the file.

Yours,
Laurenz Albe

Re: How can I change replication slot's restart_lsn from SQL?

[hubert depesz lubaczewski]

On Wed, May 17, 2023 at 08:16:41AM +0200, Laurenz Albe wrote:
> On Tue, 2023-05-16 at 19:35 +0200, hubert depesz lubaczewski wrote:
> > > I'm working on a workaround for a bug in Pg
> > > (https://www.postgresql.org/message-id/flat/Yz2hivgyjS1RfMKs%40depesz.com)
> > > I want to create replication slot, and advance is manually, keeping it
> > > always a bit lagging behind real replication slot.
> > > 
> > > I can create slot, no problem:
> > > select pg_create_logical_replication_slot('depesz', 'test_decoding');
> > > and then I can, theoretically, advance it to whatever position with
> > > select * from pg_replication_slot_advance('depesz', '...');
> > > 
> > > *BUT* - it changes only confirmed_flush_lsn, leaving restart_lsn as it
> > > was.
> > > 
> > > How can I advance restart_lsn of a slot?
> > > 
> > > Generally my idea is to get lsn from real replication slot, subtract,
> > > let's say 1GB from it, and advance my "fake slot" to this value, this
> > > keeping always buffer of 1GB in case the bug with removed wal happened
> > > again.
> > > 
> > > I will be doing this on Pg12, which can limit my options, but perhaps
> > > there is some way to do it via plain(ish) SQL ?
> > 
> > if doing it via SQL is not an option, is there any way to have
> > replication slot and modify it's restart_lsn, regardless of whether it
> > will b e physical, or logical, and what tool shoudl I use.
> > 
> > I tried with pg_recvlogical, and read
> > https://www.postgresql.org/docs/current/protocol-replication.html but
> > I don't see how I can make restart_lsn advance to anything.
> 
> You could shutdown the server and edit the file in "pg_replslot" with
> a hex editor.  Not very convenient, and you'd have to study the source
> to understand the format of the file.

OK. Shutting down production server every 5 minutes to advance the slot
isn't really an option.

How do "normal" wal consumers update restart_lsn? Is there a reason why
it can't be done using pg_rec(eive|v)* ?

Best regards,

depesz