bout twice as fast as method3. But if I
connect to a database on the other side of the city, method2 is now more
than 16 times faster than method3 . Simply because the delay in
communication is now large compared to the time it takes to insert those
rows.
hp
--
_ | Peter J. Holzer
ally
triggers the snapshot.
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
me: 5051.584 ms (00:05.052)
hjp=> alter table t add primary key(i);
ALTER TABLE
Time: 5222.788 ms (00:05.223)
As you can see, adding the primary key takes just as much time as
creating the unique index. So it doesn't look like PostgreSQL is able to
take advantage of the existing index (w
On 2024-09-19 20:12:13 +0200, Paul Foerster wrote:
> Hi Peter,
> > On 19 Sep 2024, at 19:43, Peter J. Holzer wrote:
> >
> > I wrote a small script[1] which prints all unicode code points and a few
> > selected[2] longer strings in order. If you run that before and af
ccur. So an application designed for serializable
would have some kind of retry logic already in place.
SO that leads as to another solution:
Retry each batch (possibly after reducing the batch size) until it
succeeds.
hp
--
_ | Peter J. Holzer| Story must make more sense
ndexes on text (etc.) columns
just to be sure.
hp
[1] https://git.hjp.at:3000/hjp/pgcollate
[2] The selection is highly subjective and totally unscientific.
Additions are welcome.
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
On 2024-10-12 09:02:37 -0700, Adrian Klaver wrote:
> On 10/12/24 03:17, Peter J. Holzer wrote:
> > On 2024-10-11 21:21:16 -0700, Adrian Klaver wrote:
> > > On 10/11/24 20:10, admin@iseki.space wrote:
> > > > I found. Maybe we should reply to the mailing list only. O
7;t exist. You
should create that before restoring the backup. Or could fix the errors
after the fact but for that you need to understand what went wrong.
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at
restore a 25 GB database, so
that's what I'd try first. It's simple and you can easily test it
without disruption.
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross,
On 2025-02-07 09:22:13 +0100, Michał Kłeczek wrote:
>
>
> On 6 Feb 2025, at 22:03, Peter J. Holzer wrote:
>
> On 2025-02-04 22:41:38 +0100, Thiemo Kellner wrote:
>
>
> I might see what you want to point out. E.g. the table is COLOURS. The
>
magically change color just because you changed some text in the
database. So that change simply doesn't make sense and shouldn't be done
as part of a maintenance release. Confusing a few people who just happen
to open the dropdown in the wrong second is the least of your problems.
ng similar working?
Yes. Cleaning up stuff is probably one of the most frequent uses of
cron.
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Creative writ
om what I
> can tell from @Laurenz's response above we have the names of the logs
> customised to posgtres-%d-%m-%y.
Earlier you wrote that the pattern was actually
«postgresql-%Y-%m-%d.log». «find ... -name "*.log"» would find that but
of course not «posgtres-%d-%m-%y».
t; account, you should still be able to locally connect to PG.
True. But the client may not be on the same machine.
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Cr
strong passwords, use a second factor. Or maybe
replace passwords with some other method (public keys, FIDO, ...)
altogether (in fact, I'd do that for system accounts).
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |
On 2024-12-16 10:37:59 -0500, Ron Johnson wrote:
> On Mon, Dec 16, 2024 at 10:19 AM Peter J. Holzer wrote:
>
> On 2024-12-16 09:17:25 -0500, Ron Johnson wrote:
> > Local (socket-based) connections are typically peer-authenticated
> > (meaning that authenticatio
illion attempts (so the limit doesn't help either).
OTOH, the limit gives an attacker a very simple way to deny the service to
the legitimate used: Just enter a bogus password three times and boom -
account locked. (That threat can be mitigated by applying the limit per
IP address - but the a
one = false
ORDER BY id
FOR NO KEY UPDATE
2) Check whether the id you got first is the smallest of all.
3) If it isn't, rollback and start over.
4) If it is, you have now locked all the rows with the same lock_id and
can continue.
The advisory lock isn't needed then.
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
; | uniq -c | sort -n
to find the processes with the most open files (but be aware that lsof
reports file descriptors for each thread, so any multi-threaded programs
will be vastly inflated)
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
. It's not
really "number of seconds since 1970-01-01", but "number of days since
1970-01-01 times 86400 plus number of seconds in the current day".
So you can't use epoch to detect leap seconds.
And I don't think PostgreSQL keeps track of leap seconds int
That might
work, but it probably also shouldn't do it by default.
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
On 2025-01-12 17:59:20 -0500, Tom Lane wrote:
> "Peter J. Holzer" writes:
> > The web framework Django will automatically and transparently rehash any
> > password with the currently preferred algorithm if it isn't stored that
> > way already.
>
> Re
7;t really a faster way to do what Veem wants. There may
however be less disruptive way: He could create a new column with the
new values (which takes at least as long but can be done in the
background) and then switch it over and drop the old column.
hp
--
_ | Peter J. Holzer
ure should only be enabled by a GUC.
Additional question: Do current clients (especially the ODBC client)
even support AuthenticationCleartextPassword by default?
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at
new version
2) Drop the new (empty) database
3) Invoke pg_upgradecluster (see man-page for details)
4) Check that everything is ok
5) Drop old database and uninstall old version.
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) |
On 2025-04-20 08:28:22 -0700, Adrian Klaver wrote:
> On 4/20/25 02:10, Peter J. Holzer wrote:
> > I've just read Laurenz' blog post about the differences between Oracle
> > and PostgreSQL[1].
> >
> > One of the differences is that something like
>
especially for large tables.
So, is there a better way?
hjp
[1]
https://www.cybertec-postgresql.com/en/comparison-of-the-transaction-systems-of-oracle-and-postgresql/
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hj
you want to do updates in a
predictable order. For example to prevent deadlocks.
hjp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Creative writing
__/ | h
order which can change, I guess?
Anyway, I don't have a pressing need for this, as I said I was just
curious.
hjp
[1] Mostly in MySQL I think, since it didn't have recursive queries of
any kind.
--
_ | Peter J. Holzer| Story must make mo
dfbsspec.ssa stands along in the view
> definition, Postgres does the right thing; when the exact same query
> stands in a UNION ALL with other tables, Postgres doesn't use the
> index. Hu?
It is obviously not the exact same query if one of them need to
t happens automatically with the
default settings.
> Even I enable it now, I can't figure out that error.
Just search the logs for the string "ERROR".
> By any chance, if I get that statement, what should I do?
1) Read the error message.
2) Figure out what caused the error
h migration. If they
are in the same database you could even use the same sequences to avoid
conflicts. If you need to generate new key values (for example, you are
merging two tables into one), you will need a translation table (which
could be just some extra columns in the new table).
hjp
On 2025-06-25 14:42:26 +0200, raphi wrote:
>
>
> Am 25.06.2025 um 13:55 schrieb Peter J. Holzer:
> > On 2025-06-23 16:35:35 +0200, raphi wrote:
> > > To be fair, setting up LDAP is very easy in PG, just one line in hba.conf
> > > and all is done. But sadly, tha
On 2025-06-25 17:55:12 +0200, raphi wrote:
> Am 25.06.2025 um 17:33 schrieb Peter J. Holzer:
> > On 2025-06-25 14:42:26 +0200, raphi wrote:
> > > That's not how the identiy principle works, at least not how it's
> > > implement in our company. A user in ld
On 2025-06-28 18:06:51 +0200, raphi wrote:
> Am 28.06.2025 um 15:59 schrieb Peter J. Holzer:
> > On 2025-06-27 19:00:36 +0200, raphi wrote:
> >
> > > It's the application's password that we want to ensure that it is
> > > complex and gets changed after
ted to the personal roles.
So for example you would authenticate as «raphi» and I as «hjp» but we
could both change to «foo_admin» or whatever. That would even have the
advantage that we leave an audit trail with our "real" identities.
hjp
--
_
On 2025-06-27 19:00:36 +0200, raphi wrote:
>
>
> Am 26.06.2025 um 14:27 schrieb Peter J. Holzer:
> > On 2025-06-25 17:55:12 +0200, raphi wrote:
> > > Am 25.06.2025 um 17:33 schrieb Peter J. Holzer:
> > > > On 2025-06-25 14:42:26 +0200, raphi wrote:
> >
ions and "fix" them without
investigating the cause
2) Some automated procedure (running as root) might "fix" the
permissions at startup (like the initdb in the example) or during an
upgrade.
3) use your imagination ;-).
hjp
--
_ | Peter J. Holzer| S
ling of the sort, so it
shouldn't be included, right?
And why are the plans different at all? Computing a few extra values per
row shouldn't change the cost of the query delivering the rows, IMHO.
But then the costs are very similar, so maybe it's just some random
variation.
Some estimates are quite a bit off, even on the tables. I did ANALYZE
the whole database (and then the tables, again) during my tests.
hjp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
On 2025-07-23 10:08:31 +1200, David Rowley wrote:
> On Wed, 23 Jul 2025 at 03:18, Peter J. Holzer wrote:
> >
> > PostgreSQL version 17.5 on Ubuntu 24.04 (PGDG build).
>
>
> > -> Merge Left Join (cost=4613.25..7180.30 rows=8357
> > width=1
601 - 640 of 640 matches
Mail list logo
