Did you wait to see if activity after the pg_restore crossed the
autovacuum thresholds?
--
Adrian Klaver
adrian.kla...@aklaver.com
to, with one set of users (SU)
We have another database, let’s call it LFM, which contains reference
data for some COTS software. I don't know what's in it, we just get
given updates for it in pg_backup binary files, about 2MB each.
Do you mean pg_basebackup, pg_dump or something else?
--
Adrian
kends_y2024w04
db1 schema1 subpartitions backends_y2024w05
The partitioning must be the problem somehow.
--
Adrian Klaver
adrian.kla...@aklaver.com
ables have dead tuples
You have something against providing actual numbers?
The point is there is really nothing to be gained by doing VACUUM FULL
if the dead tuples are some small percentage of the tables.
--
Adrian Klaver
adrian.kla...@aklaver.com
tables, which take up
the most space)
By the way, excuse me if I make a few mistakes (especially when
replying), this is the first time I've used Postgres community support
directly
Le lun. 29 avr. 2024 à 16:37, Adrian Klaver <mailto:adrian.kla...@aklaver.com>> a écrit :
On 4/29/
How much current free space do you have available on the disk?
Did you VACUUM FULL a table at a time or all of them at once?
What are the individual tables sizes?
Le lun. 29 avr. 2024 à 16:19, Adrian Klaver <mailto:adrian.kla...@aklaver.com>> a écrit :
On 4/29/24 06:45,
ve the
database the ability to recycle the vacuumed tuple space.
--
Adrian Klaver
adrian.kla...@aklaver.com
or. However, I'm reaching out to seek clarity on why this
disparity is occurring and to explore potential strategies for
mitigating it (as I want the behaviour to be consistent to regress test
file).
I would say your best option is to file an issue here:
https://github.com/citusdata/postgresql-
On 4/25/24 00:46, Kashif Zeeshan wrote:
I worked with both CentOS/RHEL 7/8.
What does the above mean relative to the original question?
--
Adrian Klaver
adrian.kla...@aklaver.com
are taking pg_basebackup backup.
Backup File size=613G
Backup Running Duration: 8 Hours
--
Thanks & Regards,
Jayakumar.S
+91-9840864439.
--
Adrian Klaver
adrian.kla...@aklaver.com
On 4/22/24 14:54, Atul Kumar wrote:
I mean, Once I change the hostname then how will the socket read the new
hostname ? Does it require a postgres service restart ?
The host name of the machine?
On Tue, Apr 23, 2024 at 3:19 AM Adrian Klaver <mailto:adrian.kla...@aklaver.com>&
ns
what you are really after.
Sorry. Got distracted by the answer.
https://dba.stackexchange.com/questions/338870/when-to-rerun-cluster
<https://dba.stackexchange.com/questions/338870/when-to-rerun-cluster>
--
Adrian Klaver
adrian.kla...@aklaver.com
n using sockets.
* Using a socket name makes parameterizing the hostname easier in
scripts.
--
Adrian Klaver
adrian.kla...@aklaver.com
On 4/22/24 13:59, Ron Johnson wrote:
On Mon, Apr 22, 2024 at 4:21 PM Adrian Klaver <mailto:adrian.kla...@aklaver.com>> wrote:
[snip]
Which gets us back to your comment upstream:
"What the VACUUM docs "should" do, it seems, is suggest CLUSTER on the
PK,
the "host".
Regards.
--
Adrian Klaver
adrian.kla...@aklaver.com
On 4/22/24 12:51, Ron Johnson wrote:
On Mon, Apr 22, 2024 at 3:14 PM Adrian Klaver <mailto:adrian.kla...@aklaver.com>> wrote:
1) If they are already in enough of a PK order that the CLUSTER time vs
VACUUM FULL time would not be material as there is not much or any
comment. I keep on wanting to believe that ALTER
TABLE supports GENERATED expressions, when it only supports GENERATED
IDENTITY:)
--
Adrian Klaver
adrian.kla...@aklaver.com
eleted based on CREATED_ON? I understand the correlation
between CREATED_ON and the PK just not sure why that would necessarily
translate to an on disk order by PK?
--
Adrian Klaver
adrian.kla...@aklaver.com
urrent/sql-createtable.html
GENERATED ALWAYS AS ( generation_expr ) STORED
So:
generated always as (DATE_PART('year', evtdate)::integer) STORED
--
Adrian Klaver
adrian.kla...@aklaver.com
at you want to clean out. Given
the temporary nature of the effects of a CLUSTER under a change load I
don't see why it would be the way to go to clean up a changing table.
That's because the data is already roughly in PK order.
--
Adrian Klaver
adrian.kla...@aklaver.com
On 4/21/24 14:21, Tom Lane wrote:
Adrian Klaver writes:
On 4/21/24 11:20, yudhi s wrote:
So in this case i was wondering if "event trigger" can cause any
additional threat and thus there is no such privilege like "create
trigger" exist in postgres and so it should b
So in this case i was wondering if "event trigger" can cause any
additional threat and thus there is no such privilege like "create
trigger" exist in postgres and so it should be treated cautiously?
An event trigger runs as a superuser and executes a function that in
turn
On 4/21/24 02:35, Lok P wrote:
On Sat, Apr 20, 2024 at 10:02 PM Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:
Have you tried?:
https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT
<https://www.postgresql.org/docs/curren
threat and if any option to
get this logging enabled (which will help us debug performance issues)
at same time addressing the threat too?
I should have added to previous post, if you have access to the database
the security wall has already been breached.
Regards
Lok
--
Adrian Klaver
(which will help us debug performance issues)
at same time addressing the threat too?
Regards
Lok
--
Adrian Klaver
adrian.kla...@aklaver.com
https://www.postgresql.org/docs/11/sql-copy.html
"where option can be one of:
FORMAT format_name
OIDS [ boolean ]
"
And Postgres 11 is ~6 months past EOL.
--
Adrian Klaver
adrian.kla...@aklaver.com
. (Refreshing it from the MySQL server will result in one
that has records that have been added or updated rather than an exact
copy of the one on the production serve
Use postgres_fdw to connect the test bed to the production server?
Mike Nolan
htf...@gmail.com
--
Adrian Klaver
adrian.kla
o the specified capabilities and
information."
--
Adrian Klaver
adrian.kla...@aklaver.com
On 4/16/24 12:39, veem v wrote:
On Sat, 13 Apr 2024 at 21:44, Adrian Klaver <mailto:adrian.kla...@aklaver.com>> wrote:
On 4/13/24 00:03, veem v wrote:
> Thank you Adrian.
>
> So it seems the heavy DML tables will see an impact if having
triggers
em' answer. And there is a difference
between dumping data into a table and then doing an UPGRADE where the
data strings are manipulated by functions.
Anyway, I hope I answered your questions.
Thanks for your help.
On Sunday, April 14th, 2024 at 4:28 PM, Adrian Klaver
wrote:
On 4/14/24 13:18,
that someone else can take over, so everything needs to
be as simple as possible.
--
Adrian Klaver
adrian.kla...@aklaver.com
On 4/14/24 12:22, jack wrote:
Here is an excerpt of /var/log/postgresql/postgresql-16-main.log
Where and how are the CSV files being produced?
What is the database locale?
--
Adrian Klaver
adrian.kla...@aklaver.com
dified) - 1) WHERE LENGTH(category_modified)>1 AND
category_modified LIKE '%-';
Is the above all being done in one script/transaction?
Again what are the table definitions for the tables being copied into
and/or modified?
--
Adrian Klaver
adrian.kla...@aklaver.com
d into.
3) Sample of the data being copied.
4) The error message(s) generated.
5) Database locale
Any help would be greatly appreciated.
Thank you
--
Adrian Klaver
adrian.kla...@aklaver.com
be different in Postgres?
What type of triggers where there in Oracle, per row, per statement or a
mix?
On Thu, 11 Apr 2024 at 22:00, Adrian Klaver <mailto:adrian.kla...@aklaver.com>> wrote:
On 4/11/24 07:31, veem v wrote:
> Hi, We used to use Oracle database in which
updating('create_timestamp') THEN
:new.create_userid := :old.create_userid;
:new.create_timestamp := :old.create_timestamp;
END IF;
:NEW.update_timestamp := systimestamp;
:NEW.update_userid := sys_context('USERENV','SESSION_USER');
END IF;
END;
/
Regards
Veem
-
On 4/10/24 1:31 PM, Tom Lane wrote:
Adrian Klaver writes:
On 4/10/24 12:38, Adnan Dautovic wrote:
By the way, the row count of pg_timezone_names is 385, but I do
not know how that compares to a more standard installation.
On my instance of Postgres 16.2, 1196.
You're probably using
regards,
Adnan Dautovic
--
Adrian Klaver
adrian.kla...@aklaver.com
and inexpensively!
So, recommendation and possibly some consensus would be very much
appreciated.
And apologies if I have taken up too much of your time or have placed
this in the wrong forum.
John
--
Adrian Klaver
adrian.kla...@aklaver.com
15:00-07
1 | 2024-04-01 09:45:00-07
--
Adrian Klaver
adrian.kla...@aklaver.com
On 4/9/24 9:16 AM, Lok P wrote:
On Tue, Apr 9, 2024 at 9:26 PM Adrian Klaver <mailto:adrian.kla...@aklaver.com>> wrote:
On 4/9/24 08:43, Lok P wrote:
> Hi All,
> It's version 15.4 of postgresql database. Every "date/time" data
type
ount of transactions from a table-
transaction_tab and share it with another person/customer who is in the
EST timezone, so basically the transaction has to be shown or displayed
the EST timezone.
What is the datatype for the create_timestamp?
What does SHOW timezone; return on the server?
On 4/9/24 08:12, Thiemo Kellner wrote:
Thanks for taking this up.
Am 09.04.2024 um 17:09 schrieb Adrian Klaver:
On 4/9/24 07:59, Thiemo Kellner wrote:
[Code: 0, SQL State: 0A000] ERROR: References to other databases are
not implemented: pg_catalog.pg_roles.rolname
Position: 298 [Script
cute V_SQL_STATEMENT;
commit;
return;
end;
$body$;
--
Adrian Klaver
adrian.kla...@aklaver.com
hat PostgreSQL version are you using? The feature was introduced
in v11.
How exactly is the publication defined? Perhaps TRUNCATE is excluded.
Yours,
Laurenz Albe
--
Adrian Klaver
adrian.kla...@aklaver.com
should reach out to their tech support.
Thanks!
--
Adrian Klaver
adrian.kla...@aklaver.com
On 4/6/24 13:04, yudhi s wrote:
On Sat, Apr 6, 2024 at 10:25 PM Adrian Klaver <mailto:adrian.kla...@aklaver.com>> wrote:
Your original problem description was:
"Then subsequently these rows will be inserted/updated based on the
delta number of rows that got in
hard rule, then yes up to some point just
replacing the data in mass would be the simplest/fastest method. You
could cut a step out by doing something like TRUNCATE target_tab and
then COPY target_tab FROM 'source.csv' bypassing the INSERT INTO source_tab.
--
Adrian Klaver
adrian.kla...@aklaver.com
On 4/5/24 02:39, Adnan Dautovic wrote:
Dear Adrian,
Adrian Klaver wrote:
Define 'read-only', especially as it applies to the privileges on the
public schema.
I am not quite sure which information you are looking for
exactly. According to this [1], I ran the following query:
WITH "
On 4/4/24 13:42, yudhi s wrote:
On Thu, Apr 4, 2024 at 9:04 PM Adrian Klaver <mailto:adrian.kla...@aklaver.com>> wrote:
On 4/3/24 22:24, yudhi s wrote:
>
> On Thu, Apr 4, 2024 at 10:16 AM Adrian Klaver
mailto:adrian.kla...@aklaver.com>
&g
On 4/3/24 22:24, yudhi s wrote:
On Thu, Apr 4, 2024 at 10:16 AM Adrian Klaver <mailto:adrian.kla...@aklaver.com>> wrote:
On 4/3/24 20:54, yudhi s wrote:
> On Thu, Apr 4, 2024 at 2:41 AM Adrian Klaver
mailto:adrian.kla...@aklaver.com>
> <mailto:adri
or me?
Kind regards,
Adnan Dautovic
--
Adrian Klaver
adrian.kla...@aklaver.com
On 4/3/24 20:54, yudhi s wrote:
On Thu, Apr 4, 2024 at 2:41 AM Adrian Klaver <mailto:adrian.kla...@aklaver.com>> wrote:
On 4/3/24 13:38, yudhi s wrote:
> Hi All,
> It's postgresql database version 15.4. We have a requirement in
which
> we will be ini
records faster in the target database, while making the
system online to the users?
Regards
Yudhi
--
Adrian Klaver
adrian.kla...@aklaver.com
'pandora distrib'?
--
Adrian Klaver
adrian.kla...@aklaver.com
log 1' create_date.
Read:
https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
--
Adrian Klaver
adrian.kla...@aklaver.com
ds,
Venkat
Internal Use - Confidential
-Original Message-----
From: Adrian Klaver
Sent: Wednesday, March 27, 2024 9:32 PM
To: Bandi, Venkataramana - Dell Team ; Greg Sabino
Mullane
Cc: pgsql-general@lists.postgresql.org; Kishore, Nanda - Dell Team
; Alampalli, Kishore
Subject:
out with an access denied message. So I wanted to understand the
cause of this and how we should fix it , such that anybody logging in
through that role can see/fetch the data from the cron and partman
schema tables.
grant select on cron.job to ;
grant select on cron.job_run_details to ;
grant
want to stay with DBeaver post the actual complete error
message here.
Thanks,
Arun
--
Adrian Klaver
adrian.kla...@aklaver.com
.
--
Adrian Klaver
adrian.kla...@aklaver.com
On 3/28/24 07:25, Daniel Gustafsson wrote:
On 28 Mar 2024, at 15:22, Adrian Klaver wrote:
On 3/28/24 04:56, 김명준 wrote:
Hello,
I am deeply fascinated by the powerful features and flexibility of PostgreSQL
and wish to share it with more Korean speakers. I am interested in contributing
being done:
https://babel.postgresql.org/
Translator mailing list:
https://www.postgresql.org/list/pgsql-translators/
Translator Wiki:
https://wiki.postgresql.org/wiki/NLS
Thank you.
--
Adrian Klaver
adrian.kla...@aklaver.com
le True:
rows = cursor.fetch(batch_size)
if not rows:
break
for row in rows:
if row['num'] % 2:
odd += 1
The last is:
plpy_cursor.close()
I don't know how to proceed further.
--
Adrian Klaver
adrian.kla...@aklaver.com
cursor.
I would start with:
def logging(comment):
global database
<...>
--
Adrian Klaver
adrian.kla...@aklaver.com
please do not top post, use either bottom or inline posting per:
https://en.wikipedia.org/wiki/Posting_style
Regards,
Venkat
--
Adrian Klaver
adrian.kla...@aklaver.com
On 3/25/24 00:18, Bandi, Venkataramana - Dell Team wrote:
Hi,
Please find my inline comments for your questions.
Regards,
Venkat
Internal Use - Confidential
-Original Message-
From: Adrian Klaver
Sent: Tuesday, March 19, 2024 9:33 PM
To: Bandi, Venkataramana - Dell Team ; Greg
:3: HINWEIS: materialisierte Sicht
»query_per_task⠒mv« existiert nicht, wird übersprungen
DROP MATERIALIZED VIEW
SELECT 0
REFRESH MATERIALIZED VIEW
COMMENT
COMMIT
COMMIT
--
Adrian Klaver
adrian.kla...@aklaver.com
On 3/24/24 13:58, Thiemo Kellner wrote:
Am 24.03.2024 um 21:50 schrieb Adrian Klaver:
On 3/24/24 13:36, Thiemo Kellner wrote:
It does depending on the order of viewing. Namely if you viewed the
'old' empty MV in the outside session before you dropped/created the
'new' MV and committed
On 3/24/24 13:36, Thiemo Kellner wrote:
Am 24.03.2024 um 21:30 schrieb Adrian Klaver:
On 3/24/24 13:11, Thiemo Kellner wrote:
Confirmed in the same session that created it or in a different session?
Different session, not knowing what that mattered
It does depending on the order of viewing
actually is created (confirmed by being empty).
Confirmed in the same session that created it or in a different session?
I can't find any materialized view in your archive.
Oh sh*. That is the file, I forgot to commit. Please find it attached now.
--
Adrian Klaver
adrian.kla...@aklaver.com
mpty. You might want to have a look at the code attached.
That does not contain the statements mentioned above. Provide a simple
test case as code inline to your reply.
Kind regards
Thiemo
--
Adrian Klaver
adrian.kla...@aklaver.com
ease do not get me wrong. I can totally understand that something
needs to much work to implement. I am just puzzled.
--
Adrian Klaver
adrian.kla...@aklaver.com
and timing needs to be applied before it is run.
On Fri, Mar 22, 2024 at 12:32 PM Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:
On 3/22/24 09:25, Fred Habash wrote:
> Facing an issue where sometimes humans login to a database and
run DDL
> statements causing
. Automatically time out a blocker
2. A metric that shows how many waiters for a blocker?
Thanks
--
Thank you
--
Adrian Klaver
adrian.kla...@aklaver.com
erhaps some feature that I can enable that logs which processes use
these 2 files?
Thanks,
Nick Renders
--
Adrian Klaver
adrian.kla...@aklaver.com
On 3/20/24 15:52, Jeff Ross wrote:
On 3/20/24 16:25, Adrian Klaver wrote:
On 3/20/24 15:18, Jeff Ross wrote:
Greetings!
I built a trigger fired process that copies an "order" from our
production database to our dev database. An order, in this case, is
an initial row from a tab
On 3/20/24 15:52, Jeff Ross wrote:
On 3/20/24 16:25, Adrian Klaver wrote:
On 3/20/24 15:18, Jeff Ross wrote:
Greetings!
I built a trigger fired process that copies an "order" from our
production database to our dev database. An order, in this case, is
an initial row from a tab
a function that uses both plython3u
and psycopg2. I can supply the source code if that will help.
I think that will help, especially the interaction between psycopg2 and
plpython3u.
--
Adrian Klaver
adrian.kla...@aklaver.com
On 3/20/24 13:00, Celia McInnis wrote:
On Wed, Mar 20, 2024 at 2:15 PM Adrian Klaver <mailto:adrian.kla...@aklaver.com>> wrote:
__
On 3/20/24 10:54 AM, Celia McInnis wrote:
Comments below more to sort out the process in my head then anything
else.
fully, but when I then did
select * from tempview:
"
Where the select would have been on the regular view named tempview.
Thanks,
Celia McInnis
On Wed, Mar 20, 2024 at 1:01 PM Adrian Klaver
wrote:
On 3/20/24 09:51, Celia McInnis wrote:
> The view is being used in some web q
.
Have you run EXPLAIN ANALYZE on the problem query?
On Wed, Mar 20, 2024 at 11:46 AM Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:
On 3/20/24 08:39, Celia McInnis wrote:
> Ok, thanks - so I guess that means that if there is both a
temporary and
> a non te
elect from the regular view?
Thanks,
Celia McInnis
--
Adrian Klaver
adrian.kla...@aklaver.com
-connection.html#RUNTIME-CONFIG-CONNECTION-SSL
--
Adrian Klaver
adrian.kla...@aklaver.com
. But you can track exactly what your
application is doing.
Cheers,
Greg
--
Adrian Klaver
adrian.kla...@aklaver.com
NEGATOR = <>,
RESTRICT = eqsel,
JOIN = eqjoinsel,
HASHES, MERGES
);
Thanks,
Rajesh S
--
Adrian Klaver
adrian.kla...@aklaver.com
creating the function
and operator, psql shows the error "operator is only a shell:
character varying = numeric
Your operator has numeric on the left and varchar on the right. But
your query is doing numeric on the RIGHT. Probably want to make a
matching one to cover both cases.
Cheers,
Greg
--
Adrian Klaver
adrian.kla...@aklaver.com
application which is in power builder.
Thanks,
On Fri, 15 Mar, 2024, 11:09 pm Adrian Klaver,
wrote:
On 3/15/24 3:40 AM, vedant patel wrote:
Hello There,
Recently, we've encountered some performance issues with the ODBC
driver provided by Postgres. Upon investigation, we noticed
ternal Use - Confidential
--
Adrian Klaver
adrian.kla...@aklaver.com
or suggestions you
could offer would be immensely helpful in resolving this performance
issue.
This will probably get a better answer quicker over at the ODBC list:
https://www.postgresql.org/list/pgsql-odbc/
Let me know in case of any questions or concerns.
Thanks,
--
Adrian Klaver
auxiliary structures like dblink and views to accommodate for a
"single" query solution. My two dimes.
--
Adrian Klaver
adrian.kla...@aklaver.com
monitor see all the objects of
the cluster? Background is that I was hoping to create a query to spit
out the size of tables in the cluster.
Kind regards
Thiemo
--
Adrian Klaver
adrian.kla...@aklaver.com
or bellasadie@gmail.com
<mailto:bellasadie@gmail.com>
You will find your receipt for this payment in the attached file.
Thank you!
PostgreSQL US
--
Adrian Klaver
adrian.kla...@aklaver.com
On 3/12/24 02:57, Nick Renders wrote:
On 11 Mar 2024, at 16:04, Adrian Klaver wrote:
On 3/11/24 03:11, Nick Renders wrote:
Thank you for your reply Laurenz.
I don't think it is related to any third party security software. We have
several other machines with a similar setup
workaround to this issue?
--
Domenico L.
per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]
--
Adrian Klaver
adrian.kla...@aklaver.com
g? It does not occur with every restore, but it
seems to be related anyway.
Thanks,
Nick Renders
--
Adrian Klaver
adrian.kla...@aklaver.com
On 3/10/24 11:34, sud wrote:
On Sun, Mar 10, 2024 at 11:31 PM Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:
1) The partition will be across one day(24 hours) it is just the times
may confuse people. Per you example 2024-03-07 00:00:00+00 is the same
time as 2024
On 3/10/24 10:51, sud wrote:
On Sun, Mar 10, 2024 at 10:32 PM Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:
On 3/10/24 05:12, sud wrote:
>
> In my example in the first post, I see, if someone connected to a
RDS
> Postgres database and run the c
t_val" is showing as local
timezone only. If we set the timezone to a different value than the
local timezone then it gets updated on the "setting".
Regards
Sud
--
Adrian Klaver
adrian.kla...@aklaver.com
oject a is no longer receiving data and its records
are static. Further assuming there is a PK that you could order by, then
it would seem the way to go would be to delete in batches as determined
by the PK.
--
Adrian Klaver
adrian.kla...@aklaver.com
believer in coincidences, that you reported a problem and
then the problem disappeared.
I now have 418M+ rows in the table that it got stuck on.
:shrug:
Thanks Adrian and Jeff for responding.
Steve
Jeff
--
Adrian Klaver
adrian.kla...@aklaver.com
101 - 200 of 4128 matches
Mail list logo
