agora inbox for [email protected]
help / color / mirror / Atom feedRe: How do I upsert depending on a second table?
5+ messages / 4 participants
[nested] [flat]
* Re: How do I upsert depending on a second table?
@ 2025-09-23 20:57 Adrian Klaver <[email protected]>
2025-09-23 21:02 ` Re: How do I upsert depending on a second table? Samuel Marks <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Adrian Klaver @ 2025-09-23 20:57 UTC (permalink / raw)
To: Samuel Marks <[email protected]>; [email protected]
On 9/23/25 13:36, Samuel Marks wrote:
> Attempt:
> ```sql
> CREATE TABLE org
> (
> "name" VARCHAR(50) PRIMARY KEY,
> owner VARCHAR(50) NOT NULL
> );
>
> CREATE TABLE repo
> (
> "id" INTEGER PRIMARY KEY,
> full_name VARCHAR(255) UNIQUE NOT NULL,
> org VARCHAR(50) NOT NULL REFERENCES org ("name")
> );
>
> INSERT INTO org(name, owner) VALUES ('org0', 'user0');
>
> INSERT INTO repo (id, full_name, org)
> VALUES (0, 'org0/name0 by wrong user', 'org0')
> ON CONFLICT (full_name) DO UPDATE
> SET full_name = EXCLUDED.full_name,
> org = EXCLUDED.org
> WHERE EXISTS (SELECT 1
> FROM org org_tbl
> WHERE org_tbl.name = EXCLUDED.org
> AND org_tbl.owner = 'wrong user')
> RETURNING *;
>
> SELECT * FROM repo WHERE id = 0;
> ```
Also, as shown, there is no conflict so I don't see the condition being
run per:
https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT
"
condition
An expression that returns a value of type boolean. Only rows for
which this expression returns true will be updated, although all rows
will be locked when the ON CONFLICT DO UPDATE action is taken. Note that
condition is evaluated last, after a conflict has been identified as a
candidate to update.
"
>
> This all succeeds. It should fail because the 'wrong user' is trying
> to create a new—or update an existing—repo.
>
> Thanks for all suggestions
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How do I upsert depending on a second table?
2025-09-23 20:57 Re: How do I upsert depending on a second table? Adrian Klaver <[email protected]>
@ 2025-09-23 21:02 ` Samuel Marks <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Samuel Marks @ 2025-09-23 21:02 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: [email protected]
Yeah I know my approach doesn't work, my question is, what is the
correct way to do an upsert for this schema?
Specifically:
- Create a new repo if one by that name doesn't exist + requestor is
`owner` of associated `org`
- Update an existing repo if one by that name does exist + requestor
is `owner` of associated `org`
On Tue, Sep 23, 2025 at 3:57 PM Adrian Klaver <[email protected]> wrote:
>
> On 9/23/25 13:36, Samuel Marks wrote:
> > Attempt:
> > ```sql
> > CREATE TABLE org
> > (
> > "name" VARCHAR(50) PRIMARY KEY,
> > owner VARCHAR(50) NOT NULL
> > );
> >
> > CREATE TABLE repo
> > (
> > "id" INTEGER PRIMARY KEY,
> > full_name VARCHAR(255) UNIQUE NOT NULL,
> > org VARCHAR(50) NOT NULL REFERENCES org ("name")
> > );
> >
> > INSERT INTO org(name, owner) VALUES ('org0', 'user0');
> >
> > INSERT INTO repo (id, full_name, org)
> > VALUES (0, 'org0/name0 by wrong user', 'org0')
> > ON CONFLICT (full_name) DO UPDATE
> > SET full_name = EXCLUDED.full_name,
> > org = EXCLUDED.org
> > WHERE EXISTS (SELECT 1
> > FROM org org_tbl
> > WHERE org_tbl.name = EXCLUDED.org
> > AND org_tbl.owner = 'wrong user')
> > RETURNING *;
> >
> > SELECT * FROM repo WHERE id = 0;
> > ```
>
> Also, as shown, there is no conflict so I don't see the condition being
> run per:
>
> https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT
>
> "
> condition
>
> An expression that returns a value of type boolean. Only rows for
> which this expression returns true will be updated, although all rows
> will be locked when the ON CONFLICT DO UPDATE action is taken. Note that
> condition is evaluated last, after a conflict has been identified as a
> candidate to update.
>
> "
> >
> > This all succeeds. It should fail because the 'wrong user' is trying
> > to create a new—or update an existing—repo.
> >
> > Thanks for all suggestions
> >
> >
>
>
> --
> Adrian Klaver
> [email protected]
^ permalink raw reply [nested|flat] 5+ messages in thread
* PgbackRest : backup command end: aborted with exception [101]
@ 2026-06-19 05:54 KK CHN <[email protected]>
2026-06-20 14:52 ` Re: PgbackRest : backup command end: aborted with exception [101] Greg Sabino Mullane <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: KK CHN @ 2026-06-19 05:54 UTC (permalink / raw)
To: [email protected]
List,
I am running pgbackrest 2.52.1 on RHEL 9.4 Postgres 16 for backup to a
remote repository for the last 4 months. All works fine in the initial
months.
Recently the full backup always fails without completing the backup to Repo
Server.
Only differential backup is completing the process as scheduled .
Whenever a full backup is scheduled, after running for hours it fails
with error as in the subject line. Earlier it was working and the last
full backup was completed in April 2026 . Rest of the period differential
backup only completes all full backup processes failed till today.
Yesterday 18 June 2026 at 09:30 AM I ran the full backup from a cron
scheduler explicitly to test, it runs almost 24 hours and today 19 June
10.49 AM it stopped the full backup, Aborted the process.
*What may be the issue ? Any hints are much appreciated. ** Any more
inputs required ? *
Thank you,
Krishane
*log trace below for reference. *
2026-06-19 10:49:12.499 P00 DEBUG:
command/backup/backup::backupJobResult: => void
2026-06-19 10:49:12.590 P00 DEBUG:
command/backup/backup::backupDbPing: (backupData: {BackupData}, force:
false)
2026-06-19 10:49:12.590 P00 DEBUG: db/db::dbPing: (this: {client:
null, remoteClient: {name: remote-0 ssh protocol on '10.140.0.20', state:
idle}}, force: false)
2026-06-19 10:49:12.590 P00 DEBUG: db/db::dbIsInRecovery: (this:
{client: null, remoteClient: {name: remote-0 ssh protocol on
'10.140.0.202', state: idle}})
2026-06-19 10:49:12.590 P00 DEBUG: db/db::dbQueryColumn: (this:
{client: null, remoteClient: {name: remote-0 ssh protocol on
'10.140.0.202', state: idle}}, query: {"select
pg_catalog.pg_is_in_recovery()"})
2026-06-19 10:49:12.590 P00 DEBUG: db/db::dbQuery: (this: {client:
null, remoteClient: {name: remote-0 ssh protocol on '10.140.0.202', state:
idle}}, resultType: column, query: {"select
pg_catalog.pg_is_in_recovery()"})
2026-06-19 10:49:12.739 P00 DEBUG: command/exit::exitSafe: (result: 0,
error: true, signalType: 0)
2026-06-19 10:49:12.739 P00 ERROR: [101]: raised from remote-0 ssh
protocol on '10.140.0.202': NULL result required to complete request
--------------------------------------------------------------------
If SUBMITTING AN ISSUE please provide
the following information:
version: 2.52.1
command: backup
options: --delta
--exec-id=485653-6407012b --log-level-console=info --log-level-file=debug
--pg1-host=10.140.0.202 --pg1-host-user=postgres
--pg1-path=/var/postgres16/data --pg-version-force=16 --process-max=5
--repo1-block --repo1-bundle --repo1-cipher-pass=<redacted>
--repo1-cipher-type=aes-256-cbc --repo1-path=/data/DB_BACKUPS
--repo1-retention-diff=2 --repo1-retention-full=2 --stanza=Traffic_Repo
--start-fast --type=full
stack trace:
postgres/client.c:pgClientQuery:348:(debug log level required for
parameters)
db/protocol.c:dbQueryProtocol:(debug
log level required for parameters)
protocol/server.c:protocolServerProcess:(debug log level required for
parameters)
command/remote/remote.c:cmdRemote:(debug log level required for parameters)
main.c:main:(debug log level required
for parameters)
--------------------------------------------------------------------
2026-06-19 10:49:12.740 P00 INFO: backup command end: aborted with
exception [101]
2026-06-19 10:49:12.740 P00 DEBUG: command/exit::exitSafe: => 101
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PgbackRest : backup command end: aborted with exception [101]
2026-06-19 05:54 PgbackRest : backup command end: aborted with exception [101] KK CHN <[email protected]>
@ 2026-06-20 14:52 ` Greg Sabino Mullane <[email protected]>
2026-06-22 04:53 ` Re: PgbackRest : backup command end: aborted with exception [101] KK CHN <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Greg Sabino Mullane @ 2026-06-20 14:52 UTC (permalink / raw)
To: KK CHN <[email protected]>; +Cc: [email protected]
Smells like a timeout of some sort to me. Could be a firewall dropping an
"idle" connection. How long does the diff backup take to complete? How long
from when you kick off the full backup to the time it errors out? Is that
timeout consistent? See if you have ServerAliveInterval set in your ssh
config, and if not, consider adding it (and related vars).
Cheers,
Greg
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PgbackRest : backup command end: aborted with exception [101]
2026-06-19 05:54 PgbackRest : backup command end: aborted with exception [101] KK CHN <[email protected]>
2026-06-20 14:52 ` Re: PgbackRest : backup command end: aborted with exception [101] Greg Sabino Mullane <[email protected]>
@ 2026-06-22 04:53 ` KK CHN <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: KK CHN @ 2026-06-22 04:53 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; +Cc: [email protected]
it stopped full backup multiple times. This time it succeeded, One
difference, I have ran the full backup script again immediately after the
full backup failure happened after running for almost 20 Hours (2026-06-19
10:49:12.739 P00 ERROR: [101]: raised from remote-0 ssh protocol on
'10.140.0.202': NULL result required to complete request) . After this
time stamp I scheduled the full backup again on 2026-06-19 11:19 Hrs and
it completed the next day 2026-06-20 05:20 Hrs. (~= 18 Hrs).
I assume the full backup continues from the last point from where it
stopped , not afresh when you reschedule the full backup again ?
Here the time duration is observed.
full backup: 20260614-011004F
timestamp start/stop: 2026-06-19 11:19:05+05:30 / 2026-06-20
05:20:17+05:30
wal start/stop: 000000010000158A000000CA /
000000010000159A000000FE
database size: 3876.8GB, database backup size: 3876.8GB
repo1: backup size: 967.1GB
Almost 18 Hrs.
diff backup: 20260614-011004F_20260621-030404D
timestamp start/stop: 2026-06-21 03:04:04+05:30 / 2026-06-21
04:55:20+05:30
wal start/stop: 00000001000015AD000000E7 /
00000001000015AF00000057
database size: 3895.9GB, database backup size: 70.2GB
repo1: backup size: 12.8GB
backup reference list: 20260614-011004F
approx : 2 Hours
As suggested, add the ServerAliveInterval 60
ServerAliveCountMax 3 vars in sshd_config next time and observe the
difference.
Thank you,
Krshane.
On Sat, Jun 20, 2026 at 8:22 PM Greg Sabino Mullane <[email protected]>
wrote:
> Smells like a timeout of some sort to me. Could be a firewall dropping an
> "idle" connection. How long does the diff backup take to complete? How long
> from when you kick off the full backup to the time it errors out? Is that
> timeout consistent? See if you have ServerAliveInterval set in your ssh
> config, and if not, consider adding it (and related vars).
>
> Cheers,
> Greg
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-06-22 04:53 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-09-23 20:57 Re: How do I upsert depending on a second table? Adrian Klaver <[email protected]>
2025-09-23 21:02 ` Samuel Marks <[email protected]>
2026-06-19 05:54 PgbackRest : backup command end: aborted with exception [101] KK CHN <[email protected]>
2026-06-20 14:52 ` Re: PgbackRest : backup command end: aborted with exception [101] Greg Sabino Mullane <[email protected]>
2026-06-22 04:53 ` Re: PgbackRest : backup command end: aborted with exception [101] KK CHN <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox