diff options
| -rw-r--r-- | sql/pgq/functions/pgq.batch_event_sql.sql | 2 | ||||
| -rw-r--r-- | sql/pgq/functions/pgq.create_queue.sql | 18 | ||||
| -rw-r--r-- | sql/pgq/functions/pgq.drop_queue.sql | 8 | ||||
| -rw-r--r-- | sql/pgq/functions/pgq.grant_perms.sql | 6 | ||||
| -rw-r--r-- | sql/pgq/functions/pgq.maint_rotate_tables.sql | 4 | ||||
| -rw-r--r-- | sql/pgq/functions/pgq.quote_fqname.sql | 36 | ||||
| -rw-r--r-- | sql/pgq/functions/pgq.seq_funcs.sql | 17 | ||||
| -rw-r--r-- | sql/pgq/structure/func_internal.sql | 1 | ||||
| -rw-r--r-- | sql/pgq/structure/grants.ini | 1 |
9 files changed, 60 insertions, 33 deletions
diff --git a/sql/pgq/functions/pgq.batch_event_sql.sql b/sql/pgq/functions/pgq.batch_event_sql.sql index 1ed7c5ac..c826a0f2 100644 --- a/sql/pgq/functions/pgq.batch_event_sql.sql +++ b/sql/pgq/functions/pgq.batch_event_sql.sql @@ -97,7 +97,7 @@ begin for rec in select xtbl from pgq.batch_event_tables(x_batch_id) xtbl loop - tbl := rec.xtbl; + tbl := pgq.quote_fqname(rec.xtbl); -- this gets newer queries that definitely are not in prev_snapshot part := select_fields || ' from pgq.tick cur, pgq.tick last, ' || tbl || ' ev ' diff --git a/sql/pgq/functions/pgq.create_queue.sql b/sql/pgq/functions/pgq.create_queue.sql index d1faacf2..ded10e77 100644 --- a/sql/pgq/functions/pgq.create_queue.sql +++ b/sql/pgq/functions/pgq.create_queue.sql @@ -52,21 +52,21 @@ begin where queue_id = id; -- create seqs - execute 'CREATE SEQUENCE ' || tick_seq; - execute 'CREATE SEQUENCE ' || ev_seq; + execute 'CREATE SEQUENCE ' || pgq.quote_fqname(tick_seq); + execute 'CREATE SEQUENCE ' || pgq.quote_fqname(ev_seq); -- create data tables - execute 'CREATE TABLE ' || tblpfx || ' () ' + execute 'CREATE TABLE ' || pgq.quote_fqname(tblpfx) || ' () ' || ' INHERITS (pgq.event_template)'; for i in 0 .. (n_tables - 1) loop tblname := tblpfx || '_' || i::text; - idxname := idxpfx || '_' || i::text; - execute 'CREATE TABLE ' || tblname || ' () ' - || ' INHERITS (' || tblpfx || ')'; - execute 'ALTER TABLE ' || tblname || ' ALTER COLUMN ev_id ' + idxname := idxpfx || '_' || i::text || '_txid_idx'; + execute 'CREATE TABLE ' || pgq.quote_fqname(tblname) || ' () ' + || ' INHERITS (' || pgq.quote_fqname(tblpfx) || ')'; + execute 'ALTER TABLE ' || pgq.quote_fqname(tblname) || ' ALTER COLUMN ev_id ' || ' SET DEFAULT nextval(' || quote_literal(ev_seq) || ')'; - execute 'create index ' || idxname || '_txid_idx on ' - || tblname || ' (ev_txid)'; + execute 'create index ' || quote_ident(idxname) || ' on ' + || pgq.quote_fqname(tblname) || ' (ev_txid)'; end loop; perform pgq.grant_perms(i_queue_name); diff --git a/sql/pgq/functions/pgq.drop_queue.sql b/sql/pgq/functions/pgq.drop_queue.sql index dd1fa8ab..63ec7850 100644 --- a/sql/pgq/functions/pgq.drop_queue.sql +++ b/sql/pgq/functions/pgq.drop_queue.sql @@ -46,17 +46,17 @@ begin -- drop data tables for i in 0 .. (q.queue_ntables - 1) loop tblname := q.queue_data_pfx || '_' || i::text; - execute 'DROP TABLE ' || tblname; + execute 'DROP TABLE ' || pgq.quote_fqname(tblname); end loop; - execute 'DROP TABLE ' || q.queue_data_pfx; + execute 'DROP TABLE ' || pgq.quote_fqname(q.queue_data_pfx); -- delete ticks delete from pgq.tick where tick_queue = q.queue_id; -- drop seqs -- FIXME: any checks needed here? - execute 'DROP SEQUENCE ' || q.queue_tick_seq; - execute 'DROP SEQUENCE ' || q.queue_event_seq; + execute 'DROP SEQUENCE ' || pgq.quote_fqname(q.queue_tick_seq); + execute 'DROP SEQUENCE ' || pgq.quote_fqname(q.queue_event_seq); -- delete event delete from pgq.queue diff --git a/sql/pgq/functions/pgq.grant_perms.sql b/sql/pgq/functions/pgq.grant_perms.sql index db8c6edf..97412367 100644 --- a/sql/pgq/functions/pgq.grant_perms.sql +++ b/sql/pgq/functions/pgq.grant_perms.sql @@ -38,10 +38,10 @@ begin end if; -- tick seq, normal users don't need to modify it - execute 'grant select on ' || q.queue_tick_seq || ' to public'; + execute 'grant select on ' || pgq.quote_fqname(q.queue_tick_seq) || ' to public'; -- event seq - execute 'grant select on ' || q.queue_event_seq || ' to public'; + execute 'grant select on ' || pgq.quote_fqname(q.queue_event_seq) || ' to public'; -- set grants on parent table perform pgq._grant_perms_from('pgq', 'event_template', dst_schema, dst_table); @@ -94,5 +94,5 @@ begin return 1; end; -$$ language plpgsql; +$$ language plpgsql strict; diff --git a/sql/pgq/functions/pgq.maint_rotate_tables.sql b/sql/pgq/functions/pgq.maint_rotate_tables.sql index d3cee739..fdb71d44 100644 --- a/sql/pgq/functions/pgq.maint_rotate_tables.sql +++ b/sql/pgq/functions/pgq.maint_rotate_tables.sql @@ -68,8 +68,8 @@ begin -- there may be long lock on the table from pg_dump, -- detect it and skip rotate then begin - execute 'lock table ' || tbl || ' nowait'; - execute 'truncate ' || tbl; + execute 'lock table ' || pgq.quote_fqname(tbl) || ' nowait'; + execute 'truncate ' || pgq.quote_fqname(tbl); exception when lock_not_available then -- cannot truncate, skipping rotate diff --git a/sql/pgq/functions/pgq.quote_fqname.sql b/sql/pgq/functions/pgq.quote_fqname.sql new file mode 100644 index 00000000..975ac374 --- /dev/null +++ b/sql/pgq/functions/pgq.quote_fqname.sql @@ -0,0 +1,36 @@ + +create or replace function pgq.quote_fqname(i_name text) +returns text as $$ +-- ---------------------------------------------------------------------- +-- Function: pgq.quote_fqname(1) +-- +-- Quete fully-qualified object name for SQL. +-- +-- First dot is taken as schema separator. +-- +-- If schema is missing, 'public' is assumed. +-- +-- Parameters: +-- i_name - fully qualified object name. +-- +-- Returns: +-- Quoted name. +-- ---------------------------------------------------------------------- +declare + res text; + pos integer; + s text; + n text; +begin + pos := position('.' in i_name); + if pos > 0 then + s := substring(i_name for pos - 1); + n := substring(i_name from pos + 1); + else + s := 'public'; + n := i_name; + end if; + return quote_ident(s) || '.' || quote_ident(n); +end; +$$ language plpgsql strict immutable; + diff --git a/sql/pgq/functions/pgq.seq_funcs.sql b/sql/pgq/functions/pgq.seq_funcs.sql index 4a31f3a9..6b90e4df 100644 --- a/sql/pgq/functions/pgq.seq_funcs.sql +++ b/sql/pgq/functions/pgq.seq_funcs.sql @@ -32,7 +32,7 @@ begin execute 'select last_value from ' || fqname into res; return res; end; -$$ language plpgsql; +$$ language plpgsql strict; create or replace function pgq.seq_setval(i_seq_name text, i_new_value int8) returns bigint as $$ @@ -51,19 +51,8 @@ returns bigint as $$ declare res int8; fqname text; - pos integer; - s text; - n text; begin - pos := position('.' in i_seq_name); - if pos > 0 then - s := substring(i_seq_name for pos - 1); - n := substring(i_seq_name from pos + 1); - else - s := 'public'; - n := i_seq_name; - end if; - fqname := quote_ident(s) || '.' || quote_ident(n); + fqname := pgq.quote_fqname(i_seq_name); res := pgq.seq_getval(i_seq_name); if res < i_new_value then @@ -72,5 +61,5 @@ begin end if; return res; end; -$$ language plpgsql; +$$ language plpgsql strict; diff --git a/sql/pgq/structure/func_internal.sql b/sql/pgq/structure/func_internal.sql index b6e2467d..09a81f01 100644 --- a/sql/pgq/structure/func_internal.sql +++ b/sql/pgq/structure/func_internal.sql @@ -31,4 +31,5 @@ select pgq.upgrade_schema(); \i functions/pgq.tune_storage.sql \i functions/pgq.force_tick.sql \i functions/pgq.seq_funcs.sql +\i functions/pgq.quote_fqname.sql diff --git a/sql/pgq/structure/grants.ini b/sql/pgq/structure/grants.ini index 82cb8554..451695da 100644 --- a/sql/pgq/structure/grants.ini +++ b/sql/pgq/structure/grants.ini @@ -48,6 +48,7 @@ pgq_generic_fns = pgq.get_consumer_info(), pgq.get_consumer_info(text), pgq.get_consumer_info(text, text), + pgq.quote_fqname(text), pgq.version() pgq_read_fns = |