I know I can create user steve_test with password testpassword122 as md5 by
doing:
select 'md5'||md5('testpassword122steve_test'); Returns -->
md5eb7e220574bf85096ee99370ad67cbd3
CREATE USER steve_test WITH PASSWORD 'md5eb7e220574bf85096ee99370ad67cbd3';
And then I can login as steve_test wi
Hi,
I have the need to dynamically create a procedure. Here is a simple procedure:
create or replace procedure junk.test_proc()
LANGUAGE plpgsql
AS $$
declare
v_cnt integer := 0;
begin
raise notice 'v_cnt is %', v_cnt;
end $$;
That creates and runs fine.
Here I’m trying to create i
Aurora Postgres version 13.7.
Table definition:
acquisition_channel_db=> \d acquisition_channel.acquired_object
Table
"acquisition_channel.acquired_object"
Column | Type | Collation
| Nu
The query and execution plan are shown below. My question is related to the
result set the optimizer is choosing to build the hash table from. My
understanding is for a hash join you want to build the hash table out of the
smaller result set. If you look at the execution plan below you can se
We have a custom procedure to add/drop partitions in Postgres. It has a main
FOR LOOP to find tables needing to be processed. Inside that FOR LOOP there is
a BEGIN so if a single table gets an error we catch the error in an exception.
At the end of the END for the FOR LOOP it issues a commit.
ments.
Am 29.11.23 um 21:25 schrieb Dirschel, Steve:
>
> I have a question on the execution time of a query and the
> fluctuations I'm seeing. I enabled auto_trace to capture some actual
> executions of a query by an app. Below are 2 executions of it. The
> top one took 1.
I have a question on the execution time of a query and the fluctuations I'm
seeing. I enabled auto_trace to capture some actual executions of a query by
an app. Below are 2 executions of it. The top one took 1.697 milliseconds.
The 2nd one took 31.241 milliseconds. Note the query has hints
. Oracle did 20
regardless if it was count(*) or count(non-indexed-column).
Regards
Steve
-Original Message-
From: Peter Geoghegan
Sent: Thursday, September 7, 2023 8:22 PM
To: David Rowley
Cc: Dirschel, Steve ;
pgsql-general@lists.postgresql.org; Wong, Kam Fook (TR Technology)
Subject
We are in the process of converting from Oracle to Postgres and I have a query
that is using the same index in Postgres as is used in Oracle but in Postgres
the query does 16x more buffer/logical reads. I'd like to understand why. The
query is hitting a partitioned table but to simply things I
Thanks for the reply Jeff. Yes- more of an academic question. Regarding this
part:
Index Cond: ((workflow_id = 1070) AND ((status)::text = ANY
('{NOT_STARTED,PAUSED,PENDING,RUNNING}'::text[])))
Filter: (deleted_millis <= 0)
Buffers: shared hit=24
For this usage, the =ANY is applied
Table definition:
workflow_db=> \d workflow_execution_test
Table "public.workflow_execution_test"
Column | Type | Collation |
Nullable | Default
--+--+---+---
on your reply below in both of these cases the vacuum
should not have been able to cleanup these 10 dead rows. What am I missing
here?
Regards
Steve
> -Original Message-
> From: Christophe Pettus
> Sent: Wednesday, January 25, 2023 4:49 PM
> To: Dirschel, Steve
>
>> When I connect to the database through DBeaver with those 2 default
>> settings changed and find that session in pg_stat_activity column
>> xact_start is populated along with backend_xmin. Those get populated
>> just by logging in.
>As you found out in the log, the driver runs DbEaver run m
Not sure if anyone on this list may have ideas on this or not. I will also try
and find a DBeaver e-mail list and send to that also.
We are running into a problem where users will connect to a database through
DBeaver. I have found if I leave all DBeaver options as default this issue
does not
Table definition:
Table "load.lm_queue"
Column | Type | Collation | Nullable
| Default
++---+--+-
guid
>> On Sep 21, 2022, at 9:32 AM, Dirschel, Steve
>> wrote:
>>
>> On Wed, 2022-09-21 at 14:11 +, Dirschel, Steve wrote:
>>>> We are troubleshooting an issue where autovacuum is not cleaning up a
>>>> table.
>>>> The application usin
On Wed, 2022-09-21 at 14:11 +, Dirschel, Steve wrote:
>> We are troubleshooting an issue where autovacuum is not cleaning up a table.
>> The application using this database runs with autocommit turned off.
>> We can see in pg_stat_activity lots of sessions “idle in transactio
We are troubleshooting an issue where autovacuum is not cleaning up a table.
The application using this database runs with autocommit turned off. We can
see in pg_stat_activity lots of sessions "idle in transaction" even though
those sessions have not executed any DML- they have executed sele
> "Dirschel, Steve" writes:
>> setdatabase | setrole |
>>
We recently upgraded from postgres 12.8 to 14.3. We are running
Aurora Postgres on AWS.
>Aurora Postgres != Postgres. This doesn't seem like something they'd change,
>but we can't rule that out entirely.
>>> Try:
>>> select setting, source, sourcefile, pending_restart from pg_settin
>On 9/6/22 12:07, Dirschel, Steve wrote:
>> We recently upgraded from postgres 12.8 to 14.3. We are running
>> Aurora Postgres on AWS.
>>
>> We have procedures that will make calls to RAISE NOTICE to write out
>> messages if you interactively call t
We recently upgraded from postgres 12.8 to 14.3. We are running Aurora
Postgres on AWS.
We have procedures that will make calls to RAISE NOTICE to write out messages
if you interactively call the procedure through psql. These procedures are
getting called by pg_cron.
Since upgrading these RA
I am fairly new to tuning Postgres queries. I have a long background tuning
Oracle queries.
Posrgres version 10.11
Here is the DDL for the index the query is using:
create index workflow_execution_initial_ui_tabs
on workflow_execution (workflow_id asc, status asc, result asc,
completed_da
We are in the process of migrating from Oracle to Postgres and the following
query does much less work with Oracle vs Postgres.
explain (analyze, buffers)
select favoritegr0_.FAVORITE_GROUP_SID as favorite1_2_, favoritegr0_.CHANGED as
changed2_2_, favoritegr0_.TYPE_DISCRIMINATOR as type_dis3_2_,
Hello,
I'm not sure if this is the correct distribution list for this type of question
but I'll try anyways. We have an app that uses multiple schemas. It will do a
set schema 'schema_name' and execute queries. The queries executed are the
same regardless of the schema the connection set.
I
25 matches
Mail list logo
