Re: How to log bind values for statements that produce errors
Hmm, actually, this same client executes several queries for that same session. For the first queries, I can clearly see the binds in postgresql.log. Only this third query that is being executed, it produces error and not binds are logged. Also, there may the case, where the third query gets its bind parameter from the second query (as I understood the developer). Regards Raul Kontakt Tom Lane () kirjutas kuupäeval T, 7. september 2021 kell 16:48: > Raul Kaubi writes: > > We have a problem with certain select statement, which produces error > and we would like to know the bind value that is given as parameter. > > I have tried parameters: > > log_min_duration_statement = 0 > > log_parameter_max_length_on_error = -1 > > The other constraint on reporting parameters during error is that they > have to be sent by the client in text not binary mode. I venture that > your client is sending them in binary. > > (The reason for this restriction is to avoid the overhead of converting > binary to text for every query, which we'd have to do in advance of > knowing whether the query will throw an error.) > > regards, tom lane >
How to log bind values for statements that produce errors
H Centos7, postgres 13 We have a problem with certain select statement, which produces error and we would like to know the bind value that is given as parameter. At the moment, the column is bigint type, and probably the bind that goes there is text, in postgresql logfile, the error is following. operator does not exist: bigint = text And it does log this select statement as well, where the bind value is $1. “.. col1 = $1…” I have tried parameters: log_min_duration_statement = 0 log_parameter_max_length_on_error = -1 But still nothing. The first parameter logs binds only for succeeded statements. While the second one logs only this select statement and this error, but no binds are logged. Is there any possibilities to log this bind value to postgresql.log file..? Regards Raul Sent from my iPhone
Re: Determine if postgresql cluster running is primary or not
Ok, Thanks! Raul Kontakt Paul Förster () kirjutas kuupäeval R, 20. november 2020 kell 12:54: > Hi Raul, > > > On 20. Nov, 2020, at 11:45, Raul Kaubi wrote: > > > > Hmm, ok. > > > > But how is this possible..? > > > > when b.sender>0 and c.receiver>0 then > > 'Primary+Replica' > > > > Raul > > this happens for example if you have a primary a and replica b running as > a normal cluster (we use Patroni for automatic failover) and then add > another replica c to the existing replica b, effectively replicating: a => > b => c In this case, b would be the replica of a, but also be the primary > for c. > > It's called cascading replication. > > Cheers, > Paul
Re: Determine if postgresql cluster running is primary or not
Hmm, ok. But how is this possible..? when b.sender>0 and c.receiver>0 then > 'Primary+Replica' Raul Kontakt Paul Förster () kirjutas kuupäeval R, 20. november 2020 kell 12:04: > Hi Raul, > > > On 20. Nov, 2020, at 10:41, Raul Kaubi wrote: > > > > Hi > > > > Thanks. > > Seems like 9.5 does not work. > > > > ERROR: relation "pg_stat_wal_receiver" does not exist > > LINE 20: from pg_stat_wal_receiver > > > > Any ide how to achieve this in 9.5 ? > > > > Raul > > this query is tested to work on 10.x and newer, not 9.x. I don't have 9.x, > so I can't say, sorry. > > Cheers, > Paul
Re: Determine if postgresql cluster running is primary or not
Hi Thanks. Seems like 9.5 does not work. ERROR: relation "pg_stat_wal_receiver" does not exist > LINE 20: from pg_stat_wal_receiver Any ide how to achieve this in 9.5 ? Raul Kontakt Paul Förster () kirjutas kuupäeval R, 20. november 2020 kell 11:29: > Hi Thomas, > > > On 20. Nov, 2020, at 10:03, Thomas Kellerer wrote: > > > > Raul Kaubi schrieb am 20.11.2020 um 09:53: > >> CentOS 7 > >> Postgres 9 to 12 > >> > >> For monitoring purpose, I would like that certain scripts are only run > in primary server. > >> So I am looking ways to universally discover if postgresql cluster that > is running is primary or not. > > > > As the standby will be in constant recovery, you can use > > > > select pg_is_in_recovery(); > > I usually don't recommend using pg_is_in_recovery() only because a > database cluster can be in recovery for other reasons. This is why I always > do the following: > > select distinct > case > when b.sender=0 and c.receiver=0 then > 'Standalone' > when b.sender>0 and c.receiver=0 then > 'Primary' > when b.sender=0 and c.receiver>0 then > 'Replica' > when b.sender>0 and c.receiver>0 then > 'Primary+Replica' > end as pgrole > from > pg_database a, > ( > select count(*) as sender > from pg_stat_replication > ) b, > ( > select count(*) as receiver > from pg_stat_wal_receiver > ) c > where > not a.datistemplate; > > Cheers, > Paul > >
Determine if postgresql cluster running is primary or not
Hi CentOS 7 Postgres 9 to 12 For monitoring purpose, I would like that certain scripts are only run in primary server. So I am looking ways to universally discover if postgresql cluster that is running is primary or not. What would be the best way to achieve this? Regards Raul
Re: Parameter value from (mb/gb) to bytes
Hi
Thanks, you solution works for most of the databases. But now I noticed
that we have single 9.5 version also.
And seems like this function *pg_size_bytes* came from 9.6
# psql -U postgres -Atc "select
> pg_size_bytes(current_setting('shared_buffers'));"
> ERROR: function pg_size_bytes(text) does not exist
> LINE 1: select pg_size_bytes(current_setting('shared_buffers'));
>^
> HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.
Do you perhaps happen to know a way how to get this to work in 9.5 also..?
Thanks again.
Regards
Raul
Kontakt Magnus Hagander () kirjutas kuupäeval K, 14.
oktoober 2020 kell 18:38:
>
>
> On Wed, Oct 14, 2020 at 5:23 PM Tom Lane wrote:
>
>> Magnus Hagander writes:
>> > On Wed, Oct 14, 2020 at 5:10 PM Tom Lane wrote:
>> >> It's fairly annoying that this doesn't work:
>> >> regression=# select pg_size_bytes(setting||' '||unit) from pg_settings
>> >> where name = 'shared_buffers';
>>
>> > Actually thinking though, surely *this* particular case can be spelled
>> as:
>> > SELECT pg_size_bytes(current_setting('shared_buffers'))
>>
>> Yeah, that might be the most recommendable way.
>>
>> > Or if doing it off pg_settings:
>> > SELECT setting::bigint * pg_size_bytes(unit) from pg_settings where
>> > name='shared_buffers'
>>
>> No, because that will fail for any unit other than '8kB', eg
>>
>> regression=# select pg_size_bytes('MB');
>> ERROR: invalid size: "MB"
>>
>
> Right, but it would certainly work for *this* case using pg_asettings, is
> what I meant.
>
> That said, I think it'd then actually be better to teach pg_size_bytes to
> know that "MB" is the same as "1MB" and parse that. That might be something
> that would actually be useful in other cases as well -- basically as a way
> to get conversion units in general. Basically if the string is "unit only"
> then consider that as "1 unit".
>
> --
> Magnus Hagander
> Me: https://www.hagander.net/
Discovering postgres binary directory location
Hi CentOS 7 Postgres 9 to 12 I am looking ways to universally discover postgresql binary directory for monitoring purpose. For example postgres 12, it is: */usr/pgsql-12* So what would be the best way to discover this in specific host. Regards Raul
Re: Parameter value from (mb/gb) to bytes
Hi
Thanks for all the replies.
So at first, I did this:
select (pg_size_bytes(setting) * (select setting from pg_settings where
name = 'block_size')::int) as shared_buffers from pg_settings where name =
'shared_buffers';
But as I understood, that the preferred way would be this, correct..? (at
least, it seems a lot simpler, I agree)
SELECT pg_size_bytes(current_setting('shared_buffers'))
Regards
Raul
Kontakt Tom Lane () kirjutas kuupäeval K, 14. oktoober
2020 kell 18:23:
> Magnus Hagander writes:
> > On Wed, Oct 14, 2020 at 5:10 PM Tom Lane wrote:
> >> It's fairly annoying that this doesn't work:
> >> regression=# select pg_size_bytes(setting||' '||unit) from pg_settings
> >> where name = 'shared_buffers';
>
> > Actually thinking though, surely *this* particular case can be spelled
> as:
> > SELECT pg_size_bytes(current_setting('shared_buffers'))
>
> Yeah, that might be the most recommendable way.
>
> > Or if doing it off pg_settings:
> > SELECT setting::bigint * pg_size_bytes(unit) from pg_settings where
> > name='shared_buffers'
>
> No, because that will fail for any unit other than '8kB', eg
>
> regression=# select pg_size_bytes('MB');
> ERROR: invalid size: "MB"
>
> > I'm not sure having pg_size_bytes() parse "16384 8kB" is reasonable, I
> have
> > a feeling that could lead to a lot of accidental entries giving the wrong
> > results.
>
> Yeah, that's definitely a risk. Given that current_setting() already does
> what's needed, that's probably a better answer.
>
> regards, tom lane
>
>
>
Parameter value from (mb/gb) to bytes
Hi Is there a simple way to dynamically get for example parameter "shared buffers" value (megabytes or gigabytes) to bytes, for monitoring perspective..? At the moment, this gives me value in GB. # psql -U postgres -Atc "show shared_buffers;" 1GB This value may as well be in MB. So I am looking a way to dynamically get the value in bytes. Regards Raul
Re: Error handling: Resume work after error
Oh yeah, I must add new *begin ... exception .. end;* block between. So like this: BEGIN > BEGIN > EXCEPTION when . then .. > END; > > END; At first I did like this: BEGIN . EXCEPTION when . then ..; > END; Raul Kontakt hubert depesz lubaczewski () kirjutas kuupäeval K, 5. veebruar 2020 kell 17:03: > On Wed, Feb 05, 2020 at 04:17:09PM +0200, Raul Kaubi wrote: > > Let's say I want my procedure/function to resume work after specific > error > > ( duplicate_table ). > > Is it possible to resume work after error..? > > EXCEPTION > > > WHEN duplicate_table then ..; > > Sure you can: > > > https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING > > Best regards, > > depesz > >
Error handling: Resume work after error
Hi PostgreSQL 12.1 Let's say I want my procedure/function to resume work after specific error ( duplicate_table ). Is it possible to resume work after error..? EXCEPTION > WHEN duplicate_table then ..; Or is there different approach to achieve that..? Thanks Raul
Re: Declare variable from other variable
Makes sense yeah. Thanks for both of your help. Raul Kontakt hubert depesz lubaczewski () kirjutas kuupäeval K, 5. veebruar 2020 kell 14:50: > On Wed, Feb 05, 2020 at 02:42:42PM +0200, Raul Kaubi wrote: > > Thanks, it worked! > > > > By the way, what does this "**j"* mean there..? (this does not mean > > multiply there?) > > it's normal multiplication. > > Your "j" variable is integer. > > So, '1 month'::interval * j is some number of months. > > And what if, I would like to declare v_to_date also, so that v_to_date is > > always + 1 month compared to v_date_from..? > > v_to_date := v_from_date + '1 month'::interval; ? > > Best regards, > > depesz > >
Re: Declare variable from other variable
Thanks, it worked!
By the way, what does this "**j"* mean there..? (this does not mean
multiply there?)
And what if, I would like to declare v_to_date also, so that v_to_date is
always + 1 month compared to v_date_from..?
-- This one will work, but can this be done simpler..?
v_to_date := (date_trunc('month',current_date)::date + interval '1 month' +
interval '1 month'*j)::date;
Raul
Kontakt Yasin Sari () kirjutas kuupäeval K, 5.
veebruar 2020 kell 14:28:
> On Wed, Feb 5, 2020 at 2:22 PM Raul Kaubi wrote:
>
>>
>> DO $$
>>> DECLARE
>>> v_var integer := 1;
>>> v_from_date date;
>>> BEGIN
>>> for j in 0..v_var LOOP
>>> v_from_date := (date_trunc('month',current_date) + interval 'j
>>> month')::date;
>>> RAISE NOTICE '%', v_from_date;
>>> END LOOP;
>>> END;
>>> $$ LANGUAGE plpgsql;
>>
>>
>>> ERROR: invalid input syntax for type interval: "j month"
>>> LINE 1: ...LECT (date_trunc('month',current_date) + interval 'j
>>> month')...
>>
>>
>>
>>
> If you replace red line with this one it will work:
>
> v_from_date := (date_trunc('month',current_date) + interval '1
> month'*j)::date;
>
Re: Get rid of brackets around variable
Awesome, thanks!
Kontakt Geoff Winkless () kirjutas kuupäeval K, 5.
veebruar 2020 kell 13:11:
>
>
> On Wed, 5 Feb 2020 at 10:48, Raul Kaubi wrote:
>
>>
>> DO $$
>>> DECLARE
>>> cur cursor for
>>> select * from (values('logi_web'), ('logi_taustaprotsess')) as q (col1);
>>> BEGIN
>>> for i in cur LOOP
>>> RAISE NOTICE 'create table %_y2020m01 PARTITION OF % FOR VALUES FROM
>>> (''2019-12-01'') TO (''2020-01-01'')', i, i;
>>> END LOOP;
>>> END;
>>> $$ LANGUAGE plpgsql;
>>>
>>
>> If I execute, this is the output:
>>
>> NOTICE: create table (logi_web)_y2020m01 PARTITION OF (logi_web) FOR
>>> VALUES FROM ('2019-12-01') TO ('2020-01-01')
>>> NOTICE: create table (logi_taustaprotsess)_y2020m01 PARTITION OF
>>> (logi_taustaprotsess) FOR VALUES FROM ('2019-12-01') TO ('2020-01-01')
>>> DO
>>
>>
> You're returning rows from the cursor.
>
> You need to use i.col1 instead of i.
>
> Geoff
>
Declare variable from other variable
Hi
PostgreSQL 12.1
How can I declare another variable from another variable.
Basically from oracle, I can just:
> var1 := 'asda'||var2;
In postgres, I have the following example, I would like to use variable j
to add number of months there.
" interval 'j month')::date; "
DO $$
> DECLARE
> v_var integer := 1;
> v_from_date date;
> BEGIN
> for j in 0..v_var LOOP
> v_from_date := (date_trunc('month',current_date) + interval 'j
> month')::date;
> RAISE NOTICE '%', v_from_date;
> END LOOP;
> END;
> $$ LANGUAGE plpgsql;
> ERROR: invalid input syntax for type interval: "j month"
> LINE 1: ...LECT (date_trunc('month',current_date) + interval 'j month')...
Raul
Get rid of brackets around variable
Hi
PostgreSQL 12.1
I am trying to figure out, how can I get rid of brackets for variable.
Example as follows:
DO $$
> DECLARE
> cur cursor for
> select * from (values('logi_web'), ('logi_taustaprotsess')) as q (col1);
> BEGIN
> for i in cur LOOP
> RAISE NOTICE 'create table %_y2020m01 PARTITION OF % FOR VALUES FROM
> (''2019-12-01'') TO (''2020-01-01'')', i, i;
> END LOOP;
> END;
> $$ LANGUAGE plpgsql;
>
If I execute, this is the output:
NOTICE: create table (logi_web)_y2020m01 PARTITION OF (logi_web) FOR
> VALUES FROM ('2019-12-01') TO ('2020-01-01')
> NOTICE: create table (logi_taustaprotsess)_y2020m01 PARTITION OF
> (logi_taustaprotsess) FOR VALUES FROM ('2019-12-01') TO ('2020-01-01')
> DO
Now I have tried, even if I execute this statement:
EXECUTE 'create table '||i||'_y2020m01 PARTITION OF '||i||' FOR VALUES FROM
> (''2019-12-01'') TO (''2020-01-01'')';
ERROR: syntax error at or near "("
> LINE 1: create table (logi_web)_y2020m01 PARTITION OF (logi_web) FOR...
> ^
> QUERY: create table (logi_web)_y2020m01 PARTITION OF (logi_web) FOR
> VALUES FROM ('2019-12-01') TO ('2020-01-01')
> CONTEXT: PL/pgSQL function inline_code_block line 8 at EXECUTE
>
Then you can see, that it still puts these brackets around variable.
Raul
