4,445 questions
1
vote
1
answer
68
views
Redshift : Opening multiple cursors from within the same client connection is not allowed
I have the following procedure written in redshift, which uses 2 for loops to get country code first and then years as per the country codes.
--Procedure : sp_for_loop_test
create or replace procedure ...
- 7,355
1
vote
1
answer
84
views
How to handle inserts and updates on postgres temporal tables?
I have couple temporal tables in my db.
CREATE TABLE temporal (
version_id SERIAL PRIMARY KEY,
some_id TEXT,
some_data TEXT,
valid_from TIMESTAMP NOT NULL DEFAULT ...
- 1,879
3
votes
1
answer
186
views
Why the same IMMUTABLE function takes more time than STABLE?
I have changed STABLE to IMMUTABLE and expecting it to work faster, but it works more slower. Did I miss something?
IMMUTABLE function:
test=> \sf+ rls_guard
CREATE OR REPLACE FUNCTION ...
- 26.1k
3
votes
1
answer
121
views
Update where current of cursor in a PL/pgSQL procedure. ERROR: cursor does not exist
I'm using 17.0.6 version (and 13 yet, in another test) of ODBC driver against a PostGreSQL 17 database, I got an unexplicable error when, from a PowerBuilder 12 application using the ODBC, I call a ...
- 51
3
votes
1
answer
146
views
How do I enforce uniqueness across parent and child tables without denormalizing my data?
I have two tables in Postgres with a parent/child relationship:
CREATE TABLE table_a (
id SERIAL PRIMARY KEY,
type TEXT NOT NULL,
rundate DATE NOT NULL
);
CREATE TABLE table_b (
id ...
- 496
3
votes
3
answers
120
views
Using a script variable to generate a stored procedure in Postgresql
I would like to use a script variable to set the data type, so that I can generate a few functions with different types, like so:
\set bucket_data_type DATE
Then run a script to generate this function:...
- 33
2
votes
1
answer
76
views
Catch pg_cancel_backend in plpgsql
I understand that I can't error-handle pg_terminate_backend in PL/pgSQL. But I am surprised that I can't error-handle pg_cancel_backend.
Below is one attempt that does not work, but I also tried ...
- 1,416
0
votes
1
answer
58
views
Failed to create database function at or near "create"
I'm trying to create a new function in Supabase. I'm literally using the example code in the docs:
create function hello_world()
returns text
language plpgsql
security definer set search_path = ''
as $...
- 129
3
votes
1
answer
118
views
PostgreSQL: Does an EXCEPTION block automatically release its subtransaction after finishing?
In PostgreSQL, I know that an EXCEPTION block starts a subtransaction within the current transaction. However, I'm not sure when this subtransaction is released. For example:
CREATE PROCEDURE ...
- 57
0
votes
0
answers
95
views
How to call database function followed by VACUUM in cron job
I want to schedule some database maintenance that consists of deleting old data followed by VACUUM:
CREATE EXTENSION IF NOT EXISTS pg_cron;
SELECT cron.schedule('cleanup', '* * * * *', '
CALL ...
- 27.8k
1
vote
1
answer
80
views
Why is this PostgreSQL function throwing an exception, and how can I tell which input is?
I have a table which stores attribute value data, the majority of which are numbers but occasionally strings. When the values are numbers, we are interested in a delta. The following PostgreSQL ...
- 1,206
0
votes
0
answers
45
views
How to use a dynamically created table name in a PL/pgSQL block (Postgres)
I have a block of code in PL/pgSQL that includes this line to update a table...
UPDATE myTable SET processedT = true, messType = 12 WHERE messId = new.messID;
The table myTable is actually a ...
- 867
0
votes
1
answer
52
views
Variables interpreted as values in transaction
CREATE FUNCTION create_user_if_not_exists(_name text, _pass text)
RETURNS void AS
$func$
DECLARE
_dbname TEXT := concat(_name, '_db');
BEGIN
IF EXISTS (SELECT FROM pg_catalog.pg_roles WHERE ...
- 3,221
2
votes
1
answer
50
views
How to analyze query with parameters in Postgres
I wanted to analyze and compare following queries:
SELECT from mytable WHERE mytable.TimeStamp < NOW() - MAKE_INTERVAL(DAYS => 1);
and same query, but replace Now() with a variable.
DO $$
...
- 27.8k
1
vote
1
answer
96
views
Delete using subquery vs temp table
I'm deleting data and I want to minimize time when rows or table is locked. Goal is to increase throughput of other queries.
Let's say inserting into temp table takes 10 seconds and delete statement ...
- 27.8k