Обсуждение: New Developer's FAQ item

I have added this to the developer's FAQ.  Any comments or corrections?

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)
9) How do I efficiently access information in tables from the backend code?

There are two ways. First, SearchSysCacheTuple() and related functions allow
you to query the system catalogs. This is the preferred way to access system
tables, because the first call to the cache loads the needed rows, and
future requests can return the results without accessing the base table.
Some of the caches use system table indexes to look up tuples. A list of
available caches is located in src/backend/utils/cache/syscache.c.
src/backend/utils/cache/lsyscache.c contains many column-specific cache
lookup functions.

The rows returned are cached-owned copies of the heap rows. They are
invalidated when the base table changes. Because the cache is local to each
backend, you may use the pointer returned from the cache for short periods
without making a copy of the tuple. If you send the pointer into a large
function that will be doing its own cache lookups, it is possible your entry
may be flushed, so you should use SearchSysCacheTupleCopy() in these cases,
and pfree() the resulting tuple when you are done.

If you can't use the system cache, you will need to retrieve the data
directly from the heap table, using the buffer cache that is shared by all
backends. The backend automatically takes care of loading the rows into the
buffer cache.

Open the table with heap_open(). You can then start a table scan with
heap_beginscan(), then use heap_getnext() and continue as long as
HeapTupleIsValid is true. Then do a heap_endscan(). Keys can be assigned to
the scan. No indexes are used, so all rows are going to be compared to the
keys, and only the valid rows returned.

You can also use heap_fetch() to sequentially fetch rows from the table.
Scans automatically lock/unlock rows from the buffer cache, so when using
heap_fetch(), you must retrieve the Buffer pointer, and ReleaseBuffer it
when completed.

   I have added this to the developer's FAQ.  Any comments or corrections?

   9) How do I efficiently access information in tables from the backend code?

Is all this relevant for writing triggers that have to access tables
in order to verify/modify a given tuple?  Is that even possible?  Are
there any examples?

Cheers,
Brook

>    I have added this to the developer's FAQ.  Any comments or corrections?
>
>    9) How do I efficiently access information in tables from the backend code?
>
> Is all this relevant for writing triggers that have to access tables
> in order to verify/modify a given tuple?  Is that even possible?  Are
> there any examples?

Yes, I think so.  You can check out contrib/spi.

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

>
>    I have added this to the developer's FAQ.  Any comments or corrections?
>
>    9) How do I efficiently access information in tables from the backend code?
>
> Is all this relevant for writing triggers that have to access tables
> in order to verify/modify a given tuple?  Is that even possible?  Are
> there any examples?
>
> Cheers,
> Brook

    But keep in mind that the syscache and heap access goes
    in without ACL checks!


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

   > Is all this relevant for writing triggers that have to access tables
   > in order to verify/modify a given tuple?  Is that even possible?  Are
   > there any examples?

       But keep in mind that the syscache and heap access goes
       in without ACL checks!

I don't quite know what you mean here.  What are ACL checks?  Sorry
for the naive question.

Cheers,
Brook

>
>    > Is all this relevant for writing triggers that have to access tables
>    > in order to verify/modify a given tuple?  Is that even possible?  Are
>    > there any examples?
>
>        But keep in mind that the syscache and heap access goes
>        in without ACL checks!
>
> I don't quite know what you mean here.  What are ACL checks?  Sorry
> for the naive question.
>
> Cheers,
> Brook

    On  any  table,  the owner or a superuser can GRANT or REVOKE
    access to or from other users. Thus, you might  have  granted
    another user permissions to read some of your tables, but not
    other ones.  The permissions you've setup  are  held  in  the
    relacl column in pg_class.

    But  these permissions are checked only if a regular query is
    processed by the executor (or after  my  new  changes  during
    query  rewrite).   When  accessing  information  through  the
    syscache or heap access methods, the  ACL's  (access  control
    lists) aren't checked.

    If  you  write  a  function,  that  reads  tables and returns
    information from them, any user can use  these  functions  to
    see  the data they return. Even if you explicitly revoked the
    user from reading these tables. If the function uses  SPI  to
    access  the tables, the ACL checks get performed and the user
    cannot use them to look at your data.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #