From: hubert depesz lubaczewski <depesz@depesz.com>
To: Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>
Cc: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: AW: Truncate data from whole cluster
Date: Wed, 20 Dec 2023 12:50:31 +0100
Message-ID: <ZYLVBxk3PJ2KHDZy@depesz.com> (raw)
In-Reply-To: <e087df8b0c194a5fa64c625a9cf9cdee@med.uni-muenchen.de>
References: <CAJk5Atah42SXmNFDJmP22XtTeov+aKfSSkzCAAqXYzMe0En-8A@mail.gmail.com>
<ZYLArRMPsNu3ZooU@depesz.com>
<e087df8b0c194a5fa64c625a9cf9cdee@med.uni-muenchen.de>
On Wed, Dec 20, 2023 at 11:39:40AM +0000, Dischner, Anton wrote:
> Hi all,
>
> if you ask Bard LLM do do this you get:
>
> #!/bin/bash
>
> # List all databases in the PostgreSQL instance databases=$(psql -t -c "SELECT datname FROM pg_database;")
>
> # Truncate table data for each database
> for database in $databases; do
> echo "Processing database '$database'..."
> psql -d $database -c "SET FOREIGN_KEY_CHECKS=0;"
> tables=$(psql -d $database -t -c "SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public';")
> for table in $tables; do
> echo "Truncating table '$table' in database '$database'..."
> psql -d $database -c "TRUNCATE TABLE $table;"
> done
> psql -d $database -c "SET FOREIGN_KEY_CHECKS=1;"
> done
>
> This may be a good script to start.
> You will definetly test this first on a test installation!
No, it's not.
it's full of errors, and hallucinations.
couple of things that took me < 1 minute to notice:
1. will fail for database names that contain spaces in them
2. setting variable like psql -d ... -c "set .." - is pointless, the
value will be active only for this session, but this session has just
eneded
3. there is no "FOREIGN_KEY_CHECKS" setting in pg
4. will fail for table names with spaces
5. will ignore tables in non-public schemas
6. will fail for table names with UpperCaseLetters
7. for db with N tables (in public), it will call 101 psql connections
to this database. if your db has many small tables it will be
unreasonably slow
depesz, cursing whatever "magic ai tool" people try to use as base for
their scripts
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: depesz@depesz.com, Anton.Dischner@med.uni-muenchen.de, rajeshkumar.dba09@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: AW: Truncate data from whole cluster
In-Reply-To: <ZYLVBxk3PJ2KHDZy@depesz.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox