[baseten-users] How to enable/disable baseten entities

[Tuukka Norri]

Jianhua Meng kirjoitti 31.3.2009 kello 21.56:
> Hi List,
>
> BaseTen manual mentions that it is possible to enable/disable  
> entities via SQL functions baseten.disable and baseten.enable. Can  
> someone kindly give an sql example on how exactly this is done?
>
> Would the following do it assuming PatientAccount is the name of a  
> table? Is there any documentation on baseten defined functions?
>
> baseten.disable('PatientAccount')

Hi!

The functions take an oid as the argument. See here: http://www.postgresql.org/docs/8.3/interactive/datatype-oid.html
In short, PostgreSQL assigns a numeric identifier to various database  
objects, such as types and relations. In earlier versions, also  
individual rows in tables were assigned oids, but this no longer  
happens, unless WITH OIDS is specified when creating a table.

To get the oid of a relation, you can use a query like this:
SELECT c.oid FROM pg_class c INNER JOIN pg_namespace n ON  
c.relnamespace = n.oid WHERE c.relname = 'table_name' AND n.nspname =  
'schema_name';

In this case, replace table_name with PatientAccount and schema_name  
with the schema's name, which is probably 'public' if you haven't  
changed the search_path variable. You should get a number which you  
can pass to baseten.enable or baseten.disable. There's another example  
at the end of this message.

PostgreSQL cannot supply the information about changes (inserts,  
updates and deletes) on its own. Instead, triggers and helper tables  
need to be created, which is what baseten.enable does. Because of  
this, we don't need to poll the database each time when something  
might have happened. This causes inserts, updates and deletes in  
enabled tables also to invoke SQL functions, which mightn't be  
desirable under all circumstances. For example, if rows are inserted  
into a certain table in rapid succession, polling might be more  
efficient.


Here's a log copied from psql, where I enable a relation called Sticky  
in the public schema.

stickies=> \d
               List of relations
  Schema |     Name      |   Type   |  Owner
--------+---------------+----------+---------
  public | Sticky        | table    | tsnorri
  public | Sticky_id_seq | sequence | tsnorri
(2 rows)

stickies=> SELECT c.oid FROM pg_class c INNER JOIN pg_namespace n ON  
c.relnamespace = n.oid WHERE c.relname = 'Sticky' AND n.nspname =  
'public';
   oid
--------
  179629
(1 row)

stickies=> SELECT baseten.enable (179629);
NOTICE:  merging column "baseten_lock_relid" with inherited definition
CONTEXT:  SQL statement "CREATE TABLE "baseten".lock__179629  
("baseten_lock_relid" OID NOT NULL DEFAULT 179629, id int4 NOT NULL)  
INHERITS ("baseten".lock)"
PL/pgSQL function "enable" line 33 at EXECUTE statement
SQL function "enable" statement 1
NOTICE:  merging column "baseten_modification_relid" with inherited  
definition
CONTEXT:  SQL statement "CREATE TABLE "baseten".mod__179629  
("baseten_modification_relid" OID NOT NULL DEFAULT 179629, id int4 NOT  
NULL) INHERITS ("baseten".modification)"
PL/pgSQL function "enable" line 54 at EXECUTE statement
SQL function "enable" statement 1
          enable
------------------------
  (179629,public,Sticky)
(1 row)

-- 
Best regards,
Tuukka Norri
MK&C