The first question is answered by recalling the fundamental reason for wanting dimension tables in a data mart. The dimension tables serve as the source of constraints and as the source of report row headers.
Drop table dim_time;CREATE TABLE dim_time
( pk_time integer NOT NULL,
time_time time without time zone,
time_hour smallint,
time_minute smallint,
time_second smallint,
time_12hr character(8),
time_24hr character(8),
CONSTRAINT pk_dim_time PRIMARY KEY (pk_time)
)
WITH (
OIDS=FALSE
);
ALTER TABLE dim_time
OWNER TO postgres;insert into dim_time
(
pk_time
, time_time
, time_hour
, time_minute
, time_second
, time_12hr
, time_24hr
)
select
NULLIF(to_char(time_hour, '00')||
trim(to_char(time_minute, '00'))||
trim(to_char(time_second, '00')), '')::int as pk_time
, time_time
, time_hour
, time_minute
, time_second
, to_char(time_time, 'HH12:MI:SS') as time_12hr
, to_char(time_time, 'HH24:MI:SS') as time_24hr
from
(
select
TIMESTAMP WITHOUT TIME ZONE 'epoch' +
((60*60*time_hour) + (60* time_minute)
+ time_second) * INTERVAL '1 second' as time_time
, time_hour
, time_minute
, time_secondfrom
generate_series(0,23) as time_hour
, generate_series(0,59) as time_minute
, generate_series(0,59) as time_second
) as t
order by
time_hour,
time_minute,
time_second
pgmemcache: PostgreSQL memcache function
memcache_add(key::TEXT, value::TEXT, expire::TIMESTAMPTZ) memcache_add(key::TEXT, value::TEXT, expire::INTERVAL) memcache_add(key::TEXT, value::TEXT)Adds a key to the cache cluster, if the key does not already exist.
newval = memcache_decr(key::TEXT, decrement::INT8) newval = memcache_decr(key::TEXT)If key exists and is an integer, atomically decrements by the value specified (default decrement is one). Returns INT value after decrement.
What are advantages of using transaction pooling with pgbouncer?
Transaction-level pooling will help if you have apps that hold idle sessions. PgBouncer won’t need to keep sessions open and idle, it just grabs one when a new transaction is started. Those idle sessions only cost you a pgbouncer connection, not a real idle Pg session with a backend sitting around wasting memory & synchronisation overhead doing nothing.
The main reason you’d want session pooling instead of transaction pooling is if you want to use named prepared statements, advisory locks, listen/notify, or other features that operate on a session level not a transaction level.