Re: Postgres Specific issue

No ..... sorry ... live in sequim.

I *think* if I can get the value of the recursive call $1 parameter - I can
reverse engineer it. I set log_statement_duration=0 - but still doesnt
capture in logs the value for $1. Captures statement but not val.
Any ideas on what to set to get that.

[image: image.png]

On Wed, Apr 3, 2024 at 3:18 PM Jeremy Schneider <schneider(at)ardentperf(dot)com>
wrote:

> Are you going to be at the meetup tomorrow night? I don't think I'll have
> a quick answer by tomorrow but maybe we can make some progress on it.
>
> -Jeremy
>
>
> On Tue, Apr 2, 2024 at 9:58 AM John Fak <johnfak75(at)gmail(dot)com> wrote:
>
>> Hi all.
>> Oracle DBA here - learning postgres.
>> I have a specific postgres issue with pg plan hints not working - and
>> unsure why (well its because the normalzied form isnt correct but not sure
>> what correct forms should be exactly).
>>
>> Is there someone that would be interested in discussing this with me ?
>>
>> This is what the raw SQL looks like from pg_stats_staments - I've tried
>> 4-5 different versions of normalized text into the pg_plan_hint table ...
>> with tracing I can see the parse time is trying to find it in table but 0
>> rows.
>>
>>
>> "(
>> select
>> registration_date,
>> to_char(registration_date, $17)::VARCHAR as x_axis_date,
>> to_char(registration_date, upper(adminDateFormat))::VARCHAR as
>> formatted_registration_date,
>> count(distinct lead_email) as registered_leads
>> from (
>> select
>> DATE_TRUNC($18,min(eu.create_timestamp) AT TIME ZONE $19 AT TIME ZONE
>> adminTZ) as registration_date,
>> -- DATE_TRUNC('DAY', TIMEZONE(adminTZ, TIMEZONE('PST',
>> min(eu.create_timestamp)))) as registration_date,
>> dl.email as lead_email
>> from
>> client_hierarchy ch
>> join dw_lead dl on dl.client_id = ch.sub_client_id
>> join dw_lead_user dlu on dlu.lead_id = dl.lead_id
>> join event e on dlu.event_id = e.event_id
>> join event_user eu on eu.event_user_id = dlu.event_user_id and
>> eu.event_id = dlu.event_id
>> where
>> ch.client_id = clientId::BIGINT
>> and dl.lead_update_timestamp between startDate and endDate -- this will
>> discard leads out of the selected time range
>> -- and dl.email not like '%deleted' -- this should not be needed, deleted
>> users are removed
>> and eu.create_timestamp between startDate and endDate
>> /*and e.event_profile_id <> experienceProfileId*/
>> and e.is_active = $20
>> and eu.is_deleted = $21
>> -- Exclude Golive Data
>> and e.event_profile_id not in (lighthouseProfileId,
>> lighthouseSessionProfileId)
>> -- Excluding Top-Level Overall Recurring Webcasts
>> AND NOT EXISTS (
>> SELECT $22
>> FROM recurring_webcast_metadata rwm
>> WHERE rwm.event_id = e.event_id
>> )
>> group by
>> dl.email
>> ) as leads_trend
>> group by
>> registration_date
>> order by
>> registration_date asc
>> )"
>>
>>
>> Values above like adminTZ and
>> lighthouseProfileId/lighthouseSessionProfileId are also runtime parameters
>> passed to the function.
>>
>>
>>
>>
>
> --
> http://about.me/jeremy_schneider
>