Re: infinite loop in an update statement
On Tue, Sep 10, 2024 at 4:49 PM Fabrice Chapuis wrote: > no lock, in view pg_stat_activity > > status = active > wait event = NULL > wait event type = NULL > > On Mon, Sep 9, 2024 at 5:00 PM Tom Lane wrote: > >> Fabrice Chapuis writes: >> > why this update does not return instantly? >> >> > UPDATE table_a a >> > SET col1 = (SELECT MIN(b.col1) >> > FROM table_b b >> > WHERE b.col2 = a.col2) >> >> Maybe query is waiting for a lock on one of those tables? >> >> regards, tom lane >> >
infinite loop in an update statement
Hi, table a and b are empty, this query does not return. It seems we enter in infinite loop. why this update does not return instantly? UPDATE table_a a SET col1 = (SELECT MIN(b.col1) FROM table_b b WHERE b.col2 = a.col2) Regards, Fabrice
Problem managing slots in Patroni
Hi, I use patroni version 3.2.1. There is a point that I do not understand in the slots management with Patroni. Patroni creates a slot automatically on primary node when there is a standby attached, although this slot does not belong to the patroni configuration. How to prevent the automatic creation of this slot? Thanks for helping Here is Patroni configuration: slots: barman_x: type: physical barman_y: type: physical logic_slot: database: test plugin: pgoutput postgresql: use_pg_rewind: true use_slots: true
Re:
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!
>
[no subject]
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
pg_basebackup / recovery
During recovery process of a self contained backup, how postgres know to stop reading wal when consistency is reached?
Re: pg_restore remap schema
Postgres allows us to rename a schema with the command *alter schema schema_orig rename to schema_dest*. however the definition of functions belonging to the original schema are not modified. CREATE OR REPLACE FUNCTION foo.fcount() RETURNS integer AS $$ declare v_count integer; BEGIN select count(*) into v_count >from foo.test; return v_count; -- raise notice 'The number is: %', v_count; END; $$ LANGUAGE plpgsql; select fcount(); fcount 2 (1 row) alter schema foo rename to bar; select fcount(); ERROR: relation "foo.test" does not exist LINE 2: from foo.test ^ Implementing the remap feature seemed to me feasible since this logic was similar to that of the native rename function. Oracle provides this option for its export tool. But I will not go further in this way I don't want to go against the advice of experts who have been working on the postgres code for years. Thank you for your comments Regards, Fabrice On Wed, Nov 16, 2022 at 3:42 PM Tom Lane wrote: > Guillaume Lelarge writes: > > Le mer. 16 nov. 2022 à 13:08, Fabrice Chapuis > a > > écrit : > >> I worked on the pg_dump source code to add remap schema functionality to > >> use it internally where I work. This is a first version that allows to > >> remap tables, views and sequences (only to export schema). Is this > >> development likely to interest the PG community and to continue this > >> development further? > > > If it doesn't work on functions, that will be a big issue. And to be > > honest, I don't think you can do it reliably on functions, especially > with > > dynamic queries in PL/pgsql. > > Yeah --- I fear there is no hope of making a feature like this that > works reliably enough that we'd accept it. pg_restore is just not > that smart about what is in the chunks of DDL that it processes, > and trying to make it smart enough is a losing game. > > regards, tom lane >
Re: pg_restore remap schema
Hi, I worked on the pg_dump source code to add remap schema functionality to use it internally where I work. This is a first version that allows to remap tables, views and sequences (only to export schema). Is this development likely to interest the PG community and to continue this development further? Regards, Fabrice On Mon, Aug 8, 2022 at 8:22 PM Tom Lane wrote: > Guillaume Lelarge writes: > > Le lun. 8 août 2022 à 18:28, Fabrice Chapuis a > >> Is a development in progress to add this option > > > Nope, never heard of someone working on this. > > People have asked for such a thing before, but it'd be quite difficult > to do reliably --- particularly inside function bodies, which aren't > normally parsed at all during a dump/restore. If you're willing to > accept a 95% solution, running the pg_restore output through "sed" > would likely work fairly well. I'd personally want to diff the > before-and-after scripts before applying though :-) > > regards, tom lane >
Re: pg_restore remap schema
Thank you for your reply. sed is a solution for making substitutions on plain text. But if we work with directory mode to use parallelism, I don't see how to proceed to make a schema remap. Editing the toc file in text mode does not work neither. Regards, Fabrice On Mon, Aug 8, 2022 at 9:27 PM Marcos Pegoraro wrote: > People have asked for such a thing before, but it'd be quite difficult >> to do reliably --- particularly inside function bodies, which aren't >> normally parsed at all during a dump/restore. If you're willing to >> accept a 95% solution, running the pg_restore output through "sed" >> would likely work fairly well. I'd personally want to diff the >> before-and-after scripts before applying though :-) >> > > Another 95% solution, if both schemas are on the same server, google for > function clone schema, you´ll find some of them. They create tables, > functions, sequences, views, ... > >
pg_restore remap schema
Hello, I can't find an option with pg_restore to rename an exported schema schema1 -> schema2 Is a development in progress to add this option Regards, Fabrice
