atibility with
the time before PostgreSQL had procedures.
Yours,
Laurenz Albe
On Mon, 2024-06-10 at 14:06 +0200, Torsten Förtsch wrote:
> Now, my question is why does a vacuum backend have a backend_xmin? I am just
> curious.
I'd say because it sees a certain snapshot of the database, like all other
statements.
Yours,
Laurenz Albe
On Fri, 2024-06-07 at 07:42 -0700, David G. Johnston wrote:
> On Friday, June 7, 2024, Laurenz Albe wrote:
> > On Fri, 2024-06-07 at 13:54 +, Zwettler Markus (OIZ) wrote:
> > > > Another point to keep in mind is that by default, execute privilege is
> > &g
On Mon, 2024-06-10 at 05:06 -0400, Afa Jamal wrote:
> On Mon, Jun 10, 2024 at 5:06 AM Laurenz Albe wrote:
> > On Sun, 2024-06-09 at 17:46 -0400, Afa Jamal wrote:
> > > how you can help me with lost super password?
> >
> > Are you talking about a PostgreSQL s
On Sun, 2024-06-09 at 17:46 -0400, Afa Jamal wrote:
> how you can help me with lost super password?
Are you talking about a PostgreSQL superuser password or
about the "master password" that the pgAdmin client uses
to encrypt connection information?
Yours,
Laurenz Albe
EXECUTE ON FUNCTION
FROM PUBLIC;
Yours,
Laurenz Albe
l get an
error.
So you typically won't be able to attach an existing table as a hash partition.
To convert an existing table into a hash partitioned table, you have to
- create a new, empty partitioned table with *all* its partitions
- transfer the data with "INSERT INTO hash_part_tab SELECT * FROM tab"
Yours,
Laurenz Albe
PostgreSQL provides
binary packages for the distributions that a packager cares about.
If Devrim decides that he doesn't want to build packages for v16
for a crummy old CentOS release, that's his choice.
Yours,
Laurenz Albe
50001006B
That looks like you have "archive_mode = always", and "archive_command" writes
back to the archive. Don't do that.
Yours,
Laurenz Albe
uot;max_standby_streaming_delay" To
> -1.
> Which means unlimited lag.
There should never be a restart unless you perform one or the standby crashes.
If you mean that you want to avoid a crash caused by a full disk on the standby,
the answer is probably "no". Make sure that you have enough disk space and
use monitoring.
Yours,
Laurenz Albe
his database?
> Postgres Version 10.7 on Linux(Ubuntu).
Perhaps, but you should hire an expert if the data are important for you.
Yours,
Laurenz Albe
doubt.
Still I think that your main trouble are long-running transactions.
They will always give you trouble on a busy PostgreSQL database.
You should avoid them.
Yours,
Laurenz Albe
"hot_standby_feedback" off everywhere.
- "max_standby_streaming_delay" should be -1 on the reporting standby and very
low or 0 on the HA standby. It doesn't matter on the primary.
- "statement_timeout" should be way lower on the first two nodes.
- "idle_in_transaction_session_timeout" is good.
- I would leave "autovacuum_freeze_max_age" at the default setting but 100
million
is ok too.
Yours,
Laurenz Albe
hat sometimes cause the xmin to hang back for a
> while, preventing
> vacuums from helping.
>
> All of that said, I would rather Postgres choose the right index than spend a
> lot of time optimizing
> vacuums.
I understand your pain, but your use case is somewhat unusual.
What I would consider in your place is
a) running an explicit VACUUM after you delete lots of rows or
b) using partitioning to get rid of old data
I don't know how the PostgreSQL optimizer could be improved to take dead rows
into account.
Yours,
Laurenz Albe
ary filenames from any OS in
> any character-set within the same column).
That will only work fine as long as everybody uses the same encoding,
or everybody uses only ASCII characters, or everybody works only on
their own data.
Yours,
Laurenz Albe
On Mon, 2024-05-27 at 12:48 +0200, Peter wrote:
> On Mon, May 27, 2024 at 11:25:47AM +0200, Laurenz Albe wrote:
> ! On Sat, 2024-05-25 at 12:51 +0200, Peter wrote:
> ! > I just found Autovacuum run for 6 hours on a 8 GB table, VACUUM query
> ! > doesnt cancel, cluster doesn'
On Mon, 2024-05-27 at 11:33 +0200, Andreas Joseph Krogh wrote:
> På mandag 27. mai 2024 kl. 11:10:10, skrev Laurenz Albe
> :
> > On Mon, 2024-05-27 at 09:33 +0200, Andreas Joseph Krogh wrote:
> > > I tried:
> > >
> > > REVOKE SELEC
from the "amcheck"
extension. If that reports a problem, rebuild the index.
Of course, as always, try to figure out how that could happen.
Apart from hardware problems, one frequent cause is upgrading glibc
(if the index on a string column or expression).
Yours,
Laurenz Albe
ay set as it is like 14 sec. Let me know your
> > thoughts.
>
You cannot have it.
Let me repeat: you cannot have it.
The only way you can have no delay in replication AND no canceled queries is
if you use two different standby servers with different settings for
"max_standby_streaming_delay". One of the server is for HA, the other for
your long-running queries.
Yours,
Laurenz Albe
quot;pg_read_all_data".
Yours,
Laurenz Albe
On Thu, 2024-05-23 at 11:06 -0500, Skorpeo Skorpeo wrote:
> I see people are big fans of json here.
PostgreSQL's JSON support is great.
But way too often people use it in the wrong way.
Yours,
Laurenz Albe
k taken
by the INSERT on the referenced row.
Without the unique constraint, there would be no lock.
Yours,
Laurenz Albe
On Thu, 2024-05-23 at 18:15 +0530, sud wrote:
> On Thu, May 23, 2024 at 1:45 PM Laurenz Albe wrote:
> > If a long running query on the standby influences the primary, that means
> > that
> > you have "hot_standby_feedback" set to "on".
standby influences the primary, that means that
you have "hot_standby_feedback" set to "on". Set it to "off".
Yours,
Laurenz Albe
he word "collection" makes me worry. Perhaps this article can give
you some ideas:
https://www.cybertec-postgresql.com/en/json-postgresql-how-to-use-it-right/
Yours,
Laurenz Albe
on_timeout" to 5minutes, even on all the
> prod and non prod databases, to restrict the long running transactions/queries
> and avoid such issues in future. Correct me if I'm wrong.
That looks right.
One thing you could consider is running the long-running queries on a standby
server. Rep
system usage: CPU: user: 151.07 s, system: 638.29 s, elapsed:
> 1122.92 s
Strange. Could you run
VACUUM (VERBOSE, FREEZE) public.log_entry_20240510;
and show the result? Perhaps that contains some clues.
Yours,
Laurenz Albe
ps. This means major rewrite of my backup procedure. Thanks for pointing
> this out. I will likely just switch to pg_basebackup. What I have is
> something old and gnarly from the days of psql version 8.
You could try https://github.com/cybertec-postgresql/safe-backup
to make the transition smoother.
Yours,
Laurenz Albe
On Tue, 2024-05-21 at 12:49 +0200, Sašo Gantar wrote:
> thanks for the info, but is there any solution, given that it's system tables?
We still don't know the query.
Yours,
Laurenz Albe
On Mon, 2024-05-20 at 13:08 +0200, Sašo Gantar wrote:
[execution plan without query text or explanation]
The time is lost here:
-> WindowAgg (cost=310.01..358.34 rows=537 width=888) (actual
time=0.057..19.955 rows=473 loops=401)
Buffers: shared hit=1710825
Yours,
Laurenz Albe
_inc(bar_times) THEN '[' ELSE '(' END ||
CASE WHEN upper_inc(bar_times) THEN ']' ELSE ')' END
)
WHERE ...
Yours,
Laurenz Albe
be queued
> for execution.
>
> I would like to know if we have any email discussions regarding this issue.
I am not aware of any, but search here:
https://www.postgresql.org/search/?m=1
Yours,
Laurenz Albe
On Wed, 2024-05-15 at 15:17 +0300, Danut Soare wrote:
> The difference was less than 32 except one sequence with difference equal to
> 32
>
> The cache used for sequence creation was 1.
Great. Then everything is working as expected.
Yours,
Laurenz Albe
sequence moved backward, that would be a bug. If it moves forward,
that is to be expected and OK.
Yours,
Laurenz Albe
e your application to speak
the PostgreSQL protocol.
There are other tools to migrate to PostgreSQL, for example
https://github.com/fljdin/mssql_migrator
Yours,
Laurenz Albe
r a way to make free space (given
> back to the OS)
If you don't have enough disk space for a VACUUM (FULL), your only option is
pg_dump / DROP DATABASE / CREATE DATABASE / restore.
Yours,
Laurenz Albe
ot;U" is USAGE).
> GRANT USAGE, ALTER ON SEQUENCE mqa_flfo_cstr_id_seq TO shc_uadmin;
> ERROR: unrecognized privilege type "alter" SQL state: 42601
There is no ALTER privilege. Try
GRANT UPDATE ON SEQUENCE mqa_flfo_cstr_id_seq, mqa_ffp_nval_id_seq TO
shc_uadmin;
Yours,
Laurenz Albe
On Thu, 2024-04-11 at 15:57 +0300, ilya Basin wrote:
> Is there some complexity limit after which the planner starts acting dumb?
Yes, "join_collapse_limit" and "from_collapse_limit".
You can try increasing them.
Yours,
Laurenz Albe
ulty setting without looking into the postmaster's log for
> warning messages. The system wouldn't get in your face about it
> until you did a postmaster restart.
An alternative to looking at the log file is to
SELECT * FROM pg_file_settings WHERE error IS NOT NULL;
after you reload.
Yours,
Laurenz Albe
PostgreSQL version are you using? The feature was introduced in v11.
How exactly is the publication defined? Perhaps TRUNCATE is excluded.
Yours,
Laurenz Albe
o do so? Thank you! :)
You would send a patch against the "master" branch to the pgsql-docs list for
that.
Yours,
Laurenz Albe
On Tue, 2024-04-02 at 01:41 +0530, sud wrote:
> On Thu, Mar 21, 2024 at 6:18 PM Laurenz Albe wrote:
> > > [create some partitions, then drop a partition of the referenced table]
> > >
> > > SQL Error [P0001]: ERROR: cannot drop table
> > > schema1.test_par
annot add an integer to a timestamp.
What is the supposed meaning of that addition?
Yours,
Laurenz Albe
On Thu, 2024-03-28 at 17:17 +0100, Emond Papegaaij wrote:
> Op do 28 mrt 2024 om 16:21 schreef Laurenz Albe :
> > On Thu, 2024-03-28 at 15:52 +0100, Emond Papegaaij wrote:
> > > pg_rewind: source and target cluster are on the same timeline pg_rewind:
> > > no rewind
preferably forcing
> the new
>timeline to be started?
This must be the problem addressed by commit 009746 [1].
You'd have to upgrade to PostgreSQL v16, which would be a good idea anyway,
given
that you are running v12.
A temporary workaround could be to explicitly trigger a checkpoint
olumn nullable before?
Perhaps you'd want that, but you are one of the few who do.
You'll have to get used to the way it is.
Yours,
Laurenz Albe
find the exact matching parent partitions
> so as to create the foreign keys one to one
I don't see what's so hard about it. When the time is there to create
a new partition, create a partition for both the parent and the child
table and define a foreign key between them.
Yours,
Laurenz Albe
ithout much hassle.
I understand.
But do you want to pursue a way that is not working well, just because
the tool you chose cannot handle it?
Yours,
Laurenz Albe
enario while maintaining the foreign key intact?
I told you: don't do it.
Instead, use foreign keys between the partitions.
Yours,
Laurenz Albe
On Fri, 2024-03-22 at 16:07 +0530, Vijaykumar Jain wrote:
> On Fri, 22 Mar 2024 at 15:39, Laurenz Albe wrote:
> > On Fri, 2024-03-22 at 13:41 +0530, Daulat wrote:
> > > We are unable to take the backup of our database. While taking backup we
> > > are getting the sam
|
That's normal. Every transaction has an exclusive lock on its own transaction
ID.
Yours,
Laurenz Albe
and take a backup of all the files in the data
directory.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ps that data corruption was caused by a bug that
is already fixed.
Upgrading won't get rid of the error though (I think).
The seasy way is to dump the database and restore it to a new database.
Yours,
Laurenz Albe
ed
> cron job schedule or has to be done any other way manually ?
I don't know the capabilities of partmen, but I would be surprised if
it could automatically create foreign keys on the partitions.
Yours,
Laurenz Albe
between the partitioned
tables, but between the individual table partitions. That should be easy if
you have the same partition boundaries for both.
Then you can simply drop a partition from both tables at the same time.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
d and FirstNormalObjectId
* available for automatic assignment during initdb, while ensuring they
* will never conflict with user-assigned OIDs.
*/16384
Object IDs are forced to be 16384 or above "after normal postmaster start".
Yours,
Laurenz Albe
Planning Time: 0.062 ms
> Execution Time: 5049.131 ms
Your problem are probably the "Heap Fetches: 16840".
If you VACUUM the table, the performance should improve.
The best solution is to make sure that autovacuum processes that table more
often:
ALTER TABLE public.products_inventory_delta SET
(autovacuum_vacuum_scale_factor = 0.01);
Yours,
Laurenz Albe
thod.
Yours,
Laurenz Albe
column i_id set (n_distinct_inherited=-0.0002 );
>
> I then ran
> analyze xxx;
> but saw no change in pg_stats.n_distinct for xxx.
Well, it works on my PostgreSQL v16.
Yours,
Laurenz Albe
gher n_distinct values but still far from accurate
> and my manually-set values still did not appear.
>
> How can I get these values to take effect?
If it is a partitioned table, set "n_distinct_inherited" on the column,
then run ANALYZE.
Yours,
Laurenz Albe
r B is
> throwing errors in the log again.
>
> Any idea why this is happening? It does not occur with every restore, but it
> seems to be related anyway.
I don't know Barman, but with that incomplete description anybody will have
problems determining the cause. For example, how are A and B connected?
Yours,
Laurenz Albe
use can only produce this SQL:
> `SELECT ... WHERE (key=$1 or key=$2 or ...)`.
> Surprisingly, PostgreSQL planner treats these two SQLs differently:
>
> Is it possible to configure PostgreSQL 12.16 to treat the second query as the
> first?
No, that is currently not possible.
Yours,
Laurenz Albe
nnect.html#LIBPQ-KEEPALIVES-IDLE
Yours,
Laurenz Albe
yid which we can refer to see past vs current plans difference
> and identify
> such issues quickly and fix it?
Not that I know of.
Yours,
Laurenz Albe
n the column?
No.
Yours,
Laurenz Albe
lect statistics on the
partitions, but not the partitioned table itself)
2. after you create an index on an expression (otherwise you have to
wait until autoanalyze runs to get statistics on the indexed
expression)
Yours,
Laurenz Albe
On Mon, 2024-03-04 at 00:04 +0100, Peter J. Holzer wrote:
> On 2024-02-28 20:30:35 +0100, Dimitrios Apostolou wrote:
> > On Wed, 28 Feb 2024, Laurenz Albe wrote:
> > > On Wed, 2024-02-28 at 12:16 +0100, Dimitrios Apostolou wrote:
> > > > So what is the moral of t
On Fri, 2024-03-01 at 08:14 -0800, Adrian Klaver wrote:
> [pointers to the documentation]
I know. I was not searching for help with PostgreSQL,
I was trying to point out the surprising behavior of
"character" as a suggestion for the talk.
Yours,
Laurenz Albe
count and cost of execution plan
steps. You can never use them as proof.
Yours,
Laurenz Albe
;,
and in combination with "timestamp" a great way to let the database handle
the difficult task of time zone conversion for you.
Yours,
Laurenz Albe
There is no entity that would fill in your forms.
Yours,
Laurenz Albe
y want a "SELECT count(*) FROM tab" to consider an index-only
scan on a small index, right? I'm not saying that it is a large
overhead, but if you actually have dozens of indexes, it can
make processing slower.
Yours,
Laurenz Albe
On Thu, 2024-02-29 at 10:55 +0100, Dominique Devienne wrote:
> On Thu, Feb 29, 2024 at 10:03 AM Laurenz Albe
> wrote:
>
> Honestly, I'm not sure why supporting the non-stored variant of generated
> columns is so controversial...
>
> > I am sure there are some use cases
he SQL standard.
Perhaps it would be good to warn people about using data types like
"character", "time with time zone" and "money".
Yours,
Laurenz Albe
You could use conditional indexes, but then you have to make sure that
the optimizer knows it can use these indexes.
The sum of the sizes of these indexes shouldn't exceed the size of an
unconditional index by much, but they would still be more expensive:
each statement would have to look through all the indexes to decide
which ones it can use and which ones not.
Yours,
Laurenz Albe
about such things, start reading the source.
The object ID is immutable, and initially the filenode is the save,
but it changes whenever the table is rewritten (TRUNCATE, ALTER TABLE,
VACUUM (FULL), ...).
Yours,
Laurenz Albe
support FKs and indexes on those too.
> Would be great to have feature parity on this particular point, eventually.
Isn't almost all of that functionality covered by a view?
Yours,
Laurenz Albe
rash, or that it provided some tools for the
> job. (I tried VACUUM FULL on the table, but the orphaned files did not go
> away).
That is not so simple... Also, it would slow down crash recovery.
But I agree that it would be nice to have a tool that reports or
cleans up orphaned files.
Yours,
Laurenz Albe
g to the table created with
CREATE TABLE huge AS SELECT ...
then you can do that.
If you are not 100% certain, go the safe way and use dump/restore
to a new database. Then DROP DATABASE on the old database, and
all orphaned files will be gone.
Yours,
Laurenz Albe
pens, the server is no longer accessible, and we need to restart
> the service (pg_ctl restart).
> Once restarted, Popstgres runs fine again for a couple of days.
>
> We are running PostgreSQL 16.2 on macOS 14.3.1.
Perhaps that is some kind of virus checker or something else that locks files.
Yours,
Laurenz Albe
E is twofold:
1) The column only has to be stored in the leaf pages, since it is not used for
searching.
That makes the intermediate index entries smaller, which causes a wider
fan-out of the
index, which in turn makes the tree shallower and hence faster to search.
2) It is an implicit documentation that the column is not to be used for
searching.
Yours,
Laurenz Albe
On Thu, 2024-02-22 at 19:35 +0530, Sasmit Utkarsh wrote:
> Is there any configuration/query that can be checked to verify if
> "shc_uadmin" has the correct path set?
The SQL statement "SHOW search_path" would return the current setting.
But look at the error mess
CTION");
> 3563 res = PQexec(conn,"ROLLBACK
> TRANSACTION");
> 3564 PQclear(res);
> 3565 return 0;
> 3566 }
To debug that, get the actual error message using PQerrorMessage().
That should tell you what is going on.
Perhaps the sequence is not on your "search_path", and you should
qualify the name with the schema.
Yours,
Laurenz Albe
On Thu, 2024-02-22 at 16:13 +0530, Sasmit Utkarsh wrote:
> On Thu, Feb 22, 2024 at 2:20 PM Laurenz Albe wrote:
> > On Thu, 2024-02-22 at 14:01 +0530, Sasmit Utkarsh wrote:
> > > ==3343433==ERROR: AddressSanitizer: heap-buffer-overflow on address
> > > 0xf337ba80 at pc
2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x1205e)
> #3 0xf78c05a2 in PQexecPrepared
> (/nix/store/glkv3qv4y64xigwpjcwflp3ni2anjma0-postgresql-14.9-lib/lib/libpq.so.5+0x145a2)
Perhaps you forgot to terminate a string with '\0'.
Yours,
Laurenz Albe
er. That may or may not be a problem for you, but if you could pay
the price of the extra indexes, you will certainly be able to pay the
price of advanced statistics.
You could also consider the option to drop indexes and see if somebody
complains, or watch out for changes in pg_stat_statements.
Yours,
Laurenz Albe
ting thoughts, let me add that I enjoy PostgreSQL more than Oracle. And
> libpq way more than OCI.
That goes without saying. I have never seen an API as terrible as OCI.
As an aside, IBM has re-implemented the OCI API for DB2. I am sure that led to
serial
quitting and mental illness among IBM's developers.
Yours,
Laurenz Albe
andom, or if it was not, the
> order would have been explained in the docs?
Transactions queue behind a lock, and they get the lock in a "first come,
first served" order.
Yours,
Laurenz Albe
ink there is a way to enforce that. Your application code
has to do the right thing.
Yours,
Laurenz Albe
ns (skipping over previous columns and
padding) are relevant for performance, they are often a micro-optimization
that you won't be able to measure, and you shouldn't lose too much sleep
over them.
>
> > So there's a bit of a tradeoff between minimizing alignment overhead and
> > arranging columns for fastest access.
Precisely.
Yours,
Laurenz Albe
ing client.
Also, what is the error message
1) on the client side
2) in the PostgreSQL server log
Yours,
Laurenz Albe
ontext of switchover :^/
Yours,
Laurenz Albe
.
Either use
pg_ctl promote -D /path/to/datadir
on the command line or
SELECT pg_promote();
in SQL.
Yours,
Laurenz Albe
time to replay all the changes
(if the "2 days delay" you mention are set in "recovery_min_apply_delay").
What do you get for
SELECT pg_is_in_recovery();
Yours,
Laurenz Albe
On Tue, 2024-02-06 at 00:26 +0530, veem v wrote:
> On Mon, 5 Feb 2024 at 17:52, Laurenz Albe wrote:
> > On Mon, 2024-02-05 at 03:09 +0530, veem v wrote:
> > > In postgresql, Is it possible to partition an existing nonpartitioned
> > > table having data
> > >
ster? Similarly merging
> multiple partitions
> to one partition or splitting a single partition into multiple partitions?
There is no way to do that.
Yours,
Laurenz Albe
On Fri, 2024-02-02 at 10:14 +0530, veem v wrote:
> On Fri, 2 Feb 2024 at 02:43, Laurenz Albe wrote:
> > On Fri, 2024-02-02 at 02:27 +0530, veem v wrote:
> > > We have the below query which is running for ~45 seconds on postgres
> > > aurora reader instance.
> &
atistics on
SCHEMA1.TAB4 are either out of date or not detailed enough,
which makes PostgreSQL underestimate the result size.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ta corruption, and you should restore your backup.
Yours,
Laurenz Albe
sized as a quarter of the available RAM and
define enough huge pages on the Linux kernel to fit shared buffers.
There are no tuning options for evicting buffers, and there cannot be any
fragmentation.
Yours,
Laurenz Albe
1 - 100 of 1014 matches
Mail list logo
