1
This is pgin.tcl/INTERNALS, notes on internal implementation of pgin.tcl.
2
Last updated for pgin.tcl-1.5.0 on 2003-06-28
3
-----------------------------------------------------------------------------
5
INTERNAL IMPLEMENTATION NOTES:
7
This information is provided for maintenance, test, and debugging.
9
A connection handle is just a Tcl socket channel.
11
Multiple connections should work OK but have not been extensively tested.
12
Some variables are global to all connections (for example, what to
15
Internal procedures, result structures, and other data are stored in a
16
namespace called "pgtcl".
18
A result structure is implemented as a variable result$N in the pgtcl
19
namespace, where N is an integer. (The value of N is stored in pgtcl::rn
20
and is incremented each time a new result structure is needed.) The result
21
handle is passed back to the caller as $N, just the integer. The result
22
structure is an array which stores all the meta-information about the
23
result as well as the result values.
25
The result structure array indexes in use are:
27
Variables describing the overal result:
28
result(conn) The connection handle (the socket channel)
29
result(nattr) Number of attributes (columns)
30
result(ntuple) Number of tuples (rows)
31
result(nmb) Number of bytes in the data null-map
32
result(status) PostgreSQL status code, e.g. PGRES_TUPLES_OK
33
result(error) Error message if status is PGRES_FATAL_ERROR
34
result(complete) Command completion status, e.g. "INSERT 10101"
36
Variables describing the attributes (columns) in the result:
37
result(attribs) A list of attribute names
38
result(types) A list of attribute type OIDs
39
result(sizes) A list of attribute byte lengths or -1 if variable
40
result(modifs) A list of size modifiers for attributes (not used)
41
(These are proper Tcl lists containing the data for each attribute
42
in order. The size modifiers are returned by the PostgreSQL backend
43
but there is no interface in libpgtcl to get this information.)
45
Variables storing the query result values:
46
result($irow,$icol) Data for tuple (row) $irow, attribute number $icol.
47
(irow goes from 0 to result(ntuples)-1.
48
icol goes from 0 to result(nattr)-1.)
50
(Note: Before pgin.tcl version 1.5.0, attribute names were used to index the
51
result values. Starting with 1.5.0, attribute numbers are used instead due
52
to problems with duplicate column names.)
54
The pg_exec call creates a new result structure. The pg_result call
55
retrieves information from the result structure and also frees the result
56
structure with the -clear option. The result structure innards are also
57
directly accessed by some other routines, such as pg_select and pg_execute.
58
All result structures associated with a connection handle are freed when
59
the connection handle is closed by pg_disconnect.
61
The entire result of a query is stored before anything else happens (that
62
is, before pg_exec returns, and before pg_execute and pg_select process the
63
first row). This is also true of libpq and libpgtcl, but Tcl is much
64
slower reading from the backend, and performance will suffer when queries
65
return large amounts of data. (The main slow-down seems to be when Tcl
66
needs to read null-terminated data from the socket, and has to get it one
69
-----------------------------------------------------------------------------
72
An array pgtcl::notify keeps track of notifications you want. The array is
73
indexed as pgtcl::notify(connection,name) where connection is the
74
connection handle (socket name) and name is the parameter used in
75
pg_listen. The value of an array element is the command to execute on
76
notification. Note that no data is passed - just the fact that the
77
notification occurred.
79
-----------------------------------------------------------------------------
82
The large object calls are implemented using the PostgreSQL "fast-path"
83
function call interface (same as libpq). See the next section for more
86
The pg_lo_creat command takes a mode argument. According to the PostgreSQL
87
libpq documentation, lo_creat should take "INV_READ", "INV_WRITE", or
88
"INV_READ|INV_WRITE". (pgin.tcl accepts "r", "w", and "rw" as equivalent
89
to those respectively, but this is not compatible with libpgtcl.) It isn't
90
clear why you would ever create a large object with other than
93
The pg_lo_open command also takes a mode argument. According to the
94
PostgreSQL libpq documentation, lo_open takes the same mode values as
95
lo_creat. But in libpgtcl the pg_lo_open command takes "r", "w", or "rw"
96
for the mode, for some reason. pgin.tcl accepts either form for mode,
97
but to be compatible with libpgtcl you should use "r", "w", or "rw"
98
with pg_lo_open instead of INV_READ, INV_WRITE, or INV_READ|INV_WRITE.
101
-----------------------------------------------------------------------------
102
FAST-PATH FUNCTION CALLS
104
Access to the PostgreSQL "Fast-path function call" interface is available
105
in pgin.tcl. This was written to implement the large object calls, and
106
general use is discouraged. See the libpq documentation for more details on
107
what this interface is and how to use it.
109
Internally, backend functions are called by their PostgreSQL OID, but
110
pgin.tcl handles the mapping of function name to OID for you. The
111
fast-path function interface in pgin.tcl uses an array pgtcl::fnoids to
112
cache object IDs of the PostgreSQL functions. One instance of this array
113
is shared among all connections, under the assumption that these OIDs are
114
common to all databases. (It is possible that if you have simultaneous
115
connections to multiple database servers running different versions of
116
PostgreSQL this could break.) The index to pgtcl::fnoids is the name
117
of the function, or the function plus argument type list, as supplied
118
to the pgin.tcl fast-path function call commands. The value of each
119
array index is the OID of the function.
121
PostgreSQL supports overloaded functions (same name, different number
122
and/or argument types). You can call overloaded functions with pgin.tcl by
123
specifying the argument type list after the function name. See examples
124
below. You must specify the argument list exactly like psql "\df" does - as
125
a list of correct type names, separated by a single comma and space. There
126
is currently no provision to distinguish functions by their return type. It
127
doesn't seem like there are any PostgreSQL functions which differ only by
130
If you supply the wrong number of arguments to the backend fast-path
131
function, the backend and front-end will lose synchronization and the
132
channel will be closed. This is true about libpq as well. There may be
133
other errors which cause a disconnect. Consider this a hint not to use
134
this interface casually.
139
pg_callfn $db "fname" result "arginfo" arg...
141
Call a PostgreSQL backend function and store the result.
142
Returns the size of the result in bytes.
146
$db is the connection handle.
148
"fname" is the PostgreSQL function name. This is either a simple
149
name, like "encode", or a name followed by a parenthesized
150
argument type list, like "like(text, text)". The second form
151
is needed to specify which of several overloaded functions you want
154
"result" is the name of a variable where the PostgreSQL backend
155
function returned value is to be stored. The number of bytes
156
stored in "result" is returned as the value of pg_callfn.
158
"arginfo" is a list of argument descriptors. Each list element is
159
one of the following:
160
I An integer32 argument is expected.
161
S A Tcl string argument is expected. The length of the
162
string is used (remember Tcl strings can contain null bytes).
164
A Tcl string argument is expected, and exactly this many
165
bytes of the string argument are passed (padding with null
168
arg... Zero or more arguments to the PostgreSQL function follow.
169
The number of arguments must match the number of elements
170
in the "arginfo" list. The values are passed to the backend
171
function according to the corresponding descriptor in
174
For PostgreSQL backend functions which return a single integer32 argument,
175
the following simplified interface is available:
177
pg_callfn_int $db "fname" "arginfo" arg...
179
The db, fname, arginfo, and other arguments are the same as
180
for pg_callfn. The return value from pg_callfn_int is the
181
integer32 value returned by the PostgreSQL backend function.
184
Note: These examples demonstrate the command, but in both of these
185
cases you would be better off using an SQL query instead.
187
set n [pg_callfn $db version result ""]
188
This calls the backend function version() and stores the return
189
value in $result and the result length in $n.
191
pg_callfn $db encode result {S S} $str base64
192
This calls the backend function encode($str, "base64") with 2
193
string arguments and stores the result in $result.
195
pg_callfn_int $db length(text) S "This is a test"
196
This calls the backend function length("This is a test"). Because
197
there are multiple functions called length(), the argument type
198
list "(text)" must be given after the function name. The length
199
of the string (14) is returned by the function.
201
-----------------------------------------------------------------------------
204
MD5 authentication was added at PostgreSQL-7.2. This is a
205
challenge/response protocol which avoids having clear-text passwords passed
206
over the network. To activate this, the PostgreSQL administrator puts "md5"
207
in the pg_hba.conf file instead of "password". Pgin.tcl supports this
208
transparently; that is, if the backend requests MD5 authentication during
209
the connection, pg_connect will use this protocol. The MD5 implementation
210
was coded by the original author of pgin.tcl. It does not use the tcllib
211
implementation, which is significantly faster but much more complex.
213
-----------------------------------------------------------------------------
added
removed
pgintcl-1.5.0/INTERNALS.txt
