ms";
> PreparedStatement statement = connection.prepareStatement(query);
> ResultSet resultSet = statement.executeQuery();
You should ask the JDBC driver mailing list.
As a first measure, try upgrading to 42.6.0 and see if the error persists.
Yours,
Laurenz Albe
t the log will certainly have the complete
statement.
Yours,
Laurenz Albe
t from each other. So PostgreSQL logs
all statements that are DDL *OR* that take longer than a millisecond,
rather than all statements that are DDL *AND* take longer than a
millisecond, as you seem to expect.
Yours,
Laurenz Albe
type has to be an array type...
For example, here is the definition of "cardinality()", which takes "anyarray"
as argument:
Datum
array_cardinality(PG_FUNCTION_ARGS)
{
AnyArrayType *v = PG_GETARG_ANY_ARRAY_P(0);
PG_RETURN_INT32(ArrayGetNItems(AARR_NDIM(v), AARR_DIMS(v)));
}
Yours,
Laurenz Albe
lkind = 'r'
ORDER BY age(relfrozenxid) DESC;
And VACUUM the tables (FREEZE is not necessary) starting with the top ones
on the list.
Upgrade as soon as possible. Not only are you missing out on tons of fixed
bugs, but VACUUM performance and smartness has greatly improved since 9.5.
Yours,
Laurenz Albe
,
which probably requires deeper knowledge of the application.
Yours,
Laurenz Albe
On Tue, 2023-10-03 at 12:33 +1100, rob stone wrote:
> Would running CLUSTER on the table use the new parameters for the re-
> write?
No, as far as I know.
You'd need something like
-- rewrite all tuples
UPDATE tab SET id = id;
-- get rid of the bloat
VACUUM (FULL) tab;
You
ompression.
If it finds that the data grew after compression, it will discard the
compressed value
and continue with the original value. To avoid that useless compression
attempt,
you should use STORAGE EXTERNAL in such cases.
Yours,
Laurenz Albe
ussion is here:
https://www.postgresql.org/message-id/flat/ca+q6zcwtubt_sxj0v6hy6ez89uv5wug5aefpe_9n0jr3vwn...@mail.gmail.com
You could comment on that patch or review it. Useful reviews and supporting
comments help move the patch forward. That would best serve your interests.
Yours,
Laurenz Albe
u please advise us?
There are no official releases of tds_fdw for Windows. But in this issue
(https://github.com/tds-fdw/tds_fdw/issues/53) somebody claims to have build the
extension on Windows. You could try the same.
Yours,
Laurenz Albe
icularly useful for databases that make extensive use of multi-byte
> character
> sets like UTF-8.
Wouldn't it be a good solution if we promote the #define to a configure option,
like "./configure --identifier-length-limit=256"?
Yours,
Laurenz Albe
ange the
storage format and break pg_upgrade?
Yours,
Laurenz Albe
ant to do that, if all that you have to do is specify
"recovery_target = 'immediate'" to recover to the end of the backup?
Yours,
Laurenz Albe
a partition of a table if a
foreign key points to it.
Create the foreign key constraints between the partitions instead.
Yours,
Laurenz Albe
ently).
That's about it.
Yours,
Laurenz Albe
On Wed, 2023-10-11 at 22:15 +0530, Atul Kumar wrote:
> I need to log all wal files that are getting transferred to all standby
> servers in the postgresql error logs.
WAL files are not transferred to standby servers...
WAL is streamed, so what would you want to log?
Yours,
Laurenz Albe
reamed has been replayed
> on standby.
>
> I need to keep a track of these wal files in db error logs.
I don't think you can do that, and it seems like a strange requirement to me.
Instead, you should monitor pg_stat_replication.
Yours,
Laurenz Albe
eleases, but it could be
a basis for you to work upon.
My big question for me is: Why on earth would you want to run .NET code inside a
database? Why not run it on the client?
Yours,
Laurenz Albe
On Thu, 2023-10-12 at 17:56 +0300, Олег Самойлов wrote:
> 11.10.2023, 14:20, "Laurenz Albe" :
> > On Wed, 2023-10-11 at 14:08 +0300, Олег Самойлов wrote:
> > > There are two common practice to drop partition from partitioned table:
> > > just drop or deta
hem.
Yes, you'll have to rewrite them.
Yours,
Laurenz Albe
what tables on the server
> they correspond to) of existing foreign tables?
\d+
> (May I ask to be cc'd since I'm not subscribed to the list?)
But please reply to the list.
Yours,
Laurenz Albe
an make an interesting talk.
Yours,
Laurenz Albe
he issue that the number of
> > > > rows in some of the above tables has increased. Is this possible?
> >
>
> no, reindex will not add rows to the table.
But if the indexes were corrupted before the REINDEX, it is possible that
a query that didn't find a result before the REINDEX can find one afterwards.
Yours,
Laurenz Albe
t=# explain analyze select * from test where ((v1 + v2) % 10) = 0 and v1 =
> 100 and v2 = 100;
> [partition pruning]
Yes, you only get partition pruning if the WHERE clause contains a comparison
with
the partitioning key. There is no way around that.
Yours,
Laurenz Albe
updated or deleted in a current transaction, row locked by a
> SELECT FOR UPDATE, and perhaps others I don't remember right now.
INSERT ... ON CONFLICT could also cause this.
As long as the transaction corresponding to the "xid" is marked as "aborted",
that number is not valid and is treated like a 0.
Yours,
Laurenz Albe
ond.
The network latency might well be a killer.
Use pgBouncer with transaction mode pooling.
Yours,
Laurenz Albe
On Mon, 2023-10-30 at 08:59 -0700, David Ventimiglia wrote:
> On Mon, Oct 30, 2023 at 8:46 AM Laurenz Albe wrote:
> > On Mon, 2023-10-30 at 08:05 -0700, David Ventimiglia wrote:
> > > Can someone help me develop a good mental model for estimating PostgreSQL
> > > thro
rst!
VACUUM won't modify "xmax", nor will it remove the row.
Yours,
Laurenz Albe
store (which would be run on the
> RHEL 8 VM).
Test, test, test. Theoretical considerations are pretty worthless, and it
is easy to measure that.
Yours,
Laurenz Albe
s (PID 16024) was terminated by signal 9: Killed
DETAIL: Failed process was running: SELECT ...
It is not certain, but often that statement is the one that used up
all that memory. At least it is a starting point for your investigation.
Yours,
Laurenz Albe
1. a prepared transaction (check pg_prepared_xacts)
2. a long-running query on a standby, and "hot_standby_feedback = on"
Yours,
Laurenz Albe
ROM xx LIMIT 1) FROM aa;
ERROR: column "aa.a" must appear in the GROUP BY clause or be used in an
aggregate function
LINE 1: SELECT a, (SELECT sum(a) FROM xx LIMIT 1) FROM aa;
^
I think that the optimizer is going astray here...
But perhaps I am missing something
On Wed, 2023-11-08 at 16:36 -0500, Tom Lane wrote:
> Laurenz Albe writes:
> > I found this in a blog
> > (https://buttondown.email/jaffray/archive/sql-scoping-is-surprisingly-subtle-and-semantic/):
> > CREATE TABLE aa (a INT);
> > INSERT INTO aa VALUES (1), (2), (3
" column
in the "pg_stat_activity" line above and see what that process is doing.
I wouldn't worry about a parellel worker in that state, as long as there
is no problem with the parallel leader.
Yours,
Laurenz Albe
e
that does not achieve anything except slowing down the query.
Remove the DISTINCT.
Yours,
Laurenz Albe
ke, you'll corrupt your database.
Orphaned files like that can be left behind after a database crash.
Did you experience crashes on the standby?
Yours,
Laurenz Albe
ating walsender process due to
> replication timeout
>
> Why the PostgreSQL server says something about "replication", we do
> pg_basebackup?
Because "pg_basebackup" uses a replication connection.
> Some more information:
>
> - wal_sender_timeout has
you are using has special support for that.
Currently, only functions and procedures written in PL/pgSQL cache execution
plans of static SQL statements. And you are right, that is usually a good
thing.
Yours,
Laurenz Albe
range
> hostssl all all 150.135.124.0/25 password
>
> Anywhere else that I should look for a cause?
"no encryption" does not match a "hostssl" entry.
Either add "sslmode=require" to the connection string, or use a "hostnossl"
entry.
Yours,
Laurenz Albe
tement, depending on the answer to the previous question.
No unsurmountable questions, but someone would have to come up with a
clear design and implement it.
Yours,
Laurenz Albe
ctory can grow to be very large.
You can avoidwriting WAL if you set "wal_level = minimal", restart PostgreSQL
and restore the dump with the --single-transaction option.
Yours,
Laurenz Albe
On Wed, 2023-11-22 at 23:33 +0530, Atul Kumar wrote:
> Please can you share any command for due diligence whether ip is resolved to
> ipv6 ?.
Not a lot of diligence is due to figure out that you can use
ping localhost
Yours,
Laurenz Albe
requires option: pg1-path
> HINT: does this stanza exist?
It looks like you have a bad "restore_command" configured.
It would be intereseting to see the log lines before the ones you are showing.
Yours,
Laurenz Albe
evant parameters would be "join_collapse_limit"
and
"from_collapse_limit".
But without knowing your query, we can say nothing.
Yours,
Laurenz Albe
he standby server.
This is certainly one of the major problems introduced by commit 2dedf4d9a8:
If you ever recovered a database, you may end up having recovery parameters
set in your configuration file. You don't notice them until you build a
standby server, which will then get into trouble.
Yours,
Laurenz Albe
; moved
> millions of steps within a single minute.
That looks like some application went crazy and inserted millions of rows, but
the
inserts were rolled back. But it is hard to be certain with the clues given.
Yours,
Laurenz Albe
On Fri, 2023-11-24 at 16:59 +0100, Les wrote:
>
>
> Laurenz Albe (2023. nov. 24., P, 16:00):
> > On Fri, 2023-11-24 at 12:39 +0100, Les wrote:
> > > Under normal circumstances, the number of write operations is relatively
> > > low, with an
> > > aver
Time to restore your backup.
Investigate how you got there. Did you run "pg_resetwal"?
Did you restore a database from a file system backup?
Did you remove a "backup_label" file? Is your hardware broken?
Yours,
Laurenz Albe
rift into locale-dependent rules. Such a function
would be even
more special-case than the current "initcap()", which is already pretty
specific to certain
languages. I don't know if there is enough value for the general public in it.
But it should be easy to implement it yourself, for example in PL/pgSQL. Why
don't you
try that?
Yours,
Laurenz Albe
ou are running a version older than v12, the default value for
"autovacuum_vacuum_cost_delay" is 20ms, which makes autovacuum really slow.
You should
change that parameter to 2ms, and if that is still too slow, use ALTER TABLE to
set it
to 0 on the affected table. Then aut
disvalid =
> FALSE
> and indisready = FALSE.
You forgot to ALTER INDEX ... ATTACH PARTITION to turn the invalid index on the
partitioned table into a valid index.
Yours,
Laurenz Albe
"1952" is just a typo), you should set the client encoding to WIN1252,
so that PostgreSQL knows how to convert the data correctly.
You can do that in several ways; the simplest might be to set the
environment variable PGCLIENTENCODING to WIN1252.
Yours,
Laurenz Albe
On Mon, 2023-12-11 at 13:54 -0500, Igniris Valdivia Baez wrote:
> El sáb, 9 dic 2023 a las 1:01, Laurenz Albe ()
> escribió:
> >
> > On Fri, 2023-12-08 at 23:58 -0500, Igniris Valdivia Baez wrote:
> > > hello, I have an ETL process collecting data from a postgresql
&
n't convert characters to replacement characters.
Yours,
Laurenz Albe
ll files after the reset ?
You cannot do that.
> 3, How can I determine the amount of data lost ?
Well, everything from after the "backup".
Yours,
Laurenz Albe
contains 0x00.
> How to resolve this error ? any workaround to load the data with 0x00 values ?
This is not about loading data into a "bytea", but into a "text", "varchar" or
"char" column. Check again.
Yours,
Laurenz Albe
ord
> *:*:replication:repl:repl_user_password
make sure that .pgpass is in the home directory of the PostgreSQL user and has
restrictive permissions (0600). Try using it with a manual connection attempt
via "psql".
Yours,
Laurenz Albe
On Tue, 2024-01-16 at 13:15 +0100, Rossana Ocampos wrote:
> I have a query about creating nested functions in PostgreSQL.
There are no "nested functions" in PostgreSQL.
You'd need to rewrite that to use a proper stand-alone function.
Yours,
Laurenz Albe
estore your backup.
If you have no backup, and the data are important for you (which is a
contradiction
in terms), you should hire a consultant with extensive PostgreSQL knowledge in
the hope that he or she can salvage some of your data.
Yours,
Laurenz Albe
DECLARE
_v_explain jsonb;
BEGIN
EXECUTE 'EXPLAIN (FORMAT JSON) select ...' INTO _v_explain;
RAISE NOTICE '%', _v_explain;
END;
Yours,
Laurenz Albe
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
ta corruption, and you should restore your backup.
Yours,
Laurenz Albe
stics 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
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.
> &
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 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
> > >
he server takes a long 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
the standby.
Either use
pg_ctl promote -D /path/to/datadir
on the command line or
SELECT pg_promote();
in SQL.
Yours,
Laurenz Albe
context of switchover :^/
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
f these considerations (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
;t think there is a way to enforce that. Your application code
has to do the right thing.
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
gt; As parting 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
ightly
longer. 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
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
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
quot;ROLLBACK TRANSACTION");
> 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 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
INCLUDE 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
s, 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
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
led crash, 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
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
re curious 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
;logically".
Yes, NULLs are stored in indexes, just like everything else.
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
rom the 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
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
y?
You may 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
re. There is no entity that would fill in your forms.
Yours,
Laurenz Albe
ne, but an "absolute timestamp",
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
ow count and cost of execution plan
steps. You can never use them as proof.
Yours,
Laurenz Albe
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
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 the st
ct 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
n the column?
No.
Yours,
Laurenz Albe
queryid 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
nnect.html#LIBPQ-KEEPALIVES-IDLE
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
now cluster 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
701 - 800 of 1221 matches
Mail list logo
