Re: Seems to be impossible to set a NULL search_path

On Wed, Jul 13, 2022 at 10:02 PM Bryn Llewellyn wrote: > > > > * The owner name | The *identifier* for the owner > name +- Joe > | "Joe"* > > This is what I've been banging on about all the time. It seems that I'm > the only person in the pgsq

Re: Seems to be impossible to set a NULL search_path

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> role_name >> - >> Bllewell >> ... >> "Bllewell" >> >> ...Are there really two distinct roles with those two names?... > > Is this another one of your mistakes in presenting a self-contained test case? I

Re: Seems to be impossible to set a NULL search_path

On Wed, Jul 13, 2022 at 4:33 PM Bryn Llewellyn wrote: > > > > > > * role_name - Bllewell Exotic Me "Exotic Me" "Bllewell"* > > Of course I understand why I see both Exotic Me with no quotes and "Exotic > Me" with double quotes: I asked for it. But why do I see both Bllewell with > n

Re: Seems to be impossible to set a NULL search_path

> b...@yugabyte.com wrote: > >> david.g.johns...@gmail.com wrote: >> >> As for "schema identifiers" vs. "schema names" - they both seem equally >> wrong. The list can very much contain sequences of characters that when >> interpreted as an identifier and look

Re: Issues with upserts

On 7/13/22 07:58, André Hänsel wrote: Jeremy Smith wrote: CREATE TABLE t (     id serial PRIMARY KEY,     name text NOT NULL UNIQUE,     address text NOT NULL ); This will yield “8”, showing that new sequence numbers have been generated for each attempt. If running out of id's

Re: Issues with upserts

On Wed, Jul 13, 2022 at 7:58 AM André Hänsel wrote: > > SELECT last_value FROM t_id_seq; > > > > This will yield “8”, showing that new sequence numbers have been generated > for each attempt. > Yep, an entire able-to-be-inserted tuple is formed every time. That requires evaluating defaults so t

Re: Issues with upserts

=?utf-8?Q?Andr=C3=A9_H=C3=A4nsel?= writes: > This will yield “8”, showing that new sequence numbers have been generated > for each attempt. Well, yeah, because the INSERT has to be attempted first, and that includes forming the whole candidate row including the nextval() result. If you're expec

RE: Issues with upserts

Jeremy Smith wrote: It sounds like you aren't adding a WHERE clause to prevent the duplicate rows from being updated. It would help if you could share your query, but in general this could look like this: INSERT INTO my_table (col1, col2) SELECT col1, col2 FROM other_table ON CONFLICT

Re: Issues with upserts

On Wednesday, July 13, 2022, André Hänsel wrote: > The wiki entry https://wiki.postgresql.org/wiki/UPSERT kind of suggests > that with the introduction of ON CONFLICT DO UPDATE the problem of upserts > is solved. But is it? > > > > A common use case for upserts is to keep a table up to date from

Re: Issues with upserts

- New versions are created for all rows, even if the data is identical. > This quickly fills up the WAL and puts unnecessary load on the tablespace > drives. > > - If the conflict target is not the serial column, the sequence backing > the serial column gets incremented for every row. This quickly

Issues with upserts

The wiki entry https://wiki.postgresql.org/wiki/UPSERT kind of suggests that with the introduction of ON CONFLICT DO UPDATE the problem of upserts is solved. But is it? A common use case for upserts is to keep a table up to date from an external data source. So you might have a cron job that ru

Re: Problem upgrading from 10 to 14 with pg_upgrade: unexpected error upgrading "template1" database for some clusters

From: Laurenz Albe Sent: Wednesday, July 13, 2022 3:16 AM > You configured "pg_hba.conf" so that a local login without password is > not possible.  The easiest solution is to change the "pg_hba.conf" > entries for "localhost" in IPv4 and IPv6 to "trust" for the duration of > the upgrade. I am af