1

I am trying to setup automatic audit Logging in Postgres Using Triggers and Trigger Functions. For this i want to create the table logged_actions in audit schema. When i run the following query :

CREATE TABLE IF NOT EXISTS audit.logged_actions
(
    event_id bigint NOT NULL DEFAULT nextval('audit.logged_actions_event_id_seq'::regclass),
    schema_name text COLLATE pg_catalog."default" NOT NULL,
    table_name text COLLATE pg_catalog."default" NOT NULL,
    relid oid NOT NULL,
    session_user_name text COLLATE pg_catalog."default",
    action_tstamp_tx timestamp with time zone NOT NULL,
    action_tstamp_stm timestamp with time zone NOT NULL,
    action_tstamp_clk timestamp with time zone NOT NULL,
    transaction_id bigint,
    application_name text COLLATE pg_catalog."default",
    client_addr inet,
    client_port integer,
    client_query text COLLATE pg_catalog."default",
    action text COLLATE pg_catalog."default" NOT NULL,
    row_data hstore,
    changed_fields hstore,
    statement_only boolean NOT NULL,
    CONSTRAINT logged_actions_pkey PRIMARY KEY (event_id),
    CONSTRAINT logged_actions_action_check CHECK (action = ANY (ARRAY['I'::text, 'D'::text, 'U'::text, 'T'::text]))
)

I have already created the extension "hstore" and query is not executed and error message appears stating that

ERROR: type "hstore" is only a shell LINE 17: row_data hstore

enter image description here

Mani Ratna
  • 883
  • 1
  • 11
  • 30

1 Answers1

0

That's a cryptic way of saying the hstore extension isn't loaded. You need to create extension hstore before you can use it.

Note that jsonb more-or-less makes hstore obsolete.

Schwern
  • 153,029
  • 25
  • 195
  • 336