Data from the filter and only the filter

[Krysztophe]

Hi,

I think I'm asking something outside of the scope of the tool, but I may have missed something.

When filtering a table with a criteria, I don't want data coming back from other tables (due to foreign keys) because these other tables where independently filled.
I want the other tables with a foreign key towards the filtered table to contain only the data linked to the filter key.
In other words I don't want any data except the one linked to the data in the filter.

The use case if filtering on a geographic criteria, and there are foreign keys up and down the filtered table.

Example with a DB created with pgbench --foreign-keys :

• I want to filter on pgbench_branches.bid = 5

• I want data from pgbench_tellers and pgbench_accounts where the the bid is the same.
  (both have FK towards pgbench_branches).

• And I'd like to add other

• --limit="pgbench_branches = bid=5, * = 0" returns only one line in pgbench_branches, nothing else (this is logical)

• --limit="pgbench_branches = bid=5, * = 100%" returns the whole database

• I tried this, it does not work:

./pg_sample --force --keep --limit="pgbench_branches = bid=5, pgbench_accounts = bid IN (SELECT bid FROM pgbench_branches), pgbench_tellers = bid IN (SELECT bid FROM pgbench_branches), pgbench_history = 0, .* = 0%"  --verbose -p 10001  pgs -f pgs.sample
…
[limit] pgbench_branches = bid=5
[limit] pgbench_accounts = bid IN (SELECT bid FROM pgbench_branches)
[limit] pgbench_tellers = bid IN (SELECT bid FROM pgbench_branches)
[limit] pgbench_history = 0
[limit] .* = 0%
[limit] .* = 100    <---- why? I said 0% just above 
Creating table "_pg_sample"."0001__public_pgbench_accounts" copying cols ["aid", "bid", "abalance", "filler"] 1000000     <------ full table
Creating table "_pg_sample"."0002__public_pgbench_branches" copying cols ["bid", "bbalance", "filler"] 1  <---- ok... for now
Creating table "_pg_sample"."0003__public_pgbench_history" copying cols ["tid", "bid", "aid", "delta", "mtime", "filler"] 0
Creating table "_pg_sample"."0004__public_pgbench_tellers" copying cols ["tid", "bid", "tbalance", "filler"] 100  <------ full table
…
Copying "public"."pgbench_branches" (bid) rows referenced from "_pg_sample"."0004__public_pgbench_tellers" (bid)... 9 rows  <------  other values than bid=5 ..
Exporting data from "_pg_sample"."0001__public_pgbench_accounts" (1000000)
Exporting data from "_pg_sample"."0002__public_pgbench_branches" (10)
Exporting data from "_pg_sample"."0003__public_pgbench_history" (0)
Exporting data from "_pg_sample"."0004__public_pgbench_tellers" (100)

• This works.. but I cannot expand the trick to 2 ou 3 levels of FK above :

./pg_sample --force --keep --limit="pgbench_branches = bid=5, pgbench_accounts = bid IN (SELECT bid FROM pgbench_branches where bid=5), pgbench_tellers = bid IN (SELECT bid FROM pgbench_branches where bid=5), pgbench_history = 0, .* = 0%, 1"  --verbose -p 10001  pgs -f pgs.sample
…
[limit] pgbench_branches = bid=5
[limit] pgbench_accounts = bid IN (SELECT bid FROM pgbench_branches where bid=5)
[limit] pgbench_tellers = bid IN (SELECT bid FROM pgbench_branches where bid=5)
[limit] pgbench_history = 0
[limit] .* = 0%
[limit] .* = 1
[limit] .* = 100
Creating table "_pg_sample"."0001__public_pgbench_accounts" copying cols ["aid", "bid", "abalance", "filler"] 100000 <------- ok
Creating table "_pg_sample"."0002__public_pgbench_branches" copying cols ["bid", "bbalance", "filler"] 1   <------- ok 
Creating table "_pg_sample"."0003__public_pgbench_history" copying cols ["tid", "bid", "aid", "delta", "mtime", "filler"] 0
Creating table "_pg_sample"."0004__public_pgbench_tellers" copying cols ["tid", "bid", "tbalance", "filler"] 10    <------- ok
Copying "public"."pgbench_accounts" (aid) rows referenced from "_pg_sample"."0003__public_pgbench_history" (aid)... 0E0 rows
Copying "public"."pgbench_branches" (bid) rows referenced from "_pg_sample"."0003__public_pgbench_history" (bid)... 0E0 rows
Copying "public"."pgbench_branches" (bid) rows referenced from "_pg_sample"."0004__public_pgbench_tellers" (bid)... 0E0 rows
Copying "public"."pgbench_branches" (bid) rows referenced from "_pg_sample"."0001__public_pgbench_accounts" (bid)... 0E0 rows
Copying "public"."pgbench_tellers" (tid) rows referenced from "_pg_sample"."0003__public_pgbench_history" (tid)... 0E0 rows
Exporting sequences
Exporting data from "_pg_sample"."0001__public_pgbench_accounts" (100000)
Exporting data from "_pg_sample"."0002__public_pgbench_branches" (1)
Exporting data from "_pg_sample"."0003__public_pgbench_history" (0)
Exporting data from "_pg_sample"."0004__public_pgbench_tellers" (10)