#!/bin/bash

   schema=s
    table=x
        t=$schema.$table
     file=/tmp/tsvbug.txt

tsv="title	author_first_name	author	publisher	year	publisher2	year2	remark
Casting off	Elizabeth Jane	Howard	Pan Books	2013		1995	642p. 4 of 5. The Cazalet cyclus
All change	Elizabeth Jane	Howard	Pan Books	2014		2013	573p. 5 of 5. The Cazalet cyclus
Schoene Geschichten! - Deutsche Erzaehlkunst aus 2 Jahrhunderten	Peter	von Matt (Herausg.)	Universal-Bibliothek Nr. 8840 - Philipp Reclam jun. GmbH & Co, Stuttgart	1992			605p. Jubilaeums-Edition. Auteurs:  Johann Wolfgang Goethe - Geschwisterliebe.  Jean Paul - Die Doppeltgaenger.  Bettina von Arnim - Der Koenigssohn.  Heinrich von Kleist - Der heilige Caecilie oder die Gewalt der Musik. - Die neuere (Gluecklichere) Werther.  Johann Peter Hebel - Fuenf Judengeschichten: Entraeglicher Raetselhandel. - Der falsche Edelstein. - Glimpf geht ueber Schimpf. - Der glaeserne Jude. - Gleiches mit gleichem.  Rahel Varnhagen - Die fuenf Traeume.  Clemens Brentano - Die Legende von einem Schwaben der das Leberlein gefressen.  Johann Wolfgang Goethe - Der Chodscha.  E.T.A. Hoffmann - Erscheinungen. - Das Schneiderlein aus Sachsenhausen.  Joseph von Eichendorff - Geschichte des Einsiedlers.  Adalbert Stifter - Die Sonnenfinsternis am 8. Juli 1842.  Jeremias Gotthelf - Wie man kaputt werden kann.  Heinrich Heine - Die Goetter im Exil.  Theodor Storm - Die Amtschirurgus -Heimkehr.  Gottfried Keller - Die Jungfrau und der Teufel. - Die Jungfrau als Ritter.  Friedrich Nietzsche - Die Gefangenen.  Frank Wedekind - Der Brand von Egliswyl.  Thomas Mann - Tobias Mindernickel.  Arthur Schnitzler - Wohltaten, still und rein gegeben.  Hugo von Hofmannsthal - Ein Brief.  Gustav Meyrink - Der heisse Soldat.  Heinrich Mann - Abdankung.  Marie von Ebner-Eschenbach - Ein Traum im Traume.  Franz Kafka - Automobil und Tricycle.  Else Lasker-Schueler - Das Buch der drei Abigails. - Singa, die Mutter des toten Melechs des Dritten.  Robert Walser - Die Wurst. - Schwendimann. - Helbling.  Franz Kafka - Ein Brudermord. - Ein Besuch im Bergwerk.  Regina Ullmann - Ende und Anfang einder boesen Geschichte.  Hermann Hesse - Der Hollaender.  Alfred Polgar - Die Handschuhe.  Bertold Brecht - Barbara.  Alfred Doeblin - Man bereite sich auf eine baldige Katastrofe vor. - Die Geschichte wird nochmal erzaehlt. - Zum dritten Mahl!
Confessions of St. Augustine - spiritual meditations and divine insights		Augustine, vert. E.B. Pusey	Watkins Publishing - London	2006			472p.
Le ble en herbe		Colette	Garnier-Flammarion - Paris	1969			188p.
"
echo "$tsv" > $file

echo "create schema if not exists $schema;" | psql -qX
echo "
drop table if exists $t ;
create table if not exists $t (
    title             text
  , author_first_name text
  , author            text
  , publisher         text
  , year              text
  , publisher2        text
  , year2             text
  , remark            text
  , id                integer primary key generated by default as identity
);" | psql -qX 

grep -Ev '^#|^$|^\s+' $file | grep -Evi '^quote:|^[{}]|^\s+' \
 | perl -Mstrict -ne 'chomp; my @arr = split(/\t/); while (scalar(@arr) < 8) { push(@arr, undef); } print join("\t",@arr), "\n"' \
 | psql -qXc "copy $t(title, author_first_name, author, publisher, year, publisher2, year2, remark) from stdin with (format csv, delimiter E'\t', header TRUE) ; "

# echo ok
# echo "table $t" | psql


# bug: cursor crashes if debug_assertions is 'on'
# (all is ok with assertions off)

psql -qX -a << SQL
select current_setting('debug_assertions') d_a, version();

begin;
declare myportal cursor for select * from $t order by id;
fetch all in myportal;
close myportal;
end;
SQL

#  output:
#
#  select current_setting('debug_assertions') d_a, version();
#   d_a |                                            version                                             
#  -----+------------------------------------------------------------------------------------------------
#   on  | PostgreSQL 15devel_0327_HEAD_411b on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.2.0, 64-bit
#  (1 row)
#  
#  begin;
#  declare myportal cursor for select * from s.x order by id;
#  fetch all in myportal;
#  server closed the connection unexpectedly
#          This probably means the server terminated abnormally
#          before or while processing the request.
#  connection to server was lost


# logfile:

# TRAP: FailedAssertion("HaveRegisteredOrActiveSnapshot()", File: "toast_internals.c", Line: 670, PID: 19403)
# postgres: 15_HEAD_411b: aardvark testdb [local] FETCH(ExceptionalCondition+0x7b)[0x96749b]
# postgres: 15_HEAD_411b: aardvark testdb [local] FETCH[0x524bfa]
# postgres: 15_HEAD_411b: aardvark testdb [local] FETCH(heap_fetch_toast_slice+0x137)[0x56e817]
# postgres: 15_HEAD_411b: aardvark testdb [local] FETCH[0x51c824]
# postgres: 15_HEAD_411b: aardvark testdb [local] FETCH(detoast_attr+0x18d)[0x51ccfd]
# postgres: 15_HEAD_411b: aardvark testdb [local] FETCH(text_to_cstring+0x12)[0x940822]
# postgres: 15_HEAD_411b: aardvark testdb [local] FETCH(OutputFunctionCall+0x44)[0x970f54]
# postgres: 15_HEAD_411b: aardvark testdb [local] FETCH[0x520fd5]
# postgres: 15_HEAD_411b: aardvark testdb [local] FETCH[0x85171e]
# postgres: 15_HEAD_411b: aardvark testdb [local] FETCH[0x851856]
# postgres: 15_HEAD_411b: aardvark testdb [local] FETCH(PortalRun+0x177)[0x852f87]
# postgres: 15_HEAD_411b: aardvark testdb [local] FETCH[0x84f0c7]
# postgres: 15_HEAD_411b: aardvark testdb [local] FETCH(PostgresMain+0x1652)[0x850af2]
# postgres: 15_HEAD_411b: aardvark testdb [local] FETCH[0x7c0cd3]
# postgres: 15_HEAD_411b: aardvark testdb [local] FETCH(PostmasterMain+0xc61)[0x7c1bf1]
# postgres: 15_HEAD_411b: aardvark testdb [local] FETCH(main+0x44b)[0x50f03b]
# /lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xf1)[0x7ff1d8b842e1]
# postgres: 15_HEAD_411b: aardvark testdb [local] FETCH(_start+0x2a)[0x50f0da]
# 2022-03-27 20:13:02.227 CEST 18819 LOG:  server process (PID 19403) was terminated by signal 6: Aborted
# 2022-03-27 20:13:02.227 CEST 18819 DETAIL:  Failed process was running: fetch all in myportal;
# 2022-03-27 20:13:02.227 CEST 18819 LOG:  terminating any other active server processes
# 2022-03-27 20:13:02.229 CEST 18819 LOG:  all server processes terminated; reinitializing
# 2022-03-27 20:13:02.349 CEST 19407 LOG:  database system was interrupted; last known up at 2022-03-27 20:09:38 CEST
# 2022-03-27 20:13:02.745 CEST 19407 LOG:  database system was not properly shut down; automatic recovery in progress
# 2022-03-27 20:13:02.791 CEST 19407 LOG:  redo starts at 2/5EB01AE8
# 2022-03-27 20:13:02.795 CEST 19407 LOG:  invalid record length at 2/5EB3C4B0: wanted 24, got 0
# 2022-03-27 20:13:02.795 CEST 19407 LOG:  redo done at 2/5EB3C480 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
# 2022-03-27 20:13:02.971 CEST 19408 LOG:  checkpoint starting: end-of-recovery immediate wait
# 2022-03-27 20:13:03.489 CEST 19408 LOG:  checkpoint complete: wrote 48 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.055 s, sync=0.304 s, total=0.567 s; sync files=31, longest=0.283 s, average=0.010 s; distance=234 kB, estimate=234 kB
# 2022-03-27 20:13:03.539 CEST 18819 LOG:  database system is ready to accept connections