Syntax
Hi All, What is the difference or use case for below syntaxes? do $$ declare d int; begin RAISE INFO 'Script started at %', CURRENT_TIMESTAMP; update employees set first_name = 'g' where employee_id = 1; get diagnostics d = row_count; raise info 'selected: % rows', d; RAISE INFO 'Script finished at %', CURRENT_TIMESTAMP; end;$$; Or Just Begin; Update statements Commit;
Re: PostgreSQL 12 error unrecognized configuration parameter zbx_tmp.wal_json_res
Hi,
Thanks for the reponse. What could the the potential consequenses of this
error?
Is it something that can be ignored until fix (hopefully) is provided, or
are there critical functions now not working?
Best regards,
Arne H.
ons. 6. des. 2023 kl. 17:21 skrev Adrian Klaver :
> On 12/6/23 04:21, Arne Henrik Segtnan wrote:
> >
> > Hi all,
> >
> > We are currently running Zabbix 5.0 with PostgreSQL 12, and history and
> > trend data partitioning.
> > History and trend data housekeeping has been disabled in Zabbix.
> >
> > In the PostgreSQL logs, we get the following error:
> >
> > 2023-12-06 09:12:47 CET [3509536-5] zabbix@postgres STATEMENT: select
> > current_setting('zbx_tmp.wal_json_res');
> > 2023-12-06 09:17:47 CET [3516312-1] zabbix@postgres ERROR: permission
> > denied for function pg_ls_waldir
> > 2023-12-06 09:17:47 CET [3516312-2] zabbix@postgres CONTEXT: SQL
> > statement "SELECT row_to_json(T) FROM (
> > SELECT pg_wal_lsn_diff(pg_current_wal_lsn(),'0/') AS WRITE,
> > count(*) FROM pg_ls_waldir() AS COUNT
> > ) T"
> > PL/pgSQL function inline_code_block line 10 at SQL statement
> > 2023-12-06 09:17:47 CET [3516312-3] zabbix@postgres STATEMENT: DO
> > LANGUAGE plpgsql $$
> > DECLARE
> > ver integer;
> > res text := '{"write":0,"count":0}';
> > BEGIN
> > SELECT current_setting('server_version_num') INTO ver;
> >
> > IF (SELECT NOT pg_is_in_recovery()) THEN
> > IF (ver >= 10) THEN
> > SELECT row_to_json(T) INTO res FROM (
> > SELECT pg_wal_lsn_diff(pg_current_wal_lsn(),'0/') AS WRITE,
> > count(*) FROM pg_ls_waldir() AS COUNT
> > ) T;
> >
> > ELSE
> > SELECT row_to_json(T) INTO res FROM (
> > SELECT pg_xlog_location_diff(pg_current_xlog_location(),' 0/')
> > AS WRITE,
> > count(*) FROM pg_ls_dir('pg_xlog') AS COUNT
> > ) T;
> > END IF;
> > END IF;
> >
> > perform set_config('zbx_tmp.wal_json_res', res, false);
> > END $$;
> > 2023-12-06 09:17:47 CET [3516312-4] zabbix@postgres ERROR: unrecognized
> > configuration parameter "zbx_tmp.wal_json_res"
> > 2023-12-06 09:17:47 CET [3516312-5] zabbix@postgres STATEMENT: select
> > current_setting('zbx_tmp.wal_json_res');
> >
> >
> > This seems to be related to permissions-problem and missing/unknown
> > configuration parameter.
> > We found the following article describing how to set correct permission:
> >
> > https://github.com/bitnami/charts/issues/20247
> >
> >
> > Is this a known issue? Should we just perform the step according to
> > procedure in above link to set permission, or will security be degraded
> > as commented in article?
>
>
> Or turn the DO into a full function created by a user with the necessary
> privileges and use SECURITY DEFINER in the function definition to confer
> those privileges to unprivileged user for the duration of the function
> execution per:
>
> https://www.postgresql.org/docs/12/sql-createfunction.html
>
> >
> > Please advise.
> >
> > Best regards,
> > Arne H.
> >
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
--
Med vennlig hilsen / Best regards,
___
Arne Henrik Segtnan
Senior Consultant
Tlf: +47 90415096
a...@basis-consulting.com
www.basis-consulting.com
This email contains information that may be confidential and is the
property of Basis Consulting. It is only intended for the recipient it is
addressed to. If you are not the rightful recipient you are not
authorized to read, print, retain, copy, disseminate, distribute, or use
all or part of this message. If you receive this message by mistake, we ask
you to notify the sender and erase all copies of it.
Re: Syntax
On Thu, Dec 7, 2023 at 3:01 AM arun chirappurath wrote: > Hi All, > > What is the difference or use case for below syntaxes? > > do $$ > declare d int; > begin > RAISE INFO 'Script started at %', CURRENT_TIMESTAMP; > update employees set first_name = 'g' where employee_id = 1; get > diagnostics d = row_count; raise info 'selected: % rows', d; > RAISE INFO 'Script finished at %', CURRENT_TIMESTAMP; end;$$; > > Or > > Just > > Begin; > > Update statements > > Commit; > One shows when the statement started, and when *you think* it ended, while the other doesn't. https://www.postgresql.org/docs/15/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT "Since these functions return the start time of the current transaction, their values do not change during the transaction." What you really want is clock_timestamp().
Re: Trainning and Certification
Yes, a roadmap, training & certification who atest knowledge, skills in Postgresql technologies. Similar with training, certification for Oracle, Mysql. joi, 7 dec. 2023, 00:23 Daniel Gustafsson a scris: > > On 6 Dec 2023, at 16:30, roger popa wrote: > > > > Exist a top of this issue, for this request? > > I'm not sure I understand what you are asking here. If you are asking for > a > postgres feature request or some form of prioritized roadmap then the > answer is > no. There is no such thing in the postgres project. > > -- > Daniel Gustafsson > >
Postgresql : pg_temp & pg_toast_temp
Hello Team I am seeing there are many schemas created, this is specially when enabled "CONCURRENTLY" option for refreshing materialized views through table trigger. pg_temp_1... pg_temp_30 pg_toast_temp_1... pg_toast_temp_30 If this is by design the temp schemas get created with the "CONCURRENTLY" option, is there any way to clean-up them automatically? Thanks, Vishu
Re: Trainning and Certification
> On 7 Dec 2023, at 00:25, roger popa wrote: > > Yes, a roadmap, training & certification who atest knowledge, skills in > Postgresql technologies. > Similar with training, certification for Oracle, Mysql. The postgres project doesn't have a shared roadmap, each developer is free to pursue whatever they choose. In that sense, postgres is very different from products with clear owners like Oracle and MySQL. -- Daniel Gustafsson
Re: Postgresql : pg_temp & pg_toast_temp
Vishwanath Kummera writes: >I am seeing there are many schemas created, this is specially when > enabled "CONCURRENTLY" option for refreshing materialized views through > table trigger. > pg_temp_1... pg_temp_30 > pg_toast_temp_1... pg_toast_temp_30 Yes, that's by design. There's a separate temp schema for each backend session "slot", which gets created when first needed. > If this is by design the temp schemas get created with the "CONCURRENTLY" > option, is there any way to clean-up them automatically? No. It'd just lead to thrashing/bloat in the pg_namespace catalog, since they'd be needed again eventually. regards, tom lane > Thanks, > Vishu
write a sql block which will commit if both updates are successful else it will have to be rolled back
Hi All, Can someone guide me to "write a sql block which will commit if both updates are successful else it will have to be rolled back"? would like to explicitly specify both commit and rollback in code.. I would like to turn off the autocommit then execute the query. Below is a just a starter ...it doesnt has COMMIT clause.. DO $$ DECLARE emp_id1 INT := 1; -- Assuming employee ID for the first update new_salary1 NUMERIC := 1; -- New salary for the first update emp_id2 INT := 2; -- Assuming employee ID for the second update new_salary2 NUMERIC := 3; -- New salary for the second update BEGIN -- Update Statement 1 UPDATE employees SET salary = new_salary1 WHERE employee_id = emp_id1; -- Update Statement 2 UPDATE employees SET salary = new_salary2 WHERE employee_id = emp_id2; EXCEPTION WHEN OTHERS THEN -- An error occurred during the update, log the error RAISE NOTICE 'Error during updates: %', SQLERRM; -- Roll back the transaction ROLLBACK; END $$; select * from public.employees Thanks, Arun
Re: write a sql block which will commit if both updates are successful else it will have to be rolled back
On Thursday, December 7, 2023, arun chirappurath wrote: > > > Can someone guide me to "write a sql block which will commit if both > updates are successful else it will have to be rolled back"? > would like to explicitly specify both commit and rollback in code.. > > I would like to turn off the autocommit then execute the query. > As documented under DO if you make an explicit transaction then execute the DO it cannot have transaction control commands. If you allow it to “auto-commit’’ then it can. Though in that case writing either rollback or commit for such a simple linear procedure becomes pointless since a transaction will already commit or rollback appropriately depending on whether the procedure provokes an exception. David J.
Invalid Parent Index Issue Will Not Resolve Despite Multiple Attempts To Fix
Hello all,
We have been utilizing partitioned tables with indexes. We've recently had
an issue where the parent table's index (id, date) became invalid
(indisvalid=FALSE, indisready=FALSE in pg_index). For reference the parent
table is partitioned on a date field within the table.
In order to find the indices causing the problem we utilized the following:
with invalid_indices as (
select
n.nspname,
c.relname as parent_index_name,
i.indrelid parent_table_oid,
i.indexrelid parent_index_oid,
x.indexdef as parent_indexdef,
substring(pg_get_indexdef(i.indexrelid), '.* btree \((.*)\)') as
parent_index_cols
from
pg_catalog.pg_class c, pg_catalog.pg_namespace n,
pg_catalog.pg_index i, pg_catalog.pg_indexes x
where true
and (i.indisvalid = false or i.indisready = false)
and i.indexrelid = c.oid and c.relnamespace = n.oid
and n.nspname != 'pg_catalog'
and n.nspname != 'information_schema'
and n.nspname != 'pg_toast'
and n.nspname = x.schemaname
and c.relname = x.indexname
),
tables_with_invalid_indices as (
select
i.*, c.relname as parent_table_name
from invalid_indices i
left join pg_class c
on i.parent_table_oid = c.oid
),
children_of_tables_with_invalid_indices as (
select
t.*,
i.inhrelid as child_table_oid,
c.relname as child_table_name
from tables_with_invalid_indices t
left join pg_inherits i
on t.parent_table_oid = i.inhparent
left join pg_class c
on i.inhrelid = c.oid
),
-- for each index on parent table, left join against index on child table
all_indices_on_children_of_tables_with_invalid_indices as
(
select
c.*,
a.oid as child_index_oid,
a.relname as child_index_name,
a.relispartition as child_index_ispartition,
h.inhparent as parent_of_child_index_oid,
x.indexdef as child_indexdef,
substring(pg_get_indexdef(a.oid), '.* btree \((.*)\)') as child_index_cols
from children_of_tables_with_invalid_indices c
left join pg_index i
on c.child_table_oid = i.indrelid
inner join pg_class a
on i.indexrelid = a.oid
and parent_index_cols = substring(pg_get_indexdef(a.oid), '.*
btree \((.*)\)')
left join pg_indexes x
on a.relname = x.indexname
left join pg_inherits h
on h.inhrelid = a.oid
),
unattached_indices_on_child_tables as
(
select
*
from all_indices_on_children_of_tables_with_invalid_indices
where not child_index_ispartition
),
missing_indices_on_child_tables as
(
select
a.*,
b.child_index_oid,
b.child_index_name,
b.child_index_ispartition,
b.child_indexdef,
b.parent_of_child_index_oid
from children_of_tables_with_invalid_indices a
left join all_indices_on_children_of_tables_with_invalid_indices b
on a.child_table_name = b.child_table_name
and a.parent_index_oid = b.parent_of_child_index_oid
where b.parent_of_child_index_oid is null
),
-- select * from all_indices_on_children_of_tables_with_invalid_indices
problems as (
select
u.parent_table_name,
u.parent_index_name,
u.child_table_name,
u.child_index_name,
u.parent_indexdef,
concat_ws(' ','ALTER INDEX', u.parent_index_name, 'ATTACH
PARTITION', u.child_index_name, ';') as fix_sql,
u.child_index_cols,
u.parent_index_cols
from unattached_indices_on_child_tables u
union
select
m.parent_table_name,
m.parent_index_name,
m.child_table_name,
m.child_index_name,
m.parent_indexdef,
'CREATE INDEX CONCURRENTLY IF NOT EXISTS ' || 'ix_ledger_' ||
m.child_table_name || '_' || replace(m.parent_index_cols, ', ', '_')
|| ' ON ' || m.child_table_name || ' USING btree (' ||
m.parent_index_cols || ');' as fix_sql,
'' as child_index_cols,
m.parent_index_cols
from
missing_indices_on_child_tables m
)
select * from problems;
We attempted to fix the issue by doing the following:
ALTER TABLE table_parent DETACH PARTITION table_badpartition;
DROP INDEX brokenchildindex;
CREATE INDEX newchildindex on table_badpartition using btree (id, date);
ALTER TABLE table_parent ATTACH PARTITION table_badpartition
FOR VALUES FROM (date) TO (date+1);
This did not fix the issue so we attempted an alternate fix:
begin;
set role readwrite;
ALTER TABLE table_parent DETACH PARTITION table_badpartition;
ALTER TABLE table_badpartition RENAME TO table_badpartition_detached;
CREATE TABLE table_badpartition PARTITION OF table_parent
FOR VALUES FROM (date) TO (date+1);
ALTER TABLE table_badpartitioneplica identity full;
INSERT INTO table_badpartition (id, date, ...)
SELECT id, date, ... from table_badpartition_detached;
commit;
This new table was created with the correct columns, the accurate data, and
the correct indices however the parent index is still listed with
indisvalid = FALSE and indisready = FALSE.
We did some research within the mailing list archives and found a mention
that this was an issue back in 2018 (
https://postgrespro.com/list/thread-id/2416574) with a discussion in
pghackers (
https://www.postgresql.org/message-id/20181203
Re: Invalid Parent Index Issue Will Not Resolve Despite Multiple Attempts To Fix
On Thu, 2023-12-07 at 16:38 -0600, Noel Jones wrote: > We have been utilizing partitioned tables with indexes. We've recently had an > issue > where the parent table's index (id, date) became invalid (indisvalid=FALSE, > indisready=FALSE in pg_index). For reference the parent table is partitioned > on a > date field within the table. Indexes don't become invalid just so. I wonder what happened. > We attempted to fix the issue by doing the following: > > ALTER TABLE table_parent DETACH PARTITION table_badpartition; > DROP INDEX brokenchildindex; > CREATE INDEX newchildindex on table_badpartition using btree (id, date); > ALTER TABLE table_parent ATTACH PARTITION table_badpartition > FOR VALUES FROM (date) TO (date+1); > > This did not fix the issue so we attempted an alternate fix: > > begin; > set role readwrite; > ALTER TABLE table_parent DETACH PARTITION table_badpartition; > ALTER TABLE table_badpartition RENAME TO table_badpartition_detached; > CREATE TABLE table_badpartition PARTITION OF table_parent > FOR VALUES FROM (date) TO (date+1); > ALTER TABLE table_badpartitioneplica identity full; > INSERT INTO table_badpartition (id, date, ...) > SELECT id, date, ... from table_badpartition_detached; > commit; > > This new table was created with the correct columns, the accurate data, and > the > correct indices however the parent index is still listed with indisvalid = > FALSE > and indisready = FALSE. You forgot to ALTER INDEX ... ATTACH PARTITION to turn the invalid index on the partitioned table into a valid index. Yours, Laurenz Albe
Disable script execution in server level when updating via grids
Hello All, Is there a way we can disable grid based updates from the clients in the server? suppose if someone accidentally commits an edit in dbeaver,server shall decline that incoming request. However requests from the query tool should run I have seen some options from the client side. Do we have some options in server side? [image: image.png] Thanks, Arun
