Re: Purpose of pg_dump tar archive format?
On Tue, Jun 4, 2024 at 3:15 PM Ron Johnson wrote: > > But why tar instead of custom? That was part of my original question. > I've found it pretty useful for programmatically accessing data in a dump for large databases outside of the normal pg_dump/pg_restore workflow. You don't have to seek through one large binary file to get to the data section to get at the data.
Re: Purpose of pg_dump tar archive format?
On Tue, Jun 4, 2024 at 7:36 PM Ron Johnson wrote: > On Tue, Jun 4, 2024 at 3:47 PM Gavin Roy wrote: > >> >> On Tue, Jun 4, 2024 at 3:15 PM Ron Johnson >> wrote: >> >>> >>> But why tar instead of custom? That was part of my original question. >>> >> >> I've found it pretty useful for programmatically accessing data in a dump >> for large databases outside of the normal pg_dump/pg_restore workflow. You >> don't have to seek through one large binary file to get to the data section >> to get at the data. >> > > Interesting. Please explain, though, since a big tarball _is_ "one large > binary file" that you have to sequentially scan. (I don't know the > internal structure of custom format files, and whether they have file > pointers to each table.) > Not if you untar it first. > Is it because you need individual .dat "COPY" files for something other > than loading into PG tables (since pg_restore --table= does that, too), > and directory format archives can be inconvenient? > In the past I've used it for data analysis outside of Postgres. -- *Gavin M. Roy* CTO AWeber
Regression in PL/PGSQL code using RETURN QUERY with Postgres 14
Hi All, My team was testing against Postgres 14 to ensure we could cleanly upgrade and we ran across a regression in our PL/PGSQL code related to the updates to RETURN QUERY. Our code which works in previous versions of Postgres uses UPDATE RETURNING and INSERT RETURNING in combination with RETURN QUERY. It appears that in the parallelism updates, RETURN QUERY now only accepts SELECT queries. Was this an intentional change in behavior? We can easily refactor our PL/PGSQL functions to deal with the change, but if it was intentional, perhaps it should be documented. Regards, Gavin
Re: Regression in PL/PGSQL code using RETURN QUERY with Postgres 14
On Thu, Oct 7, 2021 at 2:54 PM Adrian Klaver wrote: > On 10/7/21 11:38 AM, Gavin Roy wrote: > > Hi All, > > > > My team was testing against Postgres 14 to ensure we could cleanly > > upgrade and we ran across a regression in our PL/PGSQL code related to > > the updates to RETURN QUERY. > > > > Our code which works in previous versions of Postgres uses UPDATE > > RETURNING and INSERT RETURNING in combination with RETURN QUERY. It > > appears that in the parallelism updates, RETURN QUERY now only accepts > > SELECT queries. > > I'm pretty sure folks are going to want to see an example of the code > and the errors thrown in version 14. > Sorry, I thought that was pretty clear. As an example, this worked prior to 14 and no longer works: CREATE TABLE foo ( bar SERIAL PRIMARY KEY, baz TEXT ); CREATE FUNCTION update_foo(in_bar INT4, in_baz TEXT) RETURNS SETOF foo AS $$ BEGIN RETURN QUERY UPDATE foo SET baz = in_baz WHERE bar = in_bar RETURNING bar, baz; END; $$ LANGUAGE PLPGSQL; postgres=# SELECT * FROM update_foo(1, 'baz?'); ERROR: query is not a SELECT CONTEXT: query: UPDATE foo SET baz = in_baz WHERE bar = in_bar RETURNING bar, baz PL/pgSQL function update_foo(integer,text) line 3 at RETURN QUERY
Re: Regression in PL/PGSQL code using RETURN QUERY with Postgres 14
Thanks so much Tom! Regards, Gavin On Thu, Oct 7, 2021 at 3:05 PM Tom Lane wrote: > Gavin Roy writes: > > Our code which works in previous versions of Postgres uses UPDATE > RETURNING > > and INSERT RETURNING in combination with RETURN QUERY. It appears that in > > the parallelism updates, RETURN QUERY now only accepts SELECT queries. > > Yeah, that's a mistake, previously reported and fixed at > > > https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=e0eba586b > > regards, tom lane >
Re: Inserts and bad performance
On Wed, Nov 24, 2021 at 2:15 PM Godfrin, Philippe E < philippe.godf...@nov.com> wrote: > Greetings > > I am inserting a large number of rows, 5,10, 15 million. The python code > commits every 5000 inserts. The table has partitioned children. > On the Python client side, if you're using psycopg, you should consider using using COPY instead of INSERT if you're not: https://www.psycopg.org/psycopg3/docs/basic/copy.html#copy And if using psycopg2, execute_batch might be of value: https://www.psycopg.org/docs/extras.html?highlight=insert#psycopg2.extras.execute_batch Regards, Gavin
