Re: Creating files with testdata
On March 10, 2025 3:27:02 PM GMT-04:00, Francisco Olarte wrote: >On Mon, 10 Mar 2025 at 20:18, H wrote: >> There are tables referencing each other using randomly generated IDs, >ie. those IDs are not known until after the parent table row is >inserted. > >Random? Then they CAN collide. ;-> > >Do the IDs need to be random or are they just the default value? > >I've encountered a similar problem with sequences and have bypassed it >by using explicit values into the test data, i.e.m, inserting an >invoice with id 1000 followed by its lines with IDs 100010, 100011, > and then setting start_values for the sequences at . This >helped me a lot in a debugging. You could do the same depending on >what domain your random ids are on. > >Francisco Olarte. K am using UUIDs which the application will use as well.
Re: Creating files with testdata
On March 10, 2025 3:15:10 PM EDT, H wrote: >On March 10, 2025 2:26:48 PM GMT-04:00, Adrian Klaver > wrote: >> >> >>On 3/10/25 11:09 AM, H wrote: >>> I am developing a complex multi-tenant application in postgresql 16 >>in Linux. During the development I would like to be able to enter test >>data into various related tables at any given time for testing >>purposes. >>> >>> While this can certainly be accomplished by predefined CTE INSERT >>statements in an SQL file, I would prefer something more flexible. I >am >>thinking of using a markdown file as a source for these CTE INSERT >>statements. Specifically, having a certain structure in the markdown >>file where a given markdown heading level, bullet level etc. would >>correspond to specific tables and columns. >> >>Why CTE INSERTs? >> >>> >>> After entering my test data into the markdown file for the given >test >>scenario, I would then run an awk script or similar to create a SQL >>file with the various CTE INSERT statements. Howevever, it gets >complex >>since I need to handle 1:N relationships between tables in the >markdown >>file... >>> >>> I hope the above outline is understandable and am interested in >>comments and thoughts on my above approach. >>> >>> Thanks. >>> >>> > >There are tables referencing each other using randomly generated IDs, >ie. those IDs are not known until after the parent table row is >inserted. I thought I should give an example of why I need this. This is /not/ the application but might be a useful example: Imagine a very simple Wikipedia-clone. You have a couple of DB tables for article subjects in a couple of levels, a table for body text and a couple of different tables for references and for images related to the article in question. References and images are not referenced inline in the body text but at the end. Everything is generated manually, ie the body text is written, various references added, and a suitable subject area in different levels chosen, all by an author. I want to be able to write this "article", with the appropriate references, images etc to be done outside this database application. My vision is it's done in a markdown document where a strict use of heading levels, bullet lists etc would correlate with subject areas, body text, references, images and what-nots. A script of some kind that can parse markdown then generates the necessary SQL statements to insert all of this in the appropriate tables, all according to how the information was laid out in the markdown document. My initial thought was to use awk but it's (awk)ward since it is strictly line/paragraph oriented. Pandoc parses markdown very well, generates an AST internally and then the output filter should be able to generate complex SQL statements such as CTEs. Hope that might shed some more light on my thought process.
Re: No. Of wal files generated
On 3/7/25 17:59, Atul Kumar wrote:
Hi,
Please could you help me by sharing any redhat linux command through
which I can count the no. of wal files and no. of ".ready" files
generated in last 10 minutes.
Do you have wal archiving enabled? If yes, then simply go to the archive
dir and suit your self with
find , ls, grep, awk, sort , unic -c , etc .
Find files created in the last 10 mins :
find -cmin 10
to get statistics over time :
***CLOUD*** jboss@sma:/smadb/smadb/pgsql/pitr$ ls -lt | head -10
total 629962796
-rw--- 1 postgres postgres 16777216 Mar 10 09:32
0001472D0078
-rw--- 1 postgres postgres 16777216 Mar 10 09:32
0001472D0077
-rw--- 1 postgres postgres 16777216 Mar 10 09:32
0001472D0076
-rw--- 1 postgres postgres 16777216 Mar 10 09:32
0001472D0075
...
you may use :
ls -lt | awk '{print $6""$7"_"substr($8,1,2) }' | sort | uniq -c | less
to get the distribution over (group by) 1 hour intervals
Regards,
Atul
Re: exclusion constraint question
On 3/8/25 21:01, Rhys A.D. Stewart wrote: Greetings All, I have the following table: CREATE TABLE shelves( shelf_id bigint PRIMARY KEY, l_mug_id bigint UNIQUE, c_mug_id bigint UNIQUE, r_mug_id bigint UNIQUE, CHECK (l_mug_id <> c_mug_id AND l_mug_id <> r_mug_id AND c_mug_id <> r_mug_id), EXCLUDE USING gist (l_mug_id WITH <>, c_mug_id WITH <>, r_mug_id WITH <>) -- Not working as expected (or my expectations are wrong). ); And some data: INSERT INTO shelves VALUES (1,7,2,1); INSERT INTO shelves VALUES (2,3, null, null); INSERT INTO shelves VALUES (3, null,1,4); INSERT INTO shelves VALUES (4,4,5, null); Mugs on shelves, fascinating. A mug_id can only appear once in the entire table. The check constraint handles not having the same mug_id in each row and the unique constraints does the same for the column. But how do I get around checking multiple columns for the same mug_id. I'm thinking an exclusion constraint, but (a) I do not know if I am overthinking it and (b) the exclusion constraint I have does not work as expected, or my expectations are way off. First i'd say look at the exclusion constraint and your unique keys. Those are defined in a mutually-auto-canceling manner. e..g insert mugs 10, 11, and 12 in shelf 3 : insert into shelves(shelf_id,l_mug_id,c_mug_id,r_mug_id) values(3,10,11,12); INSERT 0 1 then insert mugs 13,14,15 in shelf 4 : insert into shelves(shelf_id,l_mug_id,c_mug_id,r_mug_id) values(4,13,14,15); ERROR: conflicting key value violates exclusion constraint "shelves_l_mug_id_c_mug_id_r_mug_id_excl" DETAIL: Key (l_mug_id, c_mug_id, r_mug_id)=(13, 14, 15) conflicts with existing key (l_mug_id, c_mug_id, r_mug_id)=(10, 11, 12). in order to bypass the exclusion constraint one of the mags must match a previous mug in the same position : insert into shelves(shelf_id,l_mug_id,c_mug_id,r_mug_id) values(4,13,14,12); but then : ERROR: duplicate key value violates unique constraint "shelves_r_mug_id_key" DETAIL: Key (r_mug_id)=(12) already exists. So your exclusion constraint does not do what you would expect. You can think of your mugs positions by pulling the shelves buckets in order to make them look like they have a linear shape. Then you would have a unique key on this linear store. What Laurenz said is most probably correct. hmm just thinking , would imagining making 64*3 long integers with all three mug ids side by side ,and using bitwise operations along with exclusion constraints might yield some results ? Any suggestions would be appreciated. Regards, Rhys Peace & Love | Live Long & Prosper
Re: exclusion constraint question
Hey Rhys, I think you might be better served doing something like CREATE TABLE shelve_items( id bigint PRIMARY KEY, shelf_id bigint, column_name VARCHAR, mug_id bigint UNIQUE ) and then putting a unique index on (shelf_id, column_name): CREATE UNIQUE INDEX shelve_items_id_c_name on shelve_items (shelf_id, column_name) The first row of your shelf would then look like: INSERT INTO shelve_items VALUES (1, 1, left, 7) INSERT INTO shelve_items VALUES (2, 1, middle, 2) INSERT INTO shelve_items VALUES (3, 1, right, 1) This would also allow you to scale to shelves that are n items wide (maybe at that point you could use an id for the columns instead of names) Does this work for you? Best, Emanuel > On 8 Mar 2025, at 20:01, Rhys A.D. Stewart wrote: > > Greetings All, > > I have the following table: > > CREATE TABLE shelves( >shelf_id bigint PRIMARY KEY, >l_mug_id bigint UNIQUE, >c_mug_id bigint UNIQUE, >r_mug_id bigint UNIQUE, >CHECK (l_mug_id <> c_mug_id AND l_mug_id <> r_mug_id AND c_mug_id > <> r_mug_id), >EXCLUDE USING gist (l_mug_id WITH <>, c_mug_id WITH <>, r_mug_id > WITH <>) -- Not working as expected (or my expectations are wrong). > ); > > And some data: > > INSERT INTO shelves VALUES (1,7,2,1); > INSERT INTO shelves VALUES (2,3, null, null); > INSERT INTO shelves VALUES (3, null,1,4); > INSERT INTO shelves VALUES (4,4,5, null); > > Mugs on shelves, fascinating. A mug_id can only appear once in the > entire table. The check constraint handles not having the same mug_id > in each row and the unique constraints does the same for the column. > But how do I get around checking multiple columns for the same mug_id. > I'm thinking an exclusion constraint, but (a) I do not know if I am > overthinking it and (b) the exclusion constraint I have does not work > as expected, or my expectations are way off. > > Any suggestions would be appreciated. > > Regards, > > Rhys > Peace & Love | Live Long & Prosper > >
Re: exclusion constraint question
On 3/10/25 10:43, Achilleas Mantzios - cloud wrote: On 3/8/25 21:01, Rhys A.D. Stewart wrote: Greetings All, I have the following table: CREATE TABLE shelves( shelf_id bigint PRIMARY KEY, l_mug_id bigint UNIQUE, c_mug_id bigint UNIQUE, r_mug_id bigint UNIQUE, CHECK (l_mug_id <> c_mug_id AND l_mug_id <> r_mug_id AND c_mug_id <> r_mug_id), EXCLUDE USING gist (l_mug_id WITH <>, c_mug_id WITH <>, r_mug_id WITH <>) -- Not working as expected (or my expectations are wrong). ); And some data: INSERT INTO shelves VALUES (1,7,2,1); INSERT INTO shelves VALUES (2,3, null, null); INSERT INTO shelves VALUES (3, null,1,4); INSERT INTO shelves VALUES (4,4,5, null); Mugs on shelves, fascinating. A mug_id can only appear once in the entire table. The check constraint handles not having the same mug_id in each row and the unique constraints does the same for the column. But how do I get around checking multiple columns for the same mug_id. I'm thinking an exclusion constraint, but (a) I do not know if I am overthinking it and (b) the exclusion constraint I have does not work as expected, or my expectations are way off. First i'd say look at the exclusion constraint and your unique keys. Those are defined in a mutually-auto-canceling manner. e..g insert mugs 10, 11, and 12 in shelf 3 : insert into shelves(shelf_id,l_mug_id,c_mug_id,r_mug_id) values(3,10,11,12); INSERT 0 1 then insert mugs 13,14,15 in shelf 4 : insert into shelves(shelf_id,l_mug_id,c_mug_id,r_mug_id) values(4,13,14,15); ERROR: conflicting key value violates exclusion constraint "shelves_l_mug_id_c_mug_id_r_mug_id_excl" DETAIL: Key (l_mug_id, c_mug_id, r_mug_id)=(13, 14, 15) conflicts with existing key (l_mug_id, c_mug_id, r_mug_id)=(10, 11, 12). in order to bypass the exclusion constraint one of the mags must match a previous mug in the same position : insert into shelves(shelf_id,l_mug_id,c_mug_id,r_mug_id) values(4,13,14,12); but then : ERROR: duplicate key value violates unique constraint "shelves_r_mug_id_key" DETAIL: Key (r_mug_id)=(12) already exists. So your exclusion constraint does not do what you would expect. You can think of your mugs positions by pulling the shelves buckets in order to make them look like they have a linear shape. Then you would have a unique key on this linear store. What Laurenz said is most probably correct. hmm just thinking , would imagining making 64*3 long integers with all three mug ids side by side ,and using bitwise operations along with exclusion constraints might yield some results ? I think I got it : ALTER TABLE shelves ADD CONSTRAINT shelves_excl EXCLUDE USING gist( array_remove((ARRAY[l_mug_id,c_mug_id,r_mug_id]::int[]),NULL) WITH && ); but the caveat is I had to cast to 32-bit int array. Haven't found any opclass for int8 (bigint) in intarray extension. The idea is make sure that there is no overlap between elements in every two different rows. Any suggestions would be appreciated. Regards, Rhys Peace & Love | Live Long & Prosper
Re: Creating files with testdata
On March 10, 2025 3:22:41 PM GMT-04:00, Francisco Olarte
wrote:
>On Mon, 10 Mar 2025 at 19:17, H wrote:
>...
>> After entering my test data into the markdown file for the given test
>scenario, I would then run an awk script or similar to create a SQL
>file with the various CTE INSERT statements. Howevever, it gets complex
>since I need to handle 1:N relationships between tables in the markdown
>file...
>> I hope the above outline is understandable and am interested in
>comments and thoughts on my above approach.
>
>I do not think MD would be a good source format. Being a developer I
>would recommend taking your favorite scripting language ( perl /
>python / lua / js , whatever ), build a big object ( which can be
>easily helped by some mini-functions to build a little DSL ) and then
>spit sql from it ( for developemewnt it is normally better then using
>whatever db connection your language has, as it leaves an
>understandable sql script ).
>
>I have done this with perl for some projects, built a driver which
>defined several helper functions, then dofile("xx.dat") which returned
>a big hash and then a series of loops on the result to write the SQL
>in whatever order was neccessary.
>
>Francisco Olarte.
The plan is to use the same format for later adding information into the
finished application. By the way, all information is text, either paragraphs,
sentences or single words.
Re: Creating files with testdata
On March 10, 2025 3:21:57 PM GMT-04:00, "David G. Johnston" wrote: >On Mon, Mar 10, 2025 at 12:17 PM H wrote: > >> There are tables referencing each other using randomly generated IDs, >ie. >> those IDs are not known until after the parent table row is inserted. >> > >I just reserve half of the number space of bigint, the negatives, for >test >data and assign known IDs as part of the test data specification. > >David J. The finished application will also use UUIDs which is why am using it now.
Re: exclusion constraint question
Greetings, > I think I got it : > > ALTER TABLE shelves ADD CONSTRAINT shelves_excl EXCLUDE USING gist( > array_remove((ARRAY[l_mug_id,c_mug_id,r_mug_id]::int[]),NULL) WITH && ); > > but the caveat is I had to cast to 32-bit int array. Haven't found any > opclass for int8 (bigint) in intarray extension. I tried this and got the opclass error for the int8 and (since postgres is so wonderfully extensible) considered trying to write the oppclass for bigint. But ultimately writing the trigger as suggested by Laurenz is much easier. So that is the route I went, Thanks all for your input. Rhys Peace & Love | Live Long & Prosper
