Re: SQL group by help
> On 11 Dec 2020, at 18:24, Chris Stephens wrote: > > I'm trying to create a visual representation of a 6x8 grid of samples on a > rack using the following SQL format: > > with rack_display as ( > select sr.ts rack_ts > , sr.rack_id > , r.rack_barcode > , 1 as row_pos > , max(case when rack_well = 0 then 'A1: '||sample_barcode end) as col1 > , max(case when rack_well = 1 then 'A2: '||sample_barcode end) as col2 > , max(case when rack_well = 2 then 'A3: '||sample_barcode end) as col3 > , max(case when rack_well = 3 then 'A4: '||sample_barcode end) as col4 > , max(case when rack_well = 4 then 'A5: '||sample_barcode end) as col5 > , max(case when rack_well = 5 then 'A6: '||sample_barcode end) as col6 > from rack r > , sample_rack sr > , sample s > where r.rack_id = sr.rack_id >and sr.sample_id = s.sample_id >and sr.rack_well < 6 > group by sr.ts, sr.rack_id, r.rack_barcode, row_pos > union all > select sr.ts rack_ts > , sr.rack_id > , r.rack_barcode > , 2 as row_pos > , max(case when rack_well = 6 then 'B1: '||sample_barcode end) as col1 > , max(case when rack_well = 7 then 'B2: '||sample_barcode end) as col2 > , max(case when rack_well = 8 then 'B3: '||sample_barcode end) as col3 > , max(case when rack_well = 9 then 'B4: '||sample_barcode end) as col4 > , max(case when rack_well = 10 then 'B5: '||sample_barcode end) as > col5 > , max(case when rack_well = 11 then 'B6: '||sample_barcode end) as > col6 > from rack r > , sample_rack sr > , sample s > where r.rack_id = sr.rack_id >and sr.sample_id = s.sample_id >and sr.rack_well >= 6 >and sr.rack_well < 12 > group by sr.ts, sr.rack_id, r.rack_barcode, row_pos > union all > ... > ) > select * from rack_display order by rack_ts, rack_id, row_pos; > > the "union all"s continue for another 6 blocks. reports would filter on > rack_id and timestamp. Is time really what groups these batches? I would double-check whether you may be omitting to store some data relevant to this process. > if timestamps for each load of a rack were guaranteed to be the same, this > would work. however, the "sr.ts" values may vary by a few seconds so there is > potential for the "group by" to break. ts differences will be a minimum of 5 > minutes for each distinct load of a rack. > > what i think i need is to manufacture a group by column based off rows in > "sample_rack" that have "ts" values that are < 1 minute from each other and > rack_id is the same. i'm coming up blank on how to accomplish that though. > my first thought was to create an interval of +/- 1 min then find all rows > that overlap and assign a group number but i'm not sure how to accomplish > that. You could date_trunc those timestamps to the minute and group on that. > there's also no guarantee an entire rack is full of samples so some "cells" > of display might be null. i think that makes the use of tablefunc crosstab a > little harder. if i remember correctly, it does not handle missing values > well. i'm open to any pivoting strategy. Many reporting tools have features to support just that. We use WebFOCUS, which calls those ACROSS columns. It’s a common requirement in reporting. Alban Hertroys -- There is always an exception to always.
pg_restore fails when psql succeeds
I am facing a consistent issue with pg_restore when moving databases with large tables from PostgreSQL 10 to 13. pg_restore fails to restore indexes on some large tables (anything over 20 million records). pg_restore: error: could not execute query: ERROR: out of memory DETAIL: Failed on request of size 214728704 in memory context "TupleSort sort". CONTEXT: parallel worker Command was: CREATE INDEX some_index_idx ON schema1.table1 USING btree (some_field); This happens when a database is exported with formats "custom" or "directory". No errors occur when the same databases are exported as plain text and imported with psql. Initially I was importing with --jobs in several threads, but reducing threads to 1 made no difference. I tried exporting with pg_dump versions 13 and 10. It made no difference either - restore succeeds with plain text + psql and fails with the other formats + pg_restore. The same doesn't happen when I import from 10 into 12. I am a bit lost and concerned at this point about moving on with conversion to version 13. Any guidance would be greatly appreciated!
Re: pg_restore fails when psql succeeds
On 12/12/20 12:10 PM, Cherio wrote: I am facing a consistent issue with pg_restore when moving databases with large tables from PostgreSQL 10 to 13. pg_restore fails to restore indexes on some large tables (anything over 20 million records). pg_restore: error: could not execute query: ERROR: out of memory DETAIL: Failed on request of size 214728704 in memory context "TupleSort sort". CONTEXT: parallel worker Command was: CREATE INDEX some_index_idx ON schema1.table1 USING btree (some_field); This happens when a database is exported with formats "custom" or "directory". No errors occur when the same databases are exported as plain text and imported with psql. Initially I was importing with --jobs in several threads, but reducing threads to 1 made no difference. I tried exporting with pg_dump versions 13 and 10. It made no difference either - restore succeeds with plain text + psql and fails with the other formats + pg_restore. The same doesn't happen when I import from 10 into 12. I am a bit lost and concerned at this point about moving on with conversion to version 13. Any guidance would be greatly appreciated! Exact Postgres 13 version? Hardware specifications for machine? Changes in this section(https://www.postgresql.org/docs/13/runtime-config-resource.html) of postgresql.conf? Relevant information from system logs? -- Adrian Klaver adrian.kla...@aklaver.com
Re: pg_restore fails when psql succeeds
I install PostgreSQL from "apt.postgresql.org" repository: $ /usr/lib/postgresql/13/bin/postgres --version postgres (PostgreSQL) 13.1 (Ubuntu 13.1-1.pgdg20.04+1) It runs in a VM, the most recent & updated version of Ubuntu LTS 20.04 $ uname -a Linux dbwfprod8-20 5.4.0-56-generic #62-Ubuntu SMP Mon Nov 23 19:20:19 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux RAM: 48GB CPU: 8 Storage: 800GB (plenty of free space left) I attached customizations to postgresql.conf and a few relevant errors from the PostgreSQL server log; there were no relevant messages in journalctl. On Sat, Dec 12, 2020 at 3:18 PM Adrian Klaver wrote: > On 12/12/20 12:10 PM, Cherio wrote: > > I am facing a consistent issue with pg_restore when moving databases > > with large tables from PostgreSQL 10 to 13. pg_restore fails to restore > > indexes on some large tables (anything over 20 million records). > > > > pg_restore: error: could not execute query: ERROR: out of memory > > DETAIL: Failed on request of size 214728704 in memory context > > "TupleSort sort". > > CONTEXT: parallel worker > > Command was: CREATE INDEX some_index_idx ON schema1.table1 USING btree > > (some_field); > > > > This happens when a database is exported with formats "custom" or > > "directory". No errors occur when the same databases are exported as > > plain text and imported with psql. > > > > Initially I was importing with --jobs in several threads, but reducing > > threads to 1 made no difference. I tried exporting with pg_dump versions > > 13 and 10. It made no difference either - restore succeeds with plain > > text + psql and fails with the other formats + pg_restore. > > > > The same doesn't happen when I import from 10 into 12. I am a bit lost > > and concerned at this point about moving on with conversion to version > 13. > > > > Any guidance would be greatly appreciated! > > Exact Postgres 13 version? > > Hardware specifications for machine? > > Changes in this > section(https://www.postgresql.org/docs/13/runtime-config-resource.html) > of postgresql.conf? > > Relevant information from system logs? > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > var-log-postgresql-postgresql-13-main.log Description: Binary data postgresql-44G.conf Description: Binary data
Is there a way to dump schema to files on disk and keep them in sync
I want to dump my postgres schema to disk in neat directories like pgadmin presents. Then I want to be able to edit the files and sync changes to the database and ideally if changes were made in the database to sync them back to the disk. Is there a tool that does this? Is there a tool that will dump the schema into separate directories and files like pgadmin does? Thanks.
