How pg_dump interact with not MVCC-safe commands?
As I understand, pg_dump first take snapshot and then lock all tables
it intended to dump. What happens if not MVCC-safe command committed
after snapshot but before lock? From comment to pg_dump.c I understand
that it may fail with 'cache lookup fa
> On 19 May 2024, at 20:37, Rich Shepard wrote:
>
> On Sun, 19 May 2024, Christophe Pettus wrote:
>
>> Of course, you can probably also shorten the query to:
>>
>> UPDATE people SET active=true WHERE ...
>>
>> Where ... is the predicate you would have used in the SELECT id WHERE ...
>
> Ah,
Hi,
Le lun. 20 mai 2024 à 11:27, PetSerAl a écrit :
> How pg_dump interact with not MVCC-safe commands?
>
> As I understand, pg_dump first take snapshot and then lock all tables
> it intended to dump. What happens if not MVCC-safe command committed
> after snapshot but before lock? From comment
Could someone kindly help me out with the correct syntax ?
My first thought was the below but that doesn't work:
update foo set upper(bar_times)=upper(bar_times)+interval '1' hour where
bar_id='abc';
ERROR: syntax error at or near "("
LINE 1: update event_sessions set upper(bar_times)=upper(bar
postgres 16.1; rocky 9.3
when connect to database postgres this query is OK, but run on user
database, got segmentation fault.
2024-05-20 17:16:54.942 WIB,,,134291,,6648420a.20c93,165,,2024-05-18
12:52:10 WIB,,0,LOG,0,"server process (PID 487952) was terminated by
signal 11: Segmentation fau
Hi
Try this one.
UPDATE foo
SET bar_times = bar_times + INTERVAL '1 hour'
WHERE bar_id = 'abc';
Regards
Kashif Zeeshan
Bitnine Global
On Mon, May 20, 2024 at 3:30 PM Laura Smith <
n5d9xq3ti233xiyif...@protonmail.ch> wrote:
> Could someone kindly help me out with the correct syntax ?
>
> My fir
On Mon, 20 May 2024 at 22:32, milist ujang wrote:
>
> postgres 16.1; rocky 9.3
>
> when connect to database postgres this query is OK, but run on user database,
> got segmentation fault.
I tried your query on 16.1 and I'm unable to reproduce the crash.
Are you able to recreate this on a freshly
as of subject, it should be 16.3.
scenario was:
- a few months ago install + deployed user data pg 16.2.
- 2 days ago, updated to 16.3
now I can reproduce the issue, the segment fault comes from
postgres[495645]: segfault at 0 ip 7f318b17e1f4 sp 7ffc7f1b15d8
error 4 in citus.so[7f318b0a40
On 2024-05-20 12:30 +0200, Laura Smith wrote:
> Could someone kindly help me out with the correct syntax ?
>
> My first thought was the below but that doesn't work:
>
> update foo set upper(bar_times)=upper(bar_times)+interval '1' hour where
> bar_id='abc';
> ERROR: syntax error at or near "("
On Mon, 20 May 2024, Alban Hertroys wrote:
That aside, while you’re not absolutely 100% definitely sure that an
UPDATE or DELETE statement is going to do exactly what you intended, and
for good measure if you are, wrapping such statements in a transaction
allows you to ROLLBACK to get back to th
>
>
> Aggregate (cost=512.53..512.54 rows=1 width=32) (actual
> time=8430.692..8430.724 rows=1 loops=1)
> Buffers: shared hit=2031540, temp read=954 written=956
> -> Subquery Scan on info (cost=510.85..512.52 rows=2 width=152)
> (actual time=8257.310..8430.532 rows=57 loops=1)
> Buff
what helps is
SET enable_nestloop = off;
query takes less then 2seconds
but it's probably not a good idea to change this flag
On Wed, 15 May 2024 at 13:23, David Rowley wrote:
> On Wed, 15 May 2024 at 21:08, Sašo Gantar wrote:
> > this query takes more than 8 seconds,
> > if i remove "AND ((pg
My question:
What happens if not MVCC-safe command committed after snapshot but before lock?
On Mon, May 20, 2024 at 12:33 PM Guillaume Lelarge
wrote:
>
> Hi,
>
> Le lun. 20 mai 2024 à 11:27, PetSerAl a écrit :
>>
>> How pg_dump interact with not MVCC-safe commands?
>>
>> As I understand, pg_dum
PetSerAl writes:
> My question:
> What happens if not MVCC-safe command committed after snapshot but before
> lock?
Then you'd get a dump that's not exactly consistent with the state
at the time of the snapshot. However, it would be the very same
database state that any other query would see at
> However, it would be the very same
> database state that any other query would see at that time.
Other queries can use different techniques to prevent this. For
example, lock tables before snapshot, as recommended in documentation.
I understand, that pg_dump can not use that, as it need snapshot
> On May 20, 2024, at 08:49, PetSerAl wrote:
> Basically, you need application cooperation to make
> consistent live database backup.
If it is critical that you have a completely consistent backup as of a
particular point in time, and you are not concerned about restoring to a
different proc
On Mon, May 20, 2024 at 11:54 AM Christophe Pettus wrote:
>
>
> > On May 20, 2024, at 08:49, PetSerAl wrote:
> > Basically, you need application cooperation to make
> > consistent live database backup.
>
> If it is critical that you have a completely consistent backup as of a
> particular point
17 matches
Mail list logo
