pdf viewer for asp.net web application : Add photo to pdf in preview Library software component asp.net winforms html mvc postgresql-9.4-A4205-part2841

Chapter 60. BKI Backend Interface
BackendInterface(BKI)filesarescriptsinaspeciallanguagethatisunderstoodbythePostgreSQL
backendwhenrunninginthe“bootstrap”mode.Thebootstrapmodeallowssystemcatalogstobe
createdandfilledfromscratch,whereasordinarySQLcommandsrequirethecatalogstoexistalready.
BKIfilescanthereforebeusedtocreatethedatabasesysteminthefirstplace.(Andtheyareprobably
notusefulforanythingelse.)
initdbusesaBKIfiletodopartofitsjobwhencreatinganewdatabasecluster.Theinputfileused
byinitdbiscreatedaspartofbuildingandinstallingPostgreSQLbyaprogramnamed
genbki.pl
,
whichreadssomespeciallyformattedCheaderfilesinthe
src/include/catalog/
directoryof
thesourcetree.ThecreatedBKIfileiscalled
postgres.bki
andisnormallyinstalledinthe
share
subdirectoryoftheinstallationtree.
Relatedinformationcanbefoundinthedocumentationforinitdb.
60.1. BKI File Format
ThissectiondescribeshowthePostgreSQLbackendinterpretsBKIfiles.Thisdescriptionwillbe
easiertounderstandifthe
postgres.bki
fileisathandasanexample.
BKIinputconsistsofasequenceofcommands. Commandsaremadeupofanumberoftokens,
dependingonthesyntaxofthecommand.Tokensareusuallyseparatedbywhitespace,butneednot
beifthereisnoambiguity.Thereisnospecialcommandseparator;thenexttokenthatsyntactically
cannotbelongtotheprecedingcommandstartsanewone.(Usuallyyouwouldputanewcommand
onanewline,forclarity.)Tokenscanbecertainkeywords,specialcharacters(parentheses,commas,
etc.),numbers,ordouble-quotedstrings.Everythingiscasesensitive.
Linesstartingwith
#
areignored.
60.2. BKI Commands
create
tablename
tableoid
[
bootstrap
] [
shared_relation
] [
without_oids
]
[
rowtype_oidoid
](
name1
=
type1
[,
name2
=
type2
,...])
Createatablenamed
tablename
,andhavingtheOID
tableoid
,withthecolumnsgivenin
parentheses.
Thefollowing columntypes aresupported directly by
bootstrap.c
:
bool
,
bytea
,
char
(1 byte),
name
,
int2
,
int4
,
regproc
,
regclass
,
regtype
,
text
,
oid
,
tid
,
xid
,
cid
,
int2vector
,
oidvector
,
_int4
(array),
_text
(array),
_oid
(array),
_char
(array),
_aclitem
(array).Althoughitispossibletocreatetablescontainingcolumnsofothertypes,
thiscannotbedoneuntilafter
pg_type
hasbeencreatedandfilledwithappropriateentries.
(Thateffectivelymeansthatonlythesecolumntypescanbeusedinbootstrappedtables,but
non-bootstrapcatalogscancontainanybuilt-intype.)
When
bootstrap
isspecified,thetablewillonlybecreatedondisk;nothingisenteredinto
pg_class
,
pg_attribute
,etc,forit.ThusthetablewillnotbeaccessiblebyordinarySQL
1978
Add photo to pdf in preview - insert images into PDF in C#.net, ASP.NET, MVC, Ajax, WinForms, WPF
Sample C# code to add image, picture, logo or digital photo into PDF document page using PDF page editor control
add photo to pdf reader; adding jpg to pdf
Add photo to pdf in preview - VB.NET PDF insert image library: insert images into PDF in vb.net, ASP.NET, MVC, Ajax, WinForms, WPF
Guide VB.NET Programmers How to Add Images in PDF Document
add jpg to pdf document; how to add image to pdf document
Chapter60.BKIBackendInterface
operationsuntilsuchentriesaremadethehardway(with
insert
commands).Thisoptionis
usedforcreating
pg_class
etcthemselves.
Thetable is createdas sharedif
shared_relation
is specified. ItwillhaveOIDs unless
without_oids
isspecified.Thetable’srowtypeOID(
pg_type
OID)canoptionallybespeci-
fiedviathe
rowtype_oid
clause;ifnotspecified,anOIDisautomaticallygeneratedforit.(The
rowtype_oid
clauseisuselessif
bootstrap
isspecified,butitcanbeprovidedanywayfor
documentation.)
opentablename
Openthetablenamed
tablename
forinsertionofdata.Anycurrentlyopentableisclosed.
close
[
tablename
]
Closetheopentable.Thenameofthetablecanbegivenasacross-check,butthisisnotrequired.
insert
[
OID =oid_value
]
(value1value2
...
)
Insertanewrowintotheopentableusing
value1
,
value2
,etc.,foritscolumnvaluesand
oid_value
foritsOID.If
oid_value
iszero(0)ortheclauseisomitted,andthetablehas
OIDs,thenthenextavailableOIDisassigned.
NULLvaluescanbespecifiedusingthespecialkeyword
_null_
.Valuescontainingspaces
mustbedoublequoted.
declare
[
unique
]
indexindexnameindexoid on tablename using amname( opclass1
name1
[,...]
)
Createanindexnamed
indexname
,havingOID
indexoid
,onthetablenamed
tablename
,
usingthe
amname
accessmethod.Thefieldstoindexarecalled
name1
,
name2
etc., andthe
operatorclassestouseare
opclass1
,
opclass2
etc., respectively.Theindexfileiscreated
andappropriatecatalogentriesaremadeforit,buttheindexcontentsarenotinitializedbythis
command.
declare toasttoasttableoidtoastindexoidontablename
CreateaTOASTtableforthetable named
tablename
.TheTOASTtableis assignedOID
toasttableoid
anditsindexisassignedOID
toastindexoid
.Aswith
declare index
,
fillingoftheindexispostponed.
build indices
Fillintheindicesthathavepreviouslybeendeclared.
60.3. Structure of the Bootstrap BKI File
The
open
commandcannotbeuseduntilthetablesitusesexistandhaveentriesforthetablethatis
tobeopened.(Theseminimumtablesare
pg_class
,
pg_attribute
,
pg_proc
,and
pg_type
.)To
allowthosetablesthemselvestobefilled,
create
withthe
bootstrap
optionimplicitlyopensthe
createdtablefordatainsertion.
Also,the
declare index
and
declare toast
commandscannotbeuseduntilthesystemcatalogs
theyneedhavebeencreatedandfilledin.
Thus,thestructureofthe
postgres.bki
filehastobe:
1.
create bootstrap
oneofthecriticaltables
2.
insert
datadescribingatleastthecriticaltables
1979
C# PDF remove image library: remove, delete images from PDF in C#.
Support removing vector image, graphic picture, digital photo, scanned signature, logo, etc. Remove Image from PDF Page Using C#. Add necessary references:
adding a png to a pdf; add image field to pdf form
VB.NET PDF remove image library: remove, delete images from PDF in
Support removing vector image, graphic picture, digital photo, scanned signature, logo, etc. VB.NET: Remove Image from PDF Page. Add necessary references:
add jpg to pdf preview; add picture to pdf form
Chapter60.BKIBackendInterface
3.
close
4.Repeatfortheothercriticaltables.
5.
create
(without
bootstrap
)anoncriticaltable
6.
open
7.
insert
desireddata
8.
close
9.Repeatfortheothernoncriticaltables.
10.Defineindexesandtoasttables.
11.
build indices
Therearedoubtlessother,undocumentedorderingdependencies.
60.4. Example
Thefollowingsequenceofcommandswillcreatethetable
test_table
withOID420,havingtwo
columns
cola
and
colb
oftype
int4
and
text
,respectively,andinserttworowsintothetable:
create test_table 420 (cola = int4, colb = text)
open test_table
insert OID=421 ( 1 "value1" )
insert OID=422 ( 2 _null_ )
close test_table
1980
VB.NET PDF copy, paste image library: copy, paste, cut PDF images
to copy, paste and cut vector image, graphic picture, digital photo, scanned signature VB.NET DLLs: Copy, Paste, Cut Image in PDF Page. Add necessary references:
add an image to a pdf acrobat; add picture to pdf reader
Chapter 61. How the Planner Uses Statistics
ThischapterbuildsonthematerialcoveredinSection14.1andSection14.2toshowsomeadditional
detailsabouthowtheplannerusesthesystemstatisticstoestimatethenumberofrowseachpartof
aquerymightreturn.Thisisasignificantpartoftheplanningprocess,providingmuchoftheraw
materialforcostcalculation.
Theintentofthischapterisnottodocumentthecodeindetail,buttopresentanoverviewofhowit
works.Thiswillperhapseasethelearningcurveforsomeonewhosubsequentlywishestoreadthe
code.
61.1. Row Estimation Examples
TheexamplesshownbelowusetablesinthePostgreSQLregressiontestdatabase.Theoutputsshown
aretakenfromversion8.3.Thebehaviorofearlier(orlater)versionsmightvary.Notealsothatsince
ANALYZE
usesrandomsamplingwhileproducingstatistics,theresultswillchangeslightlyafterany
new
ANALYZE
.
Let’sstartwithaverysimplequery:
EXPLAIN SELECT
*
FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1
(cost=0.00..458.00 rows=10000 width=244)
Howtheplannerdeterminesthecardinalityof
tenk1
iscoveredinSection14.2,butisrepeatedhere
forcompleteness.Thenumberofpagesandrowsislookedupin
pg_class
:
SELECT relpages, reltuples FROM pg_class WHERE relname = ’tenk1’;
relpages | reltuples
----------+-----------
358 |
10000
Thesenumbersarecurrentasofthelast
VACUUM
or
ANALYZE
onthetable.Theplannerthenfetches
theactualcurrentnumberofpagesinthetable(thisisacheapoperation,notrequiringatablescan).If
thatisdifferentfrom
relpages
then
reltuples
isscaledaccordinglytoarriveatacurrentnumber-
of-rowsestimate.Intheexampleabove,thevalueof
relpages
isup-to-datesotherowsestimateis
thesameas
reltuples
.
Let’smoveontoanexamplewitharangeconditioninits
WHERE
clause:
EXPLAIN SELECT
*
FROM tenk1 WHERE unique1 < 1000;
QUERY PLAN
--------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1
(cost=24.06..394.64 rows=1007 width=244)
Recheck Cond: (unique1 < 1000)
->
Bitmap Index Scan on tenk1_unique1
(cost=0.00..23.80 rows=1007 width=0)
Index Cond: (unique1 < 1000)
1981
Chapter61.HowthePlannerUsesStatistics
The planner examines the
WHERE
clauseconditionandlooks uptheselectivityfunction forthe
operator
<
in
pg_operator
. This is heldin the column
oprrest
, andthe entryin this case
is
scalarltsel
. The
scalarltsel
function retrieves the histogram for
unique1
from
pg_statistics
.Formanualqueriesitismoreconvenienttolookinthesimpler
pg_stats
view:
SELECT histogram_bounds FROM pg_stats
WHERE tablename=’tenk1’ AND attname=’unique1’;
histogram_bounds
------------------------------------------------------
{0,993,1997,3050,4040,5036,5957,7057,8029,9016,9995}
Nextthefractionofthehistogramoccupiedby“<1000”isworkedout.Thisistheselectivity.The
histogramdividestherangeintoequalfrequencybuckets,soallwehavetodoislocatethebucketthat
ourvalueisinandcountpartofitandalloftheonesbefore.Thevalue1000isclearlyinthesecond
bucket(993-1997).Assumingalineardistributionofvaluesinsideeachbucket,wecancalculatethe
selectivityas:
selectivity = (1 + (1000 - bucket[2].min)/(bucket[2].max - bucket[2].min))/num_buckets
= (1 + (1000 - 993)/(1997 - 993))/10
= 0.100697
thatis,onewholebucketplusalinearfractionofthesecond,dividedbythenumberofbuckets.The
estimatednumberofrowscannowbecalculatedastheproductoftheselectivityandthecardinality
of
tenk1
:
rows = rel_cardinality
*
selectivity
= 10000
*
0.100697
= 1007
(rounding off)
Nextlet’sconsideranexamplewithanequalityconditioninits
WHERE
clause:
EXPLAIN SELECT
*
FROM tenk1 WHERE stringu1 = ’CRAAAA’;
QUERY PLAN
----------------------------------------------------------
Seq Scan on tenk1
(cost=0.00..483.00 rows=30 width=244)
Filter: (stringu1 = ’CRAAAA’::name)
Againtheplannerexaminesthe
WHERE
clauseconditionandlooksuptheselectivityfunctionfor
=
,
whichis
eqsel
.Forequalityestimationthehistogramisnotuseful;insteadthelistofmostcom-
monvalues(MCVs)isusedtodeterminetheselectivity.Let’shavealookattheMCVs,withsome
additionalcolumnsthatwillbeusefullater:
SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM pg_stats
WHERE tablename=’tenk1’ AND attname=’stringu1’;
null_frac
| 0
n_distinct
| 676
most_common_vals
| {EJAAAA,BBAAAA,CRAAAA,FCAAAA,FEAAAA,GSAAAA,JOAAAA,MCAAAA,NAAAAA,WGAAAA}
most_common_freqs | {0.00333333,0.003,0.003,0.003,0.003,0.003,0.003,0.003,0.003,0.003}
Since
CRAAAA
appearsinthelistofMCVs,theselectivityismerelythecorrespondingentryinthelist
ofmostcommonfrequencies(MCFs):
1982
Chapter61.HowthePlannerUsesStatistics
selectivity = mcf[3]
= 0.003
Asbefore,theestimatednumberofrowsisjusttheproductofthiswiththecardinalityof
tenk1
:
rows = 10000
*
0.003
= 30
Nowconsiderthesamequery,butwithaconstantthatisnotintheMCVlist:
EXPLAIN SELECT
*
FROM tenk1 WHERE stringu1 = ’xxx’;
QUERY PLAN
----------------------------------------------------------
Seq Scan on tenk1
(cost=0.00..483.00 rows=15 width=244)
Filter: (stringu1 = ’xxx’::name)
Thisisquiteadifferentproblem:howtoestimatetheselectivitywhenthevalueisnotintheMCV
list.Theapproachistousethefactthatthevalueisnotinthelist,combinedwiththeknowledgeof
thefrequenciesforalloftheMCVs:
selectivity = (1 - sum(mvf))/(num_distinct - num_mcv)
= (1 - (0.00333333 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003 +
0.003 + 0.003 + 0.003 + 0.003))/(676 - 10)
= 0.0014559
Thatis, addupallthefrequenciesfortheMCVsandsubtractthemfromone,thendividebythe
numberofotherdistinctvalues.Thisamountstoassumingthatthefractionofthecolumnthatisnot
anyoftheMCVsisevenlydistributedamongalltheotherdistinctvalues.Noticethatthereareno
nullvaluessowedon’thavetoworryaboutthose(otherwisewe’dsubtractthenullfractionfromthe
numeratoraswell).Theestimatednumberofrowsisthencalculatedasusual:
rows = 10000
*
0.0014559
= 15
(rounding off)
Thepreviousexamplewith
unique1 < 1000
wasanoversimplificationofwhat
scalarltsel
re-
allydoes;nowthatwehaveseenanexampleoftheuseofMCVs,wecanfillinsomemoredetail.
Theexamplewascorrectasfarasitwent,becausesince
unique1
isauniquecolumnithasnoMCVs
(obviously,novalueisanymorecommonthananyothervalue).Foranon-uniquecolumn,therewill
normallybebothahistogramandanMCVlist,andthehistogramdoesnotincludetheportionofthe
columnpopulationrepresentedbytheMCVs.Wedothingsthiswaybecauseitallowsmoreprecise
estimation.Inthissituation
scalarltsel
directlyappliesthecondition(e.g.,“<1000”)toeach
valueoftheMCVlist,andaddsupthefrequenciesoftheMCVsforwhichtheconditionistrue.This
givesanexactestimateoftheselectivitywithintheportionofthetablethatisMCVs.Thehistogram
isthenusedinthesamewayasabovetoestimatetheselectivityintheportionofthetablethatis
notMCVs,andthenthetwonumbersarecombinedtoestimatetheoverallselectivity.Forexample,
consider
EXPLAIN SELECT
*
FROM tenk1 WHERE stringu1 < ’IAAAAA’;
QUERY PLAN
------------------------------------------------------------
Seq Scan on tenk1
(cost=0.00..483.00 rows=3077 width=244)
1983
Chapter61.HowthePlannerUsesStatistics
Filter: (stringu1 < ’IAAAAA’::name)
WealreadysawtheMCVinformationfor
stringu1
,andhereisitshistogram:
SELECT histogram_bounds FROM pg_stats
WHERE tablename=’tenk1’ AND attname=’stringu1’;
histogram_bounds
--------------------------------------------------------------------------------
{AAAAAA,CQAAAA,FRAAAA,IBAAAA,KRAAAA,NFAAAA,PSAAAA,SGAAAA,VAAAAA,XLAAAA,ZZAAAA}
CheckingtheMCVlist,wefindthatthecondition
stringu1 < ’IAAAAA’
issatisfiedbythefirst
sixentriesandnotthelastfour,sotheselectivitywithintheMCVpartofthepopulationis
selectivity = sum(relevant mvfs)
= 0.00333333 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003
= 0.01833333
SummingalltheMCFsalsotellsusthatthetotalfractionofthepopulationrepresentedbyMCVs
is0.03033333,andthereforethefractionrepresentedbythehistogramis0.96966667(again,there
arenonulls,elsewe’dhavetoexcludethemhere).Wecanseethatthevalue
IAAAAA
fallsnearly
attheendofthethirdhistogrambucket.Usingsomerathercheesyassumptionsaboutthefrequency
ofdifferentcharacters,theplannerarrivesattheestimate0.298387fortheportionofthehistogram
populationthatisless than
IAAAAA
. WethencombinetheestimatesfortheMCVandnon-MCV
populations:
selectivity = mcv_selectivity + histogram_selectivity
*
histogram_fraction
= 0.01833333 + 0.298387
*
0.96966667
= 0.307669
rows
= 10000
*
0.307669
= 3077
(rounding off)
Inthisparticularexample,thecorrectionfromtheMCVlistisfairlysmall,becausethecolumndistri-
butionisactuallyquiteflat(thestatisticsshowingtheseparticularvaluesasbeingmorecommonthan
othersaremostlyduetosamplingerror).Inamoretypicalcasewheresomevaluesaresignificantly
morecommonthanothers,thiscomplicatedprocessgivesausefulimprovementinaccuracybecause
theselectivityforthemostcommonvaluesisfoundexactly.
Nowlet’sconsideracasewithmorethanoneconditioninthe
WHERE
clause:
EXPLAIN SELECT
*
FROM tenk1 WHERE unique1 < 1000 AND stringu1 = ’xxx’;
QUERY PLAN
--------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1
(cost=23.80..396.91 rows=1 width=244)
Recheck Cond: (unique1 < 1000)
Filter: (stringu1 = ’xxx’::name)
->
Bitmap Index Scan on tenk1_unique1
(cost=0.00..23.80 rows=1007 width=0)
Index Cond: (unique1 < 1000)
Theplannerassumesthatthetwoconditionsareindependent,sothattheindividualselectivitiesofthe
clausescanbemultipliedtogether:
selectivity = selectivity(unique1 < 1000)
*
selectivity(stringu1 = ’xxx’)
= 0.100697
*
0.0014559
= 0.0001466
1984
Chapter61.HowthePlannerUsesStatistics
rows
= 10000
*
0.0001466
= 1
(rounding off)
Noticethatthenumberofrowsestimatedtobereturnedfromthebitmapindexscanreflectsonlythe
conditionusedwiththeindex;thisisimportantsinceitaffectsthecostestimateforthesubsequent
heapfetches.
Finallywewillexamineaquerythatinvolvesajoin:
EXPLAIN SELECT
*
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop
(cost=4.64..456.23 rows=50 width=488)
->
Bitmap Heap Scan on tenk1 t1
(cost=4.64..142.17 rows=50 width=244)
Recheck Cond: (unique1 < 50)
->
Bitmap Index Scan on tenk1_unique1
(cost=0.00..4.63 rows=50 width=0)
Index Cond: (unique1 < 50)
->
Index Scan using tenk2_unique2 on tenk2 t2
(cost=0.00..6.27 rows=1 width=244)
Index Cond: (unique2 = t1.unique2)
Therestrictionon
tenk1
,
unique1 < 50
,isevaluatedbeforethenested-loopjoin.Thisishandled
analogouslytothepreviousrangeexample.Thistimethevalue50fallsintothefirstbucketofthe
unique1
histogram:
selectivity = (0 + (50 - bucket[1].min)/(bucket[1].max - bucket[1].min))/num_buckets
= (0 + (50 - 0)/(993 - 0))/10
= 0.005035
rows
= 10000
*
0.005035
= 50
(rounding off)
Therestrictionforthejoinis
t2.unique2 = t1.unique2
.Theoperatorisjustourfamiliar
=
,
howevertheselectivityfunctionisobtainedfromthe
oprjoin
columnof
pg_operator
, andis
eqjoinsel
.
eqjoinsel
looksupthestatisticalinformationforboth
tenk2
and
tenk1
:
SELECT tablename, null_frac,n_distinct, most_common_vals FROM pg_stats
WHERE tablename IN (’tenk1’, ’tenk2’) AND attname=’unique2’;
tablename
| null_frac | n_distinct | most_common_vals
-----------+-----------+------------+------------------
tenk1
|
0 |
-1 |
tenk2
|
0 |
-1 |
InthiscasethereisnoMCVinformationfor
unique2
becauseallthevaluesappeartobeunique,so
weuseanalgorithmthatreliesonlyonthenumberofdistinctvaluesforbothrelationstogetherwith
theirnullfractions:
selectivity = (1 - null_frac1)
*
(1 - null_frac2)
*
min(1/num_distinct1, 1/num_distinct2)
= (1 - 0)
*
(1 - 0) / max(10000, 10000)
= 0.0001
Thisis,subtractthenullfractionfromoneforeachoftherelations,anddividebythemaximumof
thenumbersofdistinctvalues.Thenumberofrowsthatthejoinislikelytoemitiscalculatedasthe
cardinalityoftheCartesianproductofthetwoinputs,multipliedbytheselectivity:
1985
Chapter61.HowthePlannerUsesStatistics
rows = (outer_cardinality
*
inner_cardinality)
*
selectivity
= (50
*
10000)
*
0.0001
= 50
HadtherebeenMCVlistsforthetwocolumns,
eqjoinsel
wouldhaveuseddirectcomparisonof
theMCVliststodeterminethejoinselectivitywithinthepartofthecolumnpopulationsrepresented
bytheMCVs.Theestimatefortheremainderofthepopulationsfollowsthesameapproachshown
here.
Noticethatweshowed
inner_cardinality
as10000, thatis,theunmodifiedsizeof
tenk2
.It
mightappearfrominspectionofthe
EXPLAIN
outputthattheestimateofjoinrowscomesfrom50
*1,thatis,thenumberofouterrowstimestheestimatednumberofrowsobtainedbyeachinner
indexscanon
tenk2
.Butthisisnotthecase:thejoinrelationsizeisestimatedbeforeanyparticular
joinplanhasbeenconsidered.Ifeverythingisworkingwellthenthetwowaysofestimatingthejoin
sizewillproduceaboutthesameanswer,butduetoround-offerrorandotherfactorstheysometimes
divergesignificantly.
Forthoseinterestedinfurtherdetails,estimationofthesizeofatable(beforeany
WHERE
clauses)is
donein
src/backend/optimizer/util/plancat.c
.Thegenericlogicforclauseselectivitiesis
in
src/backend/optimizer/path/clausesel.c
.Theoperator-specificselectivityfunctionsare
mostlyfoundin
src/backend/utils/adt/selfuncs.c
.
1986
VIII. Appendixes
Documents you may be interested
Documents you may be interested